Ejemplo n.º 1
0
class CreationRequest(Request):
    id = Column(UUIDType, ForeignKey(Request.id), primary_key=True)

    name = Column(Unicode, nullable=False)
    category = Column(Unicode, nullable=False)
    status = Column(Enum(BusinessEntityStatus), nullable=False)

    address = Column(Unicode, nullable=False)
    address_sub = Column(Unicode, nullable=False)
    coordinate = Column(Geometry(geometry_type='POINT'), nullable=False)
    latitude = column_property(ST_X(coordinate))
    longitude = column_property(ST_Y(coordinate))

    def create(self) -> 'BusinessEntity':
        assert not self.committed, 'This revision has already committed'
        revision = BusinessEntityRevision(request=self,
                                          name=self.name,
                                          category=self.category,
                                          status=self.status,
                                          address=self.address,
                                          address_sub=self.address_sub,
                                          coordinate=self.coordinate)
        new = BusinessEntity()
        new.latest_revision = revision
        new.first_revision = revision
        return new

    __tablename__ = 'creation_request'
    __mapper_args__ = {'polymorphic_identity': RequestKind.creation}
Ejemplo n.º 2
0
 def postgres_aggregates(self, resolution):
     if isinstance(resolution, basestring):
         try:
             resolution = float(resolution)
         except ValueError:
             resolution = self.resolution
     return [
         label('cell_x',
               func.floor(ST_X(Column('cell')) / resolution) * resolution),
         label('cell_y',
               func.floor(ST_Y(Column('cell')) / resolution) * resolution)
     ]
Ejemplo n.º 3
0
def test_closest_stns_within_threshold_bad_data(ec_session):
    # https://github.com/pacificclimate/crmprtd/issues/8

    # Find some "good data" to use for the test run
    x, y = ec_session.query(ST_X(History.the_geom),
                            ST_Y(History.the_geom)).first()

    # Create a couple history entries with reversed lat/lons
    space1 = History(station_name="Outer space",
                     the_geom="SRID=4326;POINT(49.1658 -122.9606)")
    space2 = History(station_name="Outer space",
                     the_geom="SRID=4326;POINT(50.3225 122.7897)")
    ec_session.add_all([space1, space2])
    ec_session.commit()

    # Just search for the good station and ensure there are not errors
    x = closest_stns_within_threshold(ec_session, "EC_raw", x, y, 1)
    assert len(x) > 0
Ejemplo n.º 4
0
class BusinessEntityRevision(Base):
    id = Column(UUIDType, primary_key=True, default=uuid.uuid4)

    replacing_id = Column(UUIDType, ForeignKey('business_entity_revision.id'))
    replacing = relationship('BusinessEntityRevision',
                             remote_side=id,
                             uselist=False)
    replaced_by = relationship('BusinessEntityRevision',
                               remote_side=replacing_id,
                               uselist=False)

    created_at = Column(UtcDateTime,
                        nullable=False,
                        default=utcnow(),
                        index=True)

    request_id = Column(UUIDType,
                        ForeignKey(Request.id),
                        nullable=False,
                        unique=True)
    request = relationship(Request,
                           uselist=False,
                           backref=backref('revision', uselist=False))

    name = Column(Unicode, nullable=False)
    category = Column(Unicode, nullable=False, index=True)
    status = Column(Enum(BusinessEntityStatus), nullable=False)

    address = Column(Unicode, nullable=False)
    address_sub = Column(Unicode, nullable=False)
    coordinate = Column(Geometry(geometry_type='POINT'),
                        nullable=False,
                        index=True)
    latitude = column_property(ST_X(coordinate))
    longitude = column_property(ST_Y(coordinate))

    __tablename__ = 'business_entity_revision'
