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
}
})