def _get_intersecting_boundaries_for_geom_value(engine, table_name, geom, return_intersection_area):
"""
Queries the given table for any boundaries that intersect the given geometry and returns the shape.
: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 == True:
# 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_SetSRID(geom, 4326))
).label('AREA_RET')
],
the_table.c.wkb_geometry.ST_Intersects(func.ST_SetSRID(geom, 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_SetSRID(geom, 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 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_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_list_services_for_ellipse(location: geodetic_ellipse, boundary_table, engine):
"""
Executes a contains query for a polygon.
:param location: location object
:type location: :py:class:Geodetic2D
:param boundary_table: The name of the service boundary table.
:type boundary_table: `str`
:param engine: SQLAlchemy database engine.
:type engine: :py:class:`sqlalchemy.engine.Engine`
:return: A list of dictionaries containing the contents of returned rows.
"""
# Pull out just the number from the SRID
try:
# Get a reference to the table we're going to look in.
tbl_metadata = MetaData(bind=engine)
the_table = Table(boundary_table, tbl_metadata, autoload=True)
wkb_ellipse = location.to_wkbelement(project_to=4326)
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_SetSRID(wkb_ellipse, 4326))).label(
'AREA_RET')
],
the_table.c.wkb_geometry.ST_Intersects(wkb_ellipse)
)
results = _execute_query(engine, s)
except SQLAlchemyError as ex:
logger.error(ex)
raise SpatialQueryException(
'Unable to construct ellipse intersection query.', ex)
except SpatialQueryException as ex:
logger.error(ex)
raise
return results