def _get_additional_data_for_geometry_with_buffer(engine, geom, table_name, buffer_distance, utmsrid):
try:
# Get a reference to the table we're going to look in.
tbl_metadata = MetaData(bind=engine)
the_table = Table(table_name, tbl_metadata, autoload=True)
s = select(
[the_table],
func.ST_Intersects(
func.ST_Buffer(
func.ST_Transform(
func.ST_SetSRID(geom, 4326),
utmsrid
),
buffer_distance
),
the_table.c.wkb_geometry.ST_Transform(utmsrid)
)
)
results = _execute_query(engine, s)
return results
except SQLAlchemyError as ex:
logger.error(ex)
raise SpatialQueryException(
'Unable to construct intersection query.', ex)
except SpatialQueryException as ex:
logger.error(ex)
raise
return None
def _get_intersecting_boundaries_for_geom(engine, table_name, geom, return_intersection_area):
"""
Queries the given table for any boundaries that intersect the given geometry.
:param engine: SQLAlchemy database engine
:type engine: :py:class:`sqlalchemy.engine.Engine`
:param table_name: The name of the service boundary table.
:type table_name: `str`
:param geom: The geometry to use in the search as a GeoAlchemy WKBElement.
:type geom: :py:class:geoalchemy2.types.WKBElement
:return: A list of dictionaries containing the contents of returned rows.
"""
retval = None
try:
# Get a reference to the table we're going to look in.
tbl_metadata = MetaData(bind=engine)
the_table = Table(table_name, tbl_metadata, autoload=True)
# s = select([the_table, the_table.c.wkb_geometry.ST_AsGML()], the_table.c.wkb_geometry.ST_Contains(geom))
# Construct the "intersection" query and execute
if return_intersection_area:
# include a calculation for the intersecting the area
s = select(
[the_table,
func.ST_AsGML(3, the_table.c.wkb_geometry, 15, 16),
func.ST_Area(the_table.c.wkb_geometry.ST_Intersection(func.ST_Transform(func.ST_SetSRID(geom, geom.srid),4326))).label('AREA_RET')
],
the_table.c.wkb_geometry.ST_Intersects(func.ST_Transform(func.ST_SetSRID(geom, geom.srid),4326)))
else:
s = select(
[the_table,
func.ST_AsGML(3, the_table.c.wkb_geometry, 15, 16)],
the_table.c.wkb_geometry.ST_Intersects(func.ST_Transform(func.ST_SetSRID(geom, geom.srid),4326)))
results = _execute_query(engine, s)
except SQLAlchemyError as ex:
logger.error(ex)
raise SpatialQueryException(
'Unable to construct intersection query.', ex)
except SpatialQueryException as ex:
logger.error(ex)
raise
return results
def get_intersecting_boundaries_with_buffer(long, lat, engine, table_name, geom, buffer_distance, return_intersection_area = False):
retval = None
try:
# Get a reference to the table we're going to look in.
tbl_metadata = MetaData(bind=engine)
the_table = Table(table_name, tbl_metadata, autoload=True)
# Construct the "contains" query and execute it.
utmsrid = gc_geom.getutmsrid(long, lat, geom.srid)
if return_intersection_area:
# include a calculation for the intersecting the area
s = select([the_table, the_table.c.wkb_geometry.ST_AsGML(), func.ST_Area(
func.ST_Intersection(
func.ST_Buffer(func.ST_Transform(func.ST_SetSRID(geom, geom.srid), utmsrid), buffer_distance), the_table.c.wkb_geometry.ST_Transform(utmsrid))).label(
'AREA_RET')],
func.ST_Intersects(
func.ST_Buffer(func.ST_Transform(func.ST_SetSRID(geom, geom.srid), utmsrid), buffer_distance),
the_table.c.wkb_geometry.ST_Transform(utmsrid)))
else:
s = select([the_table, the_table.c.wkb_geometry.ST_AsGML()],
func.ST_Intersects(func.ST_Buffer(func.ST_Transform(func.ST_SetSRID(geom,4326), utmsrid), buffer_distance),
the_table.c.wkb_geometry.ST_Transform(utmsrid)))
retval = _execute_query(engine, s)
except SQLAlchemyError as ex:
logger.error(ex)
raise SpatialQueryException(
'Unable to construct contains query.', ex)
except SpatialQueryException as ex:
logger.error(ex)
raise
return retval
def _get_nearest_point(long, lat, engine, table_name, geom, buffer_distance=None):
"""
Queries the given table for the nearest boundary
:param engine: SQLAlchemy database engine
:type engine: :py:class:`sqlalchemy.engine.Engine`
:param table_name: The name of the service boundary table.
:type table_name: `str`
:param geom: The geometry to use in the search as a GeoAlchemy WKBElement.
:type geom: :py:class:geoalchemy2.types.WKBElement
:return: A list of dictionaries containing the contents of returned rows.
"""
retval = None
try:
# Get a reference to the table we're going to look in.
tbl_metadata = MetaData(bind=engine)
the_table = Table(table_name, tbl_metadata, autoload=True)
# Construct the "contains" query and execute it.
utmsrid = gc_geom.getutmsrid(long, lat)
s = select([the_table, the_table.c.wkb_geometry.ST_AsGML(),
the_table.c.wkb_geometry.ST_Distance(geom).label('DISTANCE')],
the_table.c.wkb_geometry.ST_Intersects(
func.ST_Transform(
func.ST_Buffer(
func.ST_Transform(
func.st_centroid(geom),
utmsrid
),buffer_distance, 32), 4326))
).order_by('DISTANCE').limit(1)
retval = _execute_query(engine, s)
except SQLAlchemyError as ex:
logger.error(ex)
raise SpatialQueryException(
'Unable to construct contains query.', ex)
except SpatialQueryException as ex:
logger.error(ex)
raise
return retval
def generate(self):
res = self._db.Result(
code=self.get_code(),
name=self.name or "Anglican Parish of {}".format(self.get_code()),
definition=self.__doc__,
geom=func.ST_Transform(func.ST_Multi(self.geom()), 4326),
problems=getattr(self, "problems", ""),
)
self.session.add(res)
self.session.commit()
area = self.session.query(func.ST_Area(self._db.Result.geom)).filter(
self._db.Result.code == self.get_code())[0][0]
if area is None or area == 0:
print("generation failed, empty geometry: {}".format(
self.get_code()))
def get_cut(self, db):
f = func.ST_Multi(
func.ST_GeomFromGeoJSON(json.dumps(self._paths[0].get_path(db))))
for p in self._paths[1:]:
f = func.ST_Union(
f, func.ST_GeomFromGeoJSON(json.dumps(p.get_path(db))))
session = db.session()
try:
q = session.query(f)
# log for debugging purposes
session.add(
db.Cut(description=self._description,
geom=func.ST_Transform(q, 4326)))
session.commit()
return q.one()[0]
finally:
session.close()
def _get_path(self, db):
gj = json.dumps({
"crs": {
"type": "name",
"properties": {
"name": "EPSG:4326"
}
},
"type": "LineString",
"coordinates": self._coords,
})
session = db.session()
try:
return json.loads(
session.query(
func.ST_AsGeoJSON(
func.ST_Transform(
func.ST_Multi(func.ST_GeomFromGeoJSON(gj)),
3857))).one()[0])
finally:
session.close()