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 _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_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_default_geom(linked_docs):
if not linked_docs:
return None
linked_waypoint_ids = [d['document_id'] for d in linked_docs]
default_geom = DBSession. \
query(func.ST_SetSRID(func.ST_Centroid(func.ST_ConvexHull(
func.ST_Collect(DocumentGeometry.geom))), 3857)). \
filter(DocumentGeometry.document_id.in_(linked_waypoint_ids)). \
scalar()
return default_geom
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
