def process_shp(session, spno, shp): for feature in shp: try: props = feature['properties'] # Convert property names to uppercase props = { key.upper(): props[key] for key in props } if spno != props['SPNO']: log.error('SPNO does not match %s != %s' % (spno, props['SPNO'])) return if props['RNGE'] in (8,9): # TODO - investigate what these numbers mean return taxon_id = props['TAXONID'] parts = _taxon_re.match(taxon_id) if parts is None: log.error("Invalid taxon id format: %s" % taxon_id) return prefix = parts.group(1) suffix = parts.group(2) or '' geometry = shape(feature['geometry']) if type(geometry) == Polygon: geometry = MultiPolygon([geometry]) geometry = reproject(geometry, pyproj.Proj(shp.crs), pyproj.Proj('+init=EPSG:4326')) for s in suffix.split("."): taxon_exists = len(session.execute("SELECT 1 FROM taxon WHERE id = :id", { 'id': prefix + s }).fetchall()) > 0 if taxon_exists: session.execute("""INSERT INTO taxon_range (taxon_id, range_id, breeding_range_id, geometry) VALUES (:taxon_id, :range_id, :breeding_range_id, ST_GeomFromWKB(_BINARY :geom_wkb))""", { 'taxon_id': prefix + s, 'range_id': props['RNGE'] or None, 'breeding_range_id': props['BRRNGE'] or None, 'geom_wkb': shapely.wkb.dumps(geometry) } ) if insert_subdivided: for geom in subdivide_geometry(geometry.buffer(0)): geom = to_multipolygon(geom) if not geom.is_empty: session.execute("""INSERT INTO taxon_range_subdiv (taxon_id, range_id, breeding_range_id, geometry) VALUES (:taxon_id, :range_id, :breeding_range_id, ST_GeomFromWKB(_BINARY :geom_wkb))""", { 'taxon_id': prefix + s, 'range_id': props['RNGE'] or None, 'breeding_range_id': props['BRRNGE'] or None, 'geom_wkb': shapely.wkb.dumps(to_multipolygon(geom)) } ) except: log.error("Error processing row: %s" % props) raise
def main(): logging.basicConfig( stream=sys.stdout, level=logging.INFO, format='%(asctime)-15s %(name)s %(levelname)-8s %(message)s') parser = argparse.ArgumentParser( description='Import regions from shapefile') parser.add_argument('filename', type=str, help='Shapefile containing regions') args = parser.parse_args() session = get_session() session.execute("DELETE FROM t1_survey_region") with fiona.open(args.filename, encoding='Windows-1252') as shp: for index, feature in enumerate(tqdm(shp)): props = feature['properties'] geometry = shape( transform_geom(shp.crs, 'EPSG:4326', feature['geometry'])) geometry = geometry.buffer(0) session.execute( """INSERT INTO region (id, name, geometry, state, positional_accuracy_in_m) VALUES (:id, :name, ST_GeomFromWKB(_BINARY :geometry_wkb), :state, :positional_accuracy_in_m)""", { 'id': index, 'name': props['RegName'], 'geometry_wkb': shapely.wkb.dumps( to_multipolygon(geometry)), 'state': props['StateName'], 'positional_accuracy_in_m': int(props['Accuracy']) }) for geometry in subdivide_geometry(geometry): session.execute( """INSERT INTO region_subdiv (id, name, geometry) VALUES (:id, :name, ST_GeomFromWKB(_BINARY :geometry_wkb))""", { 'id': index, 'name': props['RegName'], 'geometry_wkb': shapely.wkb.dumps(to_multipolygon(geometry)) }) log.info("Updating t1_survey_region (this may take a while)") session.execute("CALL update_t1_survey_region(NULL)") session.commit()
def get_core_range_geometry(session, taxon_id): rows = session.execute( """SELECT HEX(ST_AsBinary(geometry)) FROM taxon_range WHERE taxon_id = :taxon_id AND range_id = 1""", { 'taxon_id': taxon_id }).fetchall() geom = GeometryCollection( [shapely.wkb.loads(binascii.unhexlify(row[0])) for row in rows]) return to_multipolygon(geom)
def main(): logging.basicConfig(stream=sys.stdout, level=logging.INFO, format='%(asctime)-15s %(name)s %(levelname)-8s %(message)s') parser = argparse.ArgumentParser(description='Import Type 2 sites from shapefile') parser.add_argument('filename', type=str, help='Shapefile containing type 2 site polygons') args = parser.parse_args() session = get_session() with open_shapefile(args.filename, dest_crs='EPSG:4326') as (shp, reproject): for feature in tqdm(shp): props = feature['properties'] geometry = to_multipolygon(reproject(shape(feature['geometry']))) session.execute("""INSERT INTO t2_site (search_type_id, geometry) VALUES (:search_type_id, ST_GeomFromWKB(_BINARY :geometry_wkb))""", { 'search_type_id': props['SiteType'], 'geometry_wkb': shapely.wkb.dumps(geometry) }) session.commit()
def process_spno(spno): session = get_session() try: # Get raw points from DB raw_points = get_species_points(session, spno) if len(raw_points) < 4: # Not enough points to create an alpha hull return # Read points from database points = [reproject(p, db_proj, working_proj) for p in raw_points] # Generate alpha shape alpha_shp = make_alpha_hull( points = points, coastal_shape = coastal_shape, thinning_distance = tsx.config.config.getfloat('processing.alpha_hull', 'thinning_distance'), alpha = tsx.config.config.getfloat('processing.alpha_hull', 'alpha'), hullbuffer_distance = tsx.config.config.getfloat('processing.alpha_hull', 'hullbuffer_distance'), isolatedbuffer_distance = tsx.config.config.getfloat('processing.alpha_hull', 'isolatedbuffer_distance')) # Convert back to DB projection alpha_shp = reproject(alpha_shp, working_proj, db_proj) # Clean up geometry alpha_shp = alpha_shp.buffer(0) # Get range polygons to intersect with alpha shape for taxon_id, range_id, breeding_range_id, geom_wkb in get_species_range_polygons(session, spno): # Intersect and insert into DB geom = shapely.wkb.loads(binascii.unhexlify(geom_wkb)).buffer(0) geom = to_multipolygon(geom.intersection(alpha_shp)) # slow if len(geom) > 0: session.execute("""INSERT INTO taxon_presence_alpha_hull (taxon_id, range_id, breeding_range_id, geometry) VALUES (:taxon_id, :range_id, :breeding_range_id, ST_GeomFromWKB(_BINARY :geom_wkb))""", { 'taxon_id': taxon_id, 'range_id': range_id, 'breeding_range_id': breeding_range_id, 'geom_wkb': shapely.wkb.dumps(geom) } ) # We also subdivide the geometries into small pieces and insert this into the database. This allows for much faster # spatial queries in the database. for subgeom in subdivide_geometry(geom, max_points = 100): session.execute("""INSERT INTO taxon_presence_alpha_hull_subdiv (taxon_id, range_id, breeding_range_id, geometry) VALUES (:taxon_id, :range_id, :breeding_range_id, ST_GeomFromWKB(_BINARY :geom_wkb))""", { 'taxon_id': taxon_id, 'range_id': range_id, 'breeding_range_id': breeding_range_id, 'geom_wkb': shapely.wkb.dumps(subgeom) } ) if commit: session.commit() except: log.exception("Exception processing alpha hull") raise finally: session.close()
def process(taxon_id, coastal_shape, data_type, commit): session = get_session() try: # Load core range geometry core_range_geom = reproject( get_core_range_geometry(session, taxon_id), to_working_transformer).buffer(0).intersection(coastal_shape) for source_id in get_source_ids(session, data_type, taxon_id): log.info("Processing taxon_id: %s, source_id: %s" % (taxon_id, source_id)) # Get raw points from DB raw_points = get_raw_points(session, data_type, taxon_id, source_id) empty = len(raw_points) < 4 if empty: log.info( "Taxon %s: not enough points to create alpha hull (%s)" % (taxon_id, len(raw_points))) if not empty: # Read points from database points = [ reproject(p, to_working_transformer) for p in raw_points ] # Generate alpha shape alpha_shp = make_alpha_hull( points=points, coastal_shape=None, thinning_distance=tsx.config.config.getfloat( 'processing.alpha_hull', 'thinning_distance'), alpha=tsx.config.config.getfloat('processing.alpha_hull', 'alpha'), hullbuffer_distance=tsx.config.config.getfloat( 'processing.alpha_hull', 'hullbuffer_distance'), isolatedbuffer_distance=tsx.config.config.getfloat( 'processing.alpha_hull', 'isolatedbuffer_distance')) # Clean up geometry alpha_shp = alpha_shp.buffer(0) if core_range_geom.area == 0: log.info("Core range geometry area is zero") empty = True else: # Intersect alpha hull with core range intersected_alpha = to_multipolygon( core_range_geom.intersection(alpha_shp)) empty = intersected_alpha.is_empty if empty: session.execute( """INSERT INTO taxon_source_alpha_hull (source_id, taxon_id, data_type, core_range_area_in_m2, alpha_hull_area_in_m2) VALUES (:source_id, :taxon_id, :data_type, 0, 0)""", { 'source_id': source_id, 'taxon_id': taxon_id, 'data_type': data_type }) else: session.execute( """INSERT INTO taxon_source_alpha_hull (source_id, taxon_id, data_type, geometry, core_range_area_in_m2, alpha_hull_area_in_m2) VALUES (:source_id, :taxon_id, :data_type, ST_GeomFromWKB(_BINARY :geom_wkb), :core_range_area, :alpha_hull_area)""", { 'source_id': source_id, 'taxon_id': taxon_id, 'data_type': data_type, 'geom_wkb': shapely.wkb.dumps( reproject(intersected_alpha, to_db_transformer)), 'core_range_area': core_range_geom.area, 'alpha_hull_area': intersected_alpha.area }) if commit: session.commit() except: log.exception("Exception processing alpha hull") raise finally: session.close()
def export(layers, species=None): session = get_session() export_alpha = 'alpha' in layers export_ultrataxa = 'ultrataxa' in layers export_pseudo_absence = 'pa' in layers export_grid = 'grid' in layers export_dir = tsx.config.data_dir('export') if species == None: species = get_all_spno(session) for spno in tqdm(species): if export_alpha: filename = os.path.join(export_dir, '%s-alpha.shp' % spno) alpha_hulls = session.execute( """SELECT taxon_id, range_id, breeding_range_id, HEX(ST_AsWKB(geometry)) FROM taxon_presence_alpha_hull, taxon WHERE taxon_id = taxon.id AND spno = :spno""", { 'spno': spno }).fetchall() if len(alpha_hulls) > 0: with fiona.open(filename, 'w', driver='ESRI Shapefile', crs={ 'no_defs': True, 'ellps': 'WGS84', 'datum': 'WGS84', 'proj': 'longlat' }, schema={ 'geometry': 'MultiPolygon', 'properties': { 'TAXONID': 'str', 'RNGE': 'int', 'BRRNGE': 'int' } }) as output: for taxon_id, range_id, breeding_range_id, geom_wkb in alpha_hulls: geom = to_multipolygon( shapely.wkb.loads(binascii.unhexlify(geom_wkb))) if len(geom) == 0: continue output.write({ 'geometry': shapely.geometry.mapping(geom), 'properties': { 'TAXONID': taxon_id, 'RNGE': range_id, 'BRRNGE': breeding_range_id } }) if export_ultrataxa: filename = os.path.join(export_dir, '%s-ultrataxa.shp' % spno) items = session.execute( """SELECT ST_X(coords) AS x, ST_Y(coords) AS y, taxon.id, range_id, generated_subspecies, t2_survey_site.site_id, t2_survey.search_type_id FROM t2_ultrataxon_sighting, t2_sighting, taxon, t2_survey LEFT JOIN t2_survey_site ON t2_survey_site.survey_id = t2_survey.id WHERE t2_ultrataxon_sighting.sighting_id = t2_sighting.id AND t2_sighting.survey_id = t2_survey.id AND t2_ultrataxon_sighting.taxon_id = taxon.id AND taxon.spno = :spno """, { 'spno': spno }).fetchall() with fiona.open(filename, 'w', driver='ESRI Shapefile', crs={ 'no_defs': True, 'ellps': 'WGS84', 'datum': 'WGS84', 'proj': 'longlat' }, schema={ 'geometry': 'Point', 'properties': { 'TaxonID': 'str', 'Rnge': 'int', 'Generated': 'int', 'SearchType': 'int', 'SiteID': 'int' } }) as output: for x, y, taxon_id, range_id, generated_subspecies, site_id, search_type_id in items: geom = Point(x, y) output.write({ 'geometry': shapely.geometry.mapping(geom), 'properties': { 'TaxonID': taxon_id, 'Rnge': range_id, 'Generated': generated_subspecies, 'SearchType': search_type_id, 'SiteID': site_id } }) if export_pseudo_absence: filename = os.path.join(export_dir, '%s-pa.shp' % spno) items = session.execute( """SELECT ST_X(coords) AS x, ST_Y(coords) AS y, taxon.id FROM t2_processed_sighting, t2_processed_survey, t2_survey, taxon WHERE t2_processed_sighting.survey_id = t2_processed_survey.id AND t2_processed_survey.raw_survey_id = t2_survey.id AND t2_processed_sighting.taxon_id = taxon.id AND taxon.spno = :spno AND pseudo_absence AND experimental_design_type_id = 1 """, { 'spno': spno }).fetchall() with fiona.open(filename, 'w', driver='ESRI Shapefile', crs={ 'no_defs': True, 'ellps': 'WGS84', 'datum': 'WGS84', 'proj': 'longlat' }, schema={ 'geometry': 'Point', 'properties': { 'TaxonID': 'str' } }) as output: for x, y, taxon_id in items: geom = Point(x, y) output.write({ 'geometry': shapely.geometry.mapping(geom), 'properties': { 'TaxonID': taxon_id } }) if export_grid: filename = os.path.join(export_dir, '%s-grid.shp' % spno) items = session.execute( """SELECT ST_X(coords) AS x, ST_Y(coords) AS y, grid_cell_id, taxon.id, pseudo_absence FROM t2_processed_sighting, t2_processed_survey, t2_survey, taxon WHERE t2_processed_sighting.survey_id = t2_processed_survey.id AND t2_processed_survey.raw_survey_id = t2_survey.id AND t2_processed_sighting.taxon_id = taxon.id AND taxon.spno = :spno AND experimental_design_type_id = 2 """, { 'spno': spno }).fetchall() with fiona.open(filename, 'w', driver='ESRI Shapefile', crs={ 'no_defs': True, 'ellps': 'WGS84', 'datum': 'WGS84', 'proj': 'longlat' }, schema={ 'geometry': 'Point', 'properties': { 'TaxonID': 'str', 'GridID': 'int', 'Pseudo': 'int' } }) as output: for x, y, grid_cell_id, taxon_id, pseudo_absence in items: geom = Point(x, y) output.write({ 'geometry': shapely.geometry.mapping(geom), 'properties': { 'TaxonID': taxon_id, 'GridID': grid_cell_id, 'Pseudo': pseudo_absence } })