Example #1
0
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
Example #2
0
 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()))
Example #3
0
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
Example #4
0
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