def _get_surrounding_mis(self, position, date):
ret = set() # ([mis_id])
all_mises = self._db_session.query(metabase.Mis).all()
for mis in all_mises:
if not mis.geographic_position_compliant:
continue
if mis.shape is not None:
intersect = self._db_session.query(
ST_Intersects(
StGeogFromText(
'POINT(%s %s)' %
(position.Longitude, position.Latitude)),
mis.shape)).one()[0]
if not intersect:
continue
logging.debug("INTERSECTS SHAPE OF %s", mis.name)
if self._db_session.query(metabase.Stop.id) \
.filter(metabase.Stop.mis_id == mis.id) \
.filter(ST_DWithin(
metabase.Stop.geog,
StGeogFromText('POINT(%s %s)' % (position.Longitude, position.Latitude)),
self.SURROUNDING_MISES_MAX_DISTANCE)).count() > 0 and (mis.start_date <= date <= mis.end_date):
ret.add(mis.id)
logging.info("MIS surrounding point (%s %s): %s", position.Longitude,
position.Latitude, ret)
return ret
def search_communes(bbox):
coords = map(float, bbox.split(","))
bbox_wkb = from_shape(box(*coords), 4326)
table = db.table('communes',
db.column('insee_com'),
db.column('nom_comm'),
db.column('x_min'),
db.column('y_min'),
db.column('x_max'),
db.column('y_max'))
table.schema = 'geofla'
base = db.select(
[ table ]) \
.where(ST_Intersects(db.column('geom'), ST_Transform(bbox_wkb, 2154))) \
.order_by(db.desc(db.column("population")))
features = []
for row in db.engine.execute(base):
features.append({
"code": row['insee_com'],
"name": row['nom_comm'],
"text": row['insee_com'] + ' ' + row['nom_comm'],
"south_west": {
"lon" : round(row['x_min'], 6),
"lat": round(row['y_min'], 6)
},
"north_east": {
"lon": round(row['x_max'], 6),
"lat": round(row['y_max'], 6)
}
})
return features
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}')
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 postgres_filters(self, params):
if isinstance(params, basestring):
params = map(float, params.split(","))
xmin, ymin, xmax, ymax = params
return [
ST_Intersects(
Column('cell'),
ST_MakeBox2D(ST_Point(xmin, ymin), ST_Point(xmax, ymax)))
]
def search_communes():
query = request.args.get('q')
if not query:
return abort(401)
query = query.upper()
table = db.table('communes', db.column('insee_com'), db.column('nom_comm'),
db.column('x_min'), db.column('y_min'),
db.column('x_max'), db.column('y_max'))
table.schema = 'geofla'
base = db.select(
[ table ]) \
.where(db.column('nom_comm').contains(query)) \
.order_by(
db.desc(db.column('nom_comm').startswith(query)),
db.desc(db.column("population"))) \
.limit(GEOTAGS_GAZETTEER_MAX_RESULTS)
# Add BBOX support
# http://localhost:5000/v1/search/communes?q=BO&bbox=-1.4845,44.5004,0.3021,45.1539
bbox = request.args.get('bbox')
if bbox:
coords = map(float, bbox.split(","))
bbox_wkb = from_shape(box(*coords), 4326)
base = base.where(
ST_Intersects(db.column('geom'), ST_Transform(bbox_wkb, 2154)))
features = []
# TODO Order by distance to query location
for row in db.engine.execute(base):
features.append({
"code": row['insee_com'],
"text": row['insee_com'] + ' ' + row['nom_comm'],
"south_west": {
"lon": round(row['x_min'], 6),
"lat": round(row['y_min'], 6)
},
"north_east": {
"lon": round(row['x_max'], 6),
"lat": round(row['y_max'], 6)
}
})
return jsonify({
"max_results": GEOTAGS_GAZETTEER_MAX_RESULTS,
"results": features
})
def build_query_to_populate(self, query, full_table, aggregate_table):
insert_columns = [aggregate_table.c.join_key]
fk = Column(self.key, Integer)
geom = Column(self.geometry_column, Geometry())
bins_table = Table(self.table, full_table.metadata, fk, geom)
if self.join_custom_data:
extra_data = Table("extra_data",
full_table.metadata,
Column("timestamp", DateTime),
Column("verified", Boolean),
Column("bigquery_key", String),
Column("bigquery_test_id", String),
Column("connection_type", String),
Column("advertised_download", Integer),
Column("actual_download", Float),
Column("advertised_upload", Integer),
Column("actual_upload", Float),
Column("min_rtt", Integer),
Column("location_type", String),
Column("cost_of_service", Integer),
Column("location", Geometry("Point",
srid=4326)),
keep_existing=True)
joining = join(full_table,
extra_data,
and_(extra_data.c.bigquery_test_id == func.left(
full_table.c.test_id,
func.length(extra_data.c.bigquery_test_id))),
isouter=True)
query = query.select_from(joining)
location = case([(extra_data.c.verified,
func.coalesce(extra_data.c.location,
full_table.c.location))],
else_=full_table.c.location)
else:
location = full_table.c.location
select_query = (query.select_from(bins_table).where(
ST_Intersects(location, geom)).column(fk).group_by(fk))
return insert_columns, select_query
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 retrieve_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
record_count = int(db_session.query(ExtraData).count())
query = db_session.query(
ExtraData,
func.extract('epoch', ExtraData.timestamp).label('epoch'))
query = query.outerjoin(Maxmind,
Maxmind.ip_range.contains(ExtraData.client_ip))
query = query.add_columns(Maxmind.label)
for aggregation in aggregations:
query = query.outerjoin(
aggregation['orm'],
ST_Intersects(
ExtraData.location,
eval('aggregation["orm"].%s' %
aggregation['geometry_column'])))
query = query.add_columns(
eval('aggregation["orm"].%s' % aggregation['key']))
try:
results = query.limit(limit).offset(offset).all()
db_session.commit()
except:
db_session.rollback()
records = []
for row in results:
record = {}
record['id'] = row.ExtraData.id
record['date_pretty'] = row.ExtraData.timestamp
record['timestamp'] = int(row.epoch)
record['client_ip'] = row.ExtraData.client_ip
record['min_rtt'] = row.ExtraData.min_rtt
record['advertised_download'] = row.ExtraData.advertised_download
record['actual_download'] = row.ExtraData.actual_download
record['advertised_upload'] = row.ExtraData.advertised_upload
record['actual_upload'] = row.ExtraData.actual_upload
record['isp_user'] = row.ExtraData.isp_user
record['connection_type'] = row.ExtraData.connection_type
record['cost_of_service'] = row.ExtraData.cost_of_service
record['isp'] = rewrite_isp(row.label)
for aggregation in aggregations:
record[aggregation['table']] = eval('row.%s' % aggregation['key'])
records.append(record)
if len(records):
return (jsonify(record_count=record_count, records=records), 200, {})
else:
return ('', 500, {})
def retrieve_bq_results():
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
if request.args.get('range'):
try:
range_start, range_end = request.args.get('range').split(',')
except:
pass
else:
range_start = None
range_end = None
query = db_session.query(
Results,
func.extract('epoch', Results.time).label('timestamp'))
if range_start and range_end:
query = query.filter(
func.extract('epoch', Results.time) >= range_start)
query = query.filter(func.extract('epoch', Results.time) <= range_end)
query = query.order_by(desc(
Results.time)).limit(limit).offset(offset).from_self()
query = query.outerjoin(Maxmind,
Maxmind.ip_range.contains(Results.client_ip))
query = query.add_columns(Maxmind.label)
for aggregation in aggregations:
query = query.outerjoin(
aggregation['orm'],
ST_Intersects(
Results.location,
eval('aggregation["orm"].%s' %
aggregation['geometry_column'])))
query = query.add_columns(
eval('aggregation["orm"].%s' % aggregation['key']))
# record_count = db_session.query(Results.id).count()
try:
results = query.all()
db_session.commit()
except:
db_session.rollback()
records = []
for row in results:
record = {}
record['id'] = row.Results.id
record['timestamp'] = int(row.timestamp)
record['client_ip'] = row.Results.client_ip
record['server_ip'] = row.Results.server_ip
record['count_rtt'] = row.Results.countrtt
record['sum_rtt'] = row.Results.sumrtt
record['download_flag'] = row.Results.download_flag
record['download_time'] = row.Results.download_time
record['download_octets'] = row.Results.download_octets
record['upload_time'] = row.Results.upload_time
record['upload_octets'] = row.Results.upload_octets
record['bigquery_key'] = row.Results.bigquery_key
record['test_id'] = row.Results.test_id
record['isp_user'] = rewrite_isp(row.label)
for aggregation in aggregations:
record[aggregation['table']] = eval('row.%s' % aggregation['key'])
records.append(record)
if len(records):
return (jsonify(records=records), 200, {})
else:
return ('', 500, {})
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]