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