Ejemplo n.º 5
0
def point_elevation(geometry, format_out, dataset):
    """
    Performs PostGIS query to enrich a point geometry.
    
    :param geometry: Input point to be enriched with elevation
    :type geometry: shapely.geometry.Point
    
    :param format_out: Specifies output format. One of ['geojson', 'point']
    :type format_out: string
    
    :param dataset: Elevation dataset to use for querying
    :type dataset: string
    
    :raises InvalidUsage: internal HTTP 500 error with more detailed description.
    
    :returns: 3D Point as GeoJSON or WKT
    :rtype: string
    """

    Model = _getModel(dataset)

    if geometry.geom_type == "Point":
        query_point2d = db.session \
                            .query(func.ST_SetSRID(func.St_PointFromText(geometry.wkt), 4326).label('geom')) \
                            .subquery() \
                            .alias('points2d')

        query_getelev = db.session \
                            .query(query_point2d.c.geom,
                                   ST_Value(Model.rast, query_point2d.c.geom).label('z')) \
                            .filter(ST_Intersects(Model.rast, query_point2d.c.geom)) \
                            .subquery().alias('getelevation')

        if format_out == 'geojson':
            query_final = db.session \
                                .query(func.ST_AsGeoJSON(ST_SnapToGrid(func.ST_MakePoint(ST_X(query_getelev.c.geom),
                                                                                           ST_Y(query_getelev.c.geom),
                                                                                           query_getelev.c.z),
                                                                        coord_precision)))
        else:
            query_final = db.session \
                                .query(func.ST_AsText(ST_SnapToGrid(func.ST_MakePoint(ST_X(query_getelev.c.geom),
                                                                                       ST_Y(query_getelev.c.geom),
                                                                                       query_getelev.c.z),
                                                                    coord_precision)))
    else:
        raise InvalidUsage(
            400, 4002,
            "Needs to be a Point, not {}!".format(geometry.geom_type))

    try:
        return query_final[0][0]
    except:
        raise InvalidUsage(
            404, 4002,
            'The requested geometry is outside the bounds of {}'.format(
                dataset))
Ejemplo n.º 6
0
def line_elevation(geometry, format_out, dataset):
    """
    Performs PostGIS query to enrich a line geometry.
    
    :param geometry: Input 2D line to be enriched with elevation
    :type geometry: Shapely geometry
    
    :param format_out: Specifies output format. One of ['geojson', 'polyline',
        'encodedpolyline']
    :type format_out: string
    
    :param dataset: Elevation dataset to use for querying
    :type dataset: string
    
    :raises InvalidUsage: internal HTTP 500 error with more detailed description. 
        
    :returns: 3D line as GeoJSON or WKT
    :rtype: string
    """

    Model = _getModel(dataset)

    if geometry.geom_type == 'LineString':
        query_points2d = db.session\
                            .query(func.ST_SetSRID(ST_DumpPoints(geometry.wkt).geom, 4326) \
                            .label('geom')) \
                            .subquery().alias('points2d')

        query_getelev = db.session \
                            .query(query_points2d.c.geom,
                                   ST_Value(Model.rast, query_points2d.c.geom).label('z')) \
                            .filter(ST_Intersects(Model.rast, query_points2d.c.geom)) \
                            .subquery().alias('getelevation')

        query_points3d = db.session \
                            .query(func.ST_SetSRID(func.ST_MakePoint(ST_X(query_getelev.c.geom),
                                                                     ST_Y(query_getelev.c.geom),
                                                                     query_getelev.c.z),
                                              4326).label('geom')) \
                            .subquery().alias('points3d')

        if format_out == 'geojson':
            # Return GeoJSON directly in PostGIS
            query_final = db.session \
                              .query(func.ST_AsGeoJson(func.ST_MakeLine(ST_SnapToGrid(query_points3d.c.geom, coord_precision))))

        else:
            # Else return the WKT of the geometry
            query_final = db.session \
                              .query(func.ST_AsText(func.ST_MakeLine(ST_SnapToGrid(query_points3d.c.geom, coord_precision))))
    else:
        raise InvalidUsage(
            400, 4002,
            "Needs to be a LineString, not a {}!".format(geometry.geom_type))

    # Behaviour when all vertices are out of bounds
    if query_final[0][0] == None:
        raise InvalidUsage(
            404, 4002,
            'The requested geometry is outside the bounds of {}'.format(
                dataset))

    return query_final[0][0]
