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"