def test_buffer(self): r = session.query(Road).filter(Road.road_name=='Graeme Ave').one() assert_almost_equal(float(session.scalar(functions.area(r.road_geom.buffer(10.0), diminfo_))), 1094509.76889366) ok_(session.query(Spot).filter(functions.within( 'POINT(-88.5945861592357 42.9480095987261)', Spot.spot_location.buffer(10))).first() is not None) assert_almost_equal(float(session.scalar(functions.area(functions.buffer( 'POINT(-88.5945861592357 42.9480095987261)', diminfo_, 10, 'unit=km arc_tolerance=0.05'), diminfo_))), 312144711.50297302)
def test_buffer(self): r = session.query(Road).filter(Road.road_name == "Graeme Ave").one() assert_almost_equal(session.scalar(functions.area(r.road_geom.buffer(10.0))), 323.99187776147323) ok_( session.query(Spot) .filter(functions.within("POINT(-88.5945861592357 42.9480095987261)", Spot.spot_location.buffer(10))) .first() is not None ) assert_almost_equal( session.scalar(functions.area(functions.buffer("POINT(-88.5945861592357 42.9480095987261)", 10))), 314.12087152405275, )
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 test_buffer(self): r = session.query(Road).filter(Road.road_name == 'Graeme Ave').one() assert_almost_equal( session.scalar(functions.area(r.road_geom.buffer(10.0))), 323.99187776147323) ok_( session.query(Spot).filter( functions.within('POINT(-88.5945861592357 42.9480095987261)', Spot.spot_location.buffer(10))).first() is not None) assert_almost_equal( session.scalar( functions.area( functions.buffer( 'POINT(-88.5945861592357 42.9480095987261)', 10))), 314.12087152405275)
def test_buffer(self): r = session.query(Road).filter(Road.road_name == 'Graeme Ave').one() assert_almost_equal( session.scalar(functions.area(r.road_geom.buffer(10.0, 8))), 321.93380659099699) ok_( session.query(Spot).filter( functions.within('POINT(-88.5945861592357 42.9480095987261)', Spot.spot_location.buffer(10))).first() is not None) assert_almost_equal( session.scalar( functions.area( functions.buffer( 'POINT(-88.5945861592357 42.9480095987261)', 10, 2))), 282.84271247461902)
def main(): # Get db session. session = sa_session.get_session() # Clear cell tables for t in [sa_cell.cell_habitat_table, sa_cell.cell_table]: session.execute(t.delete()) session.commit() # For each type of cell... for cell_size in ["km100", "km1000"]: print >>sys.stderr, "Processing cells of size '%s'" % cell_size # Initialize list of cells. cells = [] # Get cell ids cell_id_attr = getattr(Habitat, "id_%s" % cell_size) cell_area = func.sum(geo_func.area(func.geography(Habitat.geom))).label("cell_area") cell_depth = func.avg(Habitat.z).label("cell_depth") cell_geom_wkb = geo_func.wkb(func.st_union(Habitat.geom).label("cell_geom")) cell_infos = session.query(cell_id_attr, cell_area, cell_depth, cell_geom_wkb).group_by(cell_id_attr).all() # For each id, create cell and assign habitats. print >>sys.stderr, "Creating cells" cell_counter = 0 for (cell_id, cell_area, cell_depth, cell_geom_wkb) in cell_infos: if (cell_counter % 1000) == 0: print >>sys.stderr, "%s..." % (cell_counter), cell_counter += 1 # Get cell's habitats. cell_habitats = session.query(Habitat).filter(cell_id_attr == cell_id).all() # Format cell's geometry. cell_geom = wkb.loads("%s" % cell_geom_wkb) if cell_geom.geom_type == "Polygon": cell_geom = MultiPolygon([(cell_geom.exterior.coords, cell_geom.interiors)]) cell = Cell( type=cell_size, type_id=cell_id, geom=cell_geom.wkt, area=cell_area, depth=cell_depth, habitats=cell_habitats, ) cells.append(cell) session.add_all(cells) session.commit()
def test_area(self): l = session.query(Lake).filter(Lake.lake_name=='Lake White').one() assert_almost_equal(float(session.scalar(l.lake_geom.area(tolerance, auto_diminfo=False))), 104854567.261647) assert_almost_equal(float(session.scalar(l.lake_geom.area( OracleSpatialDialect.get_diminfo_select(Lake.lake_geom), auto_diminfo=False))), 104854567.261647) assert_almost_equal(float(session.scalar(l.lake_geom.area( OracleSpatialDialect.get_diminfo_select(Lake.__table__.c.lake_geom), auto_diminfo=False))), 104854567.261647) ok_(session.query(Lake).filter(Lake.lake_geom.area > 0).first() is not None) assert_almost_equal(float(session.scalar(functions.area(WKTSpatialElement('POLYGON((743238 2967416,743238 2967450,743265 2967450,743265.625 2967416,743238 2967416))',2249), diminfo_))), 86.272430609366495)
def test_area(self): l = session.query(Lake).filter(Lake.lake_name == 'Lake Blue').one() assert_almost_equal(session.scalar(l.lake_geom.area), 0.10475991566721) ok_( session.query(Lake).filter( Lake.lake_geom.area > 0).first() is not None) assert_almost_equal( session.scalar( functions.area( WKTSpatialElement( 'POLYGON((743238 2967416,743238 2967450,743265 2967450,743265.625 2967416,743238 2967416))', 2249))), 928.625)
def test_buffer_with_tolerance(self): r = session.query(Road).filter(Road.road_name == "Graeme Ave").one() assert_almost_equal( session.scalar(functions.area(r.road_geom.buffer_with_tolerance(10.0, 20, 1))), 214.63894668789601 ) assert_almost_equal( session.scalar(functions.area(r.road_geom.buffer_with_tolerance(10.0, 20, 0))), 214.63894668789601 ) ok_( session.query(Spot) .filter(functions.within("POINT(-88.5945861592357 42.9480095987261)", Spot.spot_location.buffer(10))) .first() is not None ) assert_almost_equal( session.scalar( functions.area( ms_functions.buffer_with_tolerance("POINT(-88.5945861592357 42.9480095987261)", 10, 2, 0) ) ), 306.21843345678644, )
def test_buffer_with_tolerance(self): r = session.query(Road).filter(Road.road_name == 'Graeme Ave').one() assert_almost_equal( session.scalar( functions.area(r.road_geom.buffer_with_tolerance(10.0, 20, 1))), 214.63894668789601) assert_almost_equal( session.scalar( functions.area(r.road_geom.buffer_with_tolerance(10.0, 20, 0))), 214.63894668789601) ok_( session.query(Spot).filter( functions.within('POINT(-88.5945861592357 42.9480095987261)', Spot.spot_location.buffer(10))).first() is not None) assert_almost_equal( session.scalar( functions.area( ms_functions.buffer_with_tolerance( 'POINT(-88.5945861592357 42.9480095987261)', 10, 2, 0))), 306.21843345678644)
def test_area(self): l = session.query(Lake).filter(Lake.lake_name == "Lake Blue").one() assert_almost_equal(session.scalar(l.lake_geom.area), 0.10475991566721) ok_(session.query(Lake).filter(Lake.lake_geom.area > 0).first() is not None) assert_almost_equal( session.scalar( functions.area( WKTSpatialElement( "POLYGON((743238 2967416,743238 2967450,743265 2967450,743265.625 2967416,743238 2967416))", 2249, ) ) ), 928.625, )
def _create_layer_query(self, role_id): """ Create an SQLAlchemy query for Layer and for the role identified to by ``role_id``. """ q = DBSession.query(Layer).filter(Layer.public == True) # NOQA if role_id: q2 = DBSession.query(Layer) q2 = q2.join( (layer_ra, Layer.id == layer_ra.c.layer_id), (RestrictionArea, RestrictionArea.id == layer_ra.c.restrictionarea_id), (role_ra, role_ra.c.restrictionarea_id == RestrictionArea.id), (Role, Role.id == role_ra.c.role_id)) q2 = q2.filter(Role.id == role_id) q2 = q2.filter(and_( Layer.public != True, functions.area(RestrictionArea.area) > 0)) # NOQA q = q.union(q2) return q
def _create_layer_query(self, role_id): """ Create an SQLAlchemy query for Layer and for the role identified to by ``role_id``. """ q = DBSession.query(Layer).filter(Layer.public == True) # NOQA if role_id: q2 = DBSession.query(Layer) q2 = q2.join( (layer_ra, Layer.id == layer_ra.c.layer_id), (RestrictionArea, RestrictionArea.id == layer_ra.c.restrictionarea_id), (role_ra, role_ra.c.restrictionarea_id == RestrictionArea.id), (Role, Role.id == role_ra.c.role_id)) q2 = q2.filter(Role.id == role_id) q2 = q2.filter( and_(Layer.public != True, functions.area(RestrictionArea.area) > 0)) # NOQA q = q.union(q2) return q
def test_buffer(self): r = session.query(Road).filter(Road.road_name=='Graeme Ave').one() assert_almost_equal(session.scalar(functions.area(r.road_geom.buffer(10.0, 8))), 321.93380659099699) ok_(session.query(Spot).filter(functions.within('POINT(-88.5945861592357 42.9480095987261)', Spot.spot_location.buffer(10))).first() is not None) assert_almost_equal(session.scalar(functions.area(functions.buffer('POINT(-88.5945861592357 42.9480095987261)', 10, 2))), 282.84271247461902)
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"