def geojson_bbox(bounds):
bounds_parts = bounds.split(',')
line_result = get_base_line_query().filter(DbLine.maxheight.isnot(None)).filter(
func.ST_Intersects(
func.ST_MakeEnvelope(
bounds_parts[0],
bounds_parts[1],
bounds_parts[2],
bounds_parts[3],
4326
),
func.ST_Transform(func.ST_Centroid(DbLine.way),4326),
)
).all()
point_result = get_base_point_query().filter(DbPoint.maxheight.isnot(None)).filter(
func.ST_Intersects(
func.ST_MakeEnvelope(
float(bounds_parts[0]),
float(bounds_parts[1]),
float(bounds_parts[2]),
float(bounds_parts[3]),
4326
),
func.ST_Transform(DbPoint.way,4326),
)
).all()
return construct_geojson(line_result + point_result)
def with_properties(self):
if request.args.get('bounds') is None:
return Response(json.dumps([]), mimetype='application/json')
bounds_parts = request.args.get("bounds").split(',')
points = Point.query.filter(func.ST_Contains(func.ST_MakeEnvelope(bounds_parts[1], bounds_parts[0], bounds_parts[3], bounds_parts[2]), Point.geom)).filter(Point.approved).all()
points = list(map(lambda point: point.serialize_with_properties(), points))
return Response(json.dumps(points), mimetype='application/json')
def get_filtered_relations(bounds, voltages, countries, zoom):
powerlines_qry = TransnetPowerline.query
if bounds:
powerlines_qry = powerlines_qry.filter(
func.ST_Intersects(
func.ST_MakeEnvelope(bounds[1], bounds[0], bounds[3],
bounds[2]),
cast(TransnetPowerline.geom, Geography)))
if countries:
powerlines_qry = powerlines_qry.filter(
TransnetPowerline.country.in_(countries))
if voltages:
powerlines_qry = powerlines_qry.filter(
TransnetPowerline.relations.any(
TransnetRelation.voltage.in_(voltages)))
powerline_relations = powerlines_qry.options(
joinedload('relations')).all()
return TransnetRelation.prepare_relations_for_export(
powerline_relations, [], zoom, voltages)
def geojson_bbox(bounds):
bounds_parts = bounds.split(',')
result = get_base_query().filter(
func.ST_Intersects(
func.ST_MakeEnvelope(bounds_parts[0], bounds_parts[1],
bounds_parts[2], bounds_parts[3], 4326),
DbStop.point.ST_Transform(4326),
)).all()
return construct_geojson(result)
def geojson_bbox(bounds):
bounds_parts = bounds.split(',')
result = get_base_query().filter(
func.ST_Crosses(
func.ST_MakeEnvelope(bounds_parts[0], bounds_parts[1],
bounds_parts[2], bounds_parts[3], 4326),
DbEnvironmentalZone.wkb_geometry.ST_Transform(4326),
)).all()
return construct_geojson(result)
def list_cube_items(self,
cube_id: str,
bbox: str = None,
start: str = None,
end: str = None,
tiles: str = None,
page: int = 1,
per_page: int = 10):
"""Retrieve all data cube items done."""
cube = self.get_cube_or_404(cube_id=cube_id)
where = [Item.collection_id == cube.id, Tile.id == Item.tile_id]
# temporal filter
if start:
where.append(Item.start_date >= start)
if end:
where.append(Item.end_date <= end)
# tile(string) filter
if tiles:
tiles = tiles.split(',') if isinstance(tiles, str) else tiles
where.append(Tile.name.in_(tiles))
# spatial filter
if bbox:
xmin, ymin, xmax, ymax = [
float(coord) for coord in bbox.split(',')
]
where.append(
func.ST_Intersects(
func.ST_SetSRID(Item.geom, 4326),
func.ST_MakeEnvelope(xmin, ymin, xmax, ymax, 4326)))
paginator = db.session.query(Item).filter(*where).order_by(
Item.start_date.desc()).paginate(int(page),
int(per_page),
error_out=False)
result = []
for item in paginator.items:
obj = Serializer.serialize(item)
obj['geom'] = None
obj['min_convex_hull'] = None
obj['tile_id'] = item.tile.name
if item.assets.get('thumbnail'):
obj['quicklook'] = item.assets['thumbnail']['href']
del obj['assets']
result.append(obj)
return dict(items=result,
page=page,
per_page=page,
total_items=paginator.total,
total_pages=paginator.pages), 200
def index(self):
if request.args.get('bounds') is None:
return Response(json.dumps([]), mimetype='application/json')
bounds_parts = request.args.get("bounds").split(',')
zoom = int(request.args.get("zoom") or 1)
powerlines = []
if int(zoom) < 12:
if int(zoom) < 8:
# min_lenth is a multiplicator to reduce the number of shown powerlines based on their length and
# zoom level
min_length = 15000
else:
min_length = 10000
powerlines = Powerline.query.filter(
func.ST_Intersects(
func.ST_MakeEnvelope(bounds_parts[1], bounds_parts[0],
bounds_parts[3], bounds_parts[2]),
Powerline.geom)).filter(
func.ST_Length(
func.ST_GeographyFromText(
func.ST_AsText(Powerline.geom))) > min_length *
(10 - zoom)).all()
else:
powerlines = Powerline.query.filter(
func.ST_Intersects(
func.ST_MakeEnvelope(bounds_parts[1], bounds_parts[0],
bounds_parts[3], bounds_parts[2]),
Powerline.geom)).all()
powerlines = list(
map(lambda powerline: powerline.serialize(), powerlines))
return Response(json.dumps(powerlines), mimetype='application/json')
def get_grs_schema(cls,
grs_id,
bbox: Tuple[float, float, float, float] = None,
tiles=None):
"""Retrieve a Grid Schema definition with tiles associated."""
schema: GridRefSys = GridRefSys.query().filter(
GridRefSys.id == grs_id).first()
if schema is None:
return 'GRS {} not found.'.format(grs_id), 404
geom_table = schema.geom_table
srid_column = get_srid_column(geom_table.c, default_srid=4326)
where = []
if bbox is not None:
x_min, y_min, x_max, y_max = bbox
where.append(
func.ST_Intersects(
func.ST_MakeEnvelope(x_min, y_min, x_max, y_max, 4326),
func.ST_Transform(
func.ST_SetSRID(geom_table.c.geom, srid_column),
4326)))
if tiles:
where.append(geom_table.c.tile.in_(tiles))
tiles = db.session.query(
geom_table.c.tile,
func.ST_AsGeoJSON(
func.ST_Transform(
func.ST_SetSRID(geom_table.c.geom, srid_column), 4326), 6,
3).cast(
sqlalchemy.JSON).label('geom_wgs84')).filter(*where).all()
dump_grs = Serializer.serialize(schema)
dump_grs['tiles'] = [
dict(id=t.tile, geom_wgs84=t.geom_wgs84) for t in tiles
]
return dump_grs, 200
def index(self):
if request.args.get('bounds') is None:
return Response(json.dumps([]), mimetype='application/json')
bounds_parts = request.args.get("bounds").split(',')
points = Point.query.filter(
func.ST_Contains(
func.ST_MakeEnvelope(
bounds_parts[1],
bounds_parts[0],
bounds_parts[3],
bounds_parts[2]),
Point.geom
)
).filter(
Point.deleted_by_user.isnot(True)
).filter(
or_(Point.approved, Point.revised == False)
).all()
points = list(map(lambda point: point.serialize(), points))
return Response(json.dumps(points), mimetype='application/json')
def get_filtered_stations(bounds, general_filter, stations_filter):
if not len(stations_filter):
return []
power_stations_sample = db.session.query(TransnetPowerStationMissingData.id) \
.filter(TransnetPowerStationMissingData.type.in_(stations_filter)) \
.filter(
func.ST_Intersects(
func.ST_MakeEnvelope(
bounds[1],
bounds[0],
bounds[3],
bounds[2]
),
cast(TransnetPowerStationMissingData.geom, Geography)
)
)
if 'voltage' not in general_filter:
power_stations_sample = power_stations_sample.filter(
TransnetPowerStationMissingData.voltage != {0})
if 'connection' not in general_filter:
power_stations_sample = power_stations_sample.filter(
TransnetPowerStationMissingData.missing_connection == False)
power_stations_sample = power_stations_sample.all()
if len(power_stations_sample) > 5000:
power_stations_sample = random.sample(power_stations_sample, 5000)
power_stations = TransnetPowerStationMissingData.query.filter(
TransnetPowerStationMissingData.id.in_(
power_stations_sample)).all()
return list(map(lambda s: s.serialize(), power_stations))
def points_of_crowdsourcing_day(self):
if request.args.get('bounds') is None:
return Response(json.dumps([]), mimetype='application/json')
bounds_parts = request.args.get("bounds").split(',')
points = Point.query.filter(
func.ST_Contains(
func.ST_MakeEnvelope(
bounds_parts[1],
bounds_parts[0],
bounds_parts[3],
bounds_parts[2]),
Point.geom
)
).filter(
Point.deleted_by_user.isnot(True)
).filter(
or_(Point.approved, Point.revised == False)
).filter(
or_(func.substr(Point.properties[('tags', 'timestamp')].astext, 1, 10) == "2017-05-09")
).all()
points = list(map(lambda point: point.serialize(), points))
return Response(json.dumps(points), mimetype='application/json')
def get_filtered_lines(bounds, general_filter, lines_filter):
if not len(lines_filter):
return []
powerlines_sample = db.session.query(TransnetPowerLineMissingData.id) \
.filter(TransnetPowerLineMissingData.type.in_(lines_filter)) \
.filter(
func.ST_Intersects(
func.ST_MakeEnvelope(
bounds[1],
bounds[0],
bounds[3],
bounds[2]
),
cast(TransnetPowerLineMissingData.geom, Geography)
)
)
if 'voltage' not in general_filter:
powerlines_sample = powerlines_sample.filter(
TransnetPowerLineMissingData.voltage != {0})
if 'cable' not in general_filter:
powerlines_sample = powerlines_sample.filter(
TransnetPowerLineMissingData.cables != 0)
powerlines_sample = powerlines_sample.all()
if len(powerlines_sample) > 1000:
powerlines_sample = random.sample(powerlines_sample, 1000)
powerlines = TransnetPowerLineMissingData.query.filter(
TransnetPowerLineMissingData.id.in_(powerlines_sample)).all()
return list(map(lambda powerline: powerline.serialize(), powerlines))