Ejemplo n.º 7
0
def admin_extra_data():
    if request.args.get('limit'):
        limit = int(request.args.get('limit'))
    else:
        limit = 50

    if request.args.get('page'):
        offset = (int(request.args.get('page')) - 1) * limit
    else:
        offset = 0

    order_by = ExtraData.id.desc()
    sort_fields = [
        'id', 'timestamp', 'advertised_download', 'actual_download',
        'advertised_upload', 'actual_upload', 'min_rtt', 'cost_of_service',
        'isp_user', 'connection_type', 'verified'
    ]

    if request.args.get('sort'):
        if request.args.get('sort') in sort_fields:
            if request.args.get('order'):
                if request.args.get('order') in ['desc', 'asc']:
                    order_by = eval('ExtraData.%s.%s()' % (request.args.get('sort'),\
                            request.args.get('order')), {"__builtins__": None},\
                            {"ExtraData": ExtraData})
                else:
                    order_by = eval('ExtraData.%s.%s()' % (request.args.get('sort'),\
                            request.args.get('order')), {"__builtins__": None},\
                            {"ExtraData": ExtraData})
            else:
                order_by = eval('ExtraData.%s.desc()' % request.args.get('sort'),\
                        {"__builtins__": None}, {"ExtraData": ExtraData})

    try:
        record_count = int(db_session.query(ExtraData).count())
        results = db_session.query(
            ExtraData,
            ST_X(ExtraData.location).label('lon'),
            ST_Y(ExtraData.location).label('lat')).order_by(order_by).limit(
                limit).offset(offset).all()
        db_session.commit()
    except:
        db_session.rollback()

    records = []
    for row in results:
        record = {}
        record['id'] = row[0].id
        record['bigquery_key'] = row[0].bigquery_key
        record['bigquery_test_id'] = row[0].bigquery_test_id
        record['verified'] = row[0].verified
        record['timestamp'] = int(row[0].timestamp.strftime('%s')) * 1000
        record['connection_type'] = row[0].connection_type
        record['isp_user'] = row[0].isp_user
        record['advertised_download'] = row[0].advertised_download
        record['actual_download'] = row[0].actual_download
        record['advertised_upload'] = row[0].advertised_upload
        record['actual_upload'] = row[0].actual_upload
        record['min_rtt'] = row[0].min_rtt
        record['cost_of_service'] = row[0].cost_of_service
        record['latitude'] = row.lat
        record['longitude'] = row.lon
        records.append(record)

    if len(records):
        return (jsonify(record_count=record_count, records=records), 200, {})
    else:
        return ('', 500, {})
Ejemplo n.º 8
0
 def latitude(self):
     return db.session.scalar(ST_Y(self.location))
