def calculate_cell_compositions(cells_dao, habs_dao):
# Save cells, habitats to temporary tables in the db.
# This can take a bit of time.
if conf.conf['verbose']: print >> sys.stderr, "Creating temp cell/hab tables..."
tables = ['cell', 'habitat']
for table in tables:
sa.metadata.tables[table].drop(bind=sa.engine, checkfirst=True)
sa.metadata.tables[table].create(bind=sa.engine)
sa.session.add_all([c for c in cells_dao.all()])
sa.session.add_all([h for h in habs_dao.all()])
sa.session.commit()
# Calculate areas for habitats.
if conf.conf['verbose']: print >> sys.stderr, "Calculating habitat areas..."
for hab in habs_dao.all():
hab_area_entity = geo_func.area(func.geography(Habitat.geom))
hab.area = sa.session.query(hab_area_entity).filter(Habitat.id == hab.id).one()[0]
sa.session.add(hab)
sa.session.commit()
# Generate habitat compositions for cells.
counter = 0
for cell in cells_dao.all():
if conf.conf['verbose']:
if (counter % 100) == 0: print >> sys.stderr, "at cell # %s" % counter
composition = {}
# Get cell area.
cell_area_entity = geo_func.area(func.geography(Cell.geom))
cell.area = sa.session.query(cell_area_entity).filter(Cell.id == cell.id).one()[0]
# Get habitats which intersect the cell.
intersection_area_entity = geo_func.area(func.geography(geo_func.intersection(Habitat.geom, cell.geom)))
results = sa.session.query(Habitat, intersection_area_entity).filter(Habitat.geom.intersects(cell.geom)).all()
for result in results:
hab = result[0]
intersection_area = result[1]
hab_key = (hab.substrate, hab.energy,)
pct_area = intersection_area/cell.area
composition[hab_key] = composition.get(hab_key, 0) + pct_area
cell.habitat_composition = composition
sa.session.add(cell)
counter += 1
sa.session.commit()
"""
def geodesic_calc_with_postgis():
# TODO: Remove these cludges after pyproj upgarade
from sqlalchemy import func
from ...models import DBSession
fun = dict(length=func.ST_Length, area=func.ST_Area)[prop]
query = fun(func.geography(func.ST_GeomFromText(geom.wkt, srid)))
return DBSession.query(query).scalar()
def __call__(self):
tableinfo = TableInfo.from_layer(self.layer)
tableinfo.setup_metadata(self.layer._tablename)
table = tableinfo.table
columns = [
table.columns.id,
]
where = []
srsid = self.layer.srs_id if self._srs is None else self._srs.id
geomcol = table.columns.geom
geomexpr = func.st_transform(geomcol, srsid)
if self._clip_by_box is not None:
if _clipbybox2d_exists():
clip = func.st_setsrid(
func.st_makeenvelope(*self._clip_by_box.bounds),
self._clip_by_box.srid)
geomexpr = func.st_clipbybox2d(geomexpr, clip)
else:
clip = func.st_setsrid(
func.st_geomfromtext(self._clip_by_box.wkt),
self._clip_by_box.srid)
geomexpr = func.st_intersection(geomexpr, clip)
if self._simplify is not None:
geomexpr = func.st_simplifypreservetopology(
geomexpr, self._simplify)
if self._geom:
if self._single_part:
class geom(ColumnElement):
def __init__(self, base):
self.base = base
@compiles(geom)
def compile(expr, compiler, **kw):
return "(%s).geom" % str(compiler.process(expr.base))
columns.append(
func.st_asewkb(geom(func.st_dump(geomexpr))).label('geom'))
else:
columns.append(func.st_asewkb(geomexpr).label('geom'))
if self._geom_len:
columns.append(
func.st_length(
func.geography(func.st_transform(geomexpr,
4326))).label('geom_len'))
if self._box:
columns.extend((
func.st_xmin(geomexpr).label('box_left'),
func.st_ymin(geomexpr).label('box_bottom'),
func.st_xmax(geomexpr).label('box_right'),
func.st_ymax(geomexpr).label('box_top'),
))
selected_fields = []
for f in tableinfo.fields:
if not self._fields or f.keyname in self._fields:
columns.append(table.columns[f.key].label(f.keyname))
selected_fields.append(f)
if self._filter_by:
for k, v in six.iteritems(self._filter_by):
if k == 'id':
where.append(table.columns.id == v)
else:
where.append(table.columns[tableinfo[k].key] == v)
if self._filter:
token = []
for k, o, v in self._filter:
supported_operators = (
"eq",
"ge",
"gt",
"ilike",
"in",
"le",
"like",
"lt",
"ne",
"notin",
"startswith",
)
if o not in supported_operators:
raise ValueError(
"Invalid operator '%s'. Only %r are supported." %
(o, supported_operators))
if v and o in ['in', 'notin']:
v = v.split(',')
if o in [
"ilike",
"in",
"like",
"notin",
"startswith",
]:
o += "_op"
op = getattr(db.sql.operators, o)
if k == "id":
token.append(op(table.columns.id, v))
else:
token.append(op(table.columns[tableinfo[k].key], v))
where.append(db.and_(*token))
if self._filter_sql:
token = []
for _filter_sql_item in self._filter_sql:
if len(_filter_sql_item) == 3:
table_column, op, val = _filter_sql_item
if table_column == 'id':
token.append(op(table.columns.id, val))
else:
token.append(
op(table.columns[tableinfo[table_column].key],
val))
elif len(_filter_sql_item) == 4:
table_column, op, val1, val2 = _filter_sql_item
token.append(
op(table.columns[tableinfo[table_column].key], val1,
val2))
where.append(db.and_(*token))
if self._like:
token = []
for f in tableinfo.fields:
token.append(
cast(table.columns[f.key],
db.Unicode).ilike("%" + self._like + "%"))
where.append(db.or_(*token))
if self._intersects:
intgeom = func.st_setsrid(
func.st_geomfromtext(self._intersects.wkt),
self._intersects.srid)
where.append(
func.st_intersects(
geomcol, func.st_transform(intgeom, self.layer.srs_id)))
order_criterion = []
if self._order_by:
for order, colname in self._order_by:
order_criterion.append(
dict(asc=db.asc, desc=db.desc)[order](
table.columns[tableinfo[colname].key]))
order_criterion.append(table.columns.id)
class QueryFeatureSet(FeatureSet):
fields = selected_fields
layer = self.layer
_geom = self._geom
_geom_len = self._geom_len
_box = self._box
_limit = self._limit
_offset = self._offset
def __iter__(self):
query = sql.select(
columns,
whereclause=db.and_(*where),
limit=self._limit,
offset=self._offset,
order_by=order_criterion,
)
rows = DBSession.connection().execute(query)
for row in rows:
fdict = dict(
(f.keyname, row[f.keyname]) for f in selected_fields)
if self._geom:
geom = geom_from_wkb(row['geom'].tobytes(
) if six.PY3 else six.binary_type(row['geom']))
else:
geom = None
calculated = dict()
if self._geom_len:
calculated['geom_len'] = row['geom_len']
yield Feature(
layer=self.layer,
id=row.id,
fields=fdict,
geom=geom,
calculations=calculated,
box=box(row.box_left, row.box_bottom, row.box_right,
row.box_top) if self._box else None)
@property
def total_count(self):
query = sql.select([
func.count(table.columns.id),
],
whereclause=db.and_(*where))
res = DBSession.connection().execute(query)
for row in res:
return row[0]
return QueryFeatureSet()
def geodesic_calc_with_postgis():
fun = dict(length=func.ST_Length, area=func.ST_Area)[prop]
query = fun(func.geography(
func.ST_GeomFromText(geom_to_wkt(g), srid)
))
return DBSession.query(query).scalar()
def main():
# Get db session.
session = sa_session.get_session()
# Clear habitat_type tables
for t in [sa_habitat.table]:
session.execute(t.delete())
# Load shapefile
sf = ogr.Open(conf.conf['sasi_habitat_file'])
# Get feature layer.
layer = sf.GetLayer(0)
# Get layer srs.
layer_srs = layer.GetSpatialRef()
# Set target srs to 4326 (default used by most GIS software).
target_srs = ogr.osr.SpatialReference()
target_srs.ImportFromEPSG(4326)
# Get fields.
layer_def = layer.GetLayerDefn()
field_count = layer_def.GetFieldCount()
fields = [layer_def.GetFieldDefn(i).GetName() for i in range(field_count)]
# Initialize a list to hold habitat objects.
habitats = []
# For each cell feature...
counter = 0
features = [f for f in layer]
for f in features:
if (counter % 1000) == 0: print >> sys.stderr, "%s" % (counter),
counter += 1
# Get feature attributes.
f_attributes = {}
for i in range(field_count):
f_attributes[fields[i]] = f.GetField(i)
# Skip blank rows.
if (not f_attributes['SOURCE']): continue
# Get feature geometry.
ogr_g = f.GetGeometryRef()
# Transform to target_srs.
ogr_g = f.GetGeometryRef()
ogr_g.TransformTo(target_srs)
# We convert each feature into a multipolygon, since
# we may have a mix of normal polygons and multipolygons.
geom = wkb.loads(ogr_g.ExportToWkb())
if geom.geom_type =='Polygon':
geom = MultiPolygon([(geom.exterior.coords, geom.interiors )])
# Get habitat_type's energy code.
energy = energy_mappings.shp_to_va[f_attributes['Energy']]
# Get habitat_type's substrate object.
substrate_id = substrate_mappings.shp_to_va[f_attributes['TYPE_SUB'].strip()]
substrate = session.query(Substrate).filter(Substrate.id == substrate_id).one()
# Get habitat_type object.
habitat_type = session.query(Habitat_Type).join(Habitat_Type.substrate).filter(Substrate.id == substrate_id).filter(Habitat_Type.energy == energy).one()
# Make habitat object from feature data.
r = Habitat(
id_km100 = f_attributes['100km_Id'],
id_km1000 = f_attributes['1000Km_Id'],
id_vor = f_attributes['Vor_id'],
z = f_attributes['z'],
habitat_type = habitat_type,
area = f_attributes['Area_Km'],
geom = geom.wkt
)
habitats.append(r)
print >> sys.stderr, "Writing habitats to db"
session.add_all(habitats)
session.commit()
print >> sys.stderr, "Calculating areas for habitats."
habitat_area = geo_func.area(func.geography(Habitat.geom)).label('habitat_area')
habitat_infos = session.query(Habitat, habitat_area).all()
for (habitat, habitat_area) in habitat_infos:
habitat.area = habitat_area
session.commit()
print >> sys.stderr, "done"
