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}
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)
]
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
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'
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))
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]
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, {})
def latitude(self):
return db.session.scalar(ST_Y(self.location))
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]
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}')