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"