Ejemplo n.º 9
0
def line_elevation(geometry, format_out, dataset):
    """
    Performs PostGIS query to enrich a line geometry.
    
    :param geometry: Input 2D line to be enriched with elevation
    :type geometry: Shapely geometry
    
    :param format_out: Specifies output format. One of ['geojson', 'polyline',
        'encodedpolyline']
    :type format_out: string
    
    :param dataset: Elevation dataset to use for querying
    :type dataset: string
    
    :raises InvalidUsage: internal HTTP 500 error with more detailed description. 
        
    :returns: 3D line as GeoJSON or WKT
    :rtype: string
    """

    Model = _getModel(dataset)
    input_crs = _get_crs(dataset)

    if geometry.geom_type == 'LineString':
        query_points2d = db.session\
                            .query(ST_Transform(func.ST_SetSRID(ST_DumpPoints(geometry.wkt).geom, 4326), input_crs) \
                            .label('geom')) \
                            .subquery().alias('points2d')

        query_getelev = db.session \
                            .query(ST_Transform(query_points2d.c.geom, 4326).label('geom'),
                                   ST_Value(Model.rast, query_points2d.c.geom, False).label('z')) \
                            .filter(ST_Intersects(Model.rast, query_points2d.c.geom)) \
                            .subquery().alias('getelevation')

        query_points3d = db.session \
                            .query(func.ST_MakePoint(ST_X(query_getelev.c.geom),
                                                                     ST_Y(query_getelev.c.geom),
                                                                     query_getelev.c.z.cast(Integer)
                                                     ).label('geom')) \
                            .subquery().alias('points3d')

        if format_out == 'geojson':
            # Return GeoJSON directly in PostGIS
            query_final = db.session \
                              .query(func.ST_AsGeoJson(func.ST_MakeLine(ST_SnapToGrid(query_points3d.c.geom, coord_precision))))

        else:
            # Else return the WKT of the geometry
            query_final = db.session \
                              .query(func.ST_AsText(func.ST_MakeLine(ST_SnapToGrid(query_points3d.c.geom, coord_precision))))
    else:
        raise InvalidUsage(
            400, 4002,
            "Needs to be a LineString, not a {}!".format(geometry.geom_type))

    try:
        result = query_final.one()
    except NoResultFound:
        raise InvalidUsage(
            404, 4002,
            f'{tuple(geometry.coords)[0]} has no elevation value in {dataset}')

    return result[0]
Ejemplo n.º 10
0
def point_elevation(geometry, format_out, dataset):
    """
    Performs PostGIS query to enrich a point geometry.
    
    :param geometry: Input point to be enriched with elevation
    :type geometry: shapely.geometry.Point
    
    :param format_out: Specifies output format. One of ['geojson', 'point']
    :type format_out: string
    
    :param dataset: Elevation dataset to use for querying
    :type dataset: string
    
    :raises InvalidUsage: internal HTTP 500 error with more detailed description.
    
    :returns: 3D Point as GeoJSON or WKT
    :rtype: string
    """

    Model = _getModel(dataset)
    input_crs = _get_crs(dataset)

    if geometry.geom_type == "Point":
        query_point2d = db.session \
                            .query(ST_Transform(func.ST_SetSRID(func.St_PointFromText(geometry.wkt), 4326), input_crs).label('geom')) \
                            .subquery() \
                            .alias('points2d')

        query_getelev = db.session \
                            .query(ST_Transform(query_point2d.c.geom, 4326).label('geom'),
                                   ST_Value(Model.rast, query_point2d.c.geom, False).label('z')) \
                            .filter(ST_Intersects(Model.rast, query_point2d.c.geom)) \
                            .subquery().alias('getelevation')

        if format_out == 'geojson':
            query_final = db.session \
                .query(func.ST_AsGeoJSON(ST_SnapToGrid(func.ST_MakePoint(ST_X(query_getelev.c.geom),
                                                                                           ST_Y(query_getelev.c.geom),
                                                                                           query_getelev.c.z.cast(Integer)),
                                                                        coord_precision)))
        else:
            query_final = db.session \
                                .query(func.ST_AsText(ST_SnapToGrid(func.ST_MakePoint(ST_X(query_getelev.c.geom),
                                                                                       ST_Y(query_getelev.c.geom),
                                                                                       query_getelev.c.z.cast(Integer)),
                                                                    coord_precision)))
    else:
        raise InvalidUsage(
            400, 4002,
            "Needs to be a Point, not {}!".format(geometry.geom_type))

    try:
        result = query_final.one()
        return result[0]
    except NoResultFound:
        raise InvalidUsage(
            404, 4002,
            f'{tuple(geometry.coords)[0]} has no elevation value in {dataset}')