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 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 get_by_buffer(input_geom, buffer_size=100000):
sql = table.model.select().where(
func.ST_Intersects(
func.ST_setsrid(func.ST_Buffer(
input_geom,
buffer_size,
), 4326), table.model.c.geom
# func.ST_setsrid(table.model.c.geom,4326)
))
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 get_by_buffer(self, input_geom):
# new_geom = func.ST_GeomFromText(geom)
sql = self.model.select().where(
func.ST_Intersects(
func.ST_setsrid(
func.ST_Buffer(func.ST_GeomFromText(input_geom), 0.5,
"quad_segs=8"), 4326),
func.ST_setsrid(self.model.c.geom, 4326)))
result = connection.execute(sql).fetchall()
return result
def get_by_buffer(input_geom, cat_id=None, buffer_size=500):
sql = table.model.select().where(
and_(
func.ST_Intersects(
func.ST_setsrid(
func.ST_Buffer(func.ST_GeomFromText(input_geom),
buffer_size, "quad_segs=8"), 4326),
func.ST_setsrid(table.model.c.geom, 4326)),
table.model.c.category_id == cat_id))
# new_geom = func.ST_GeomFromText(geom)
if cat_id is not None:
sql = table.model.select().where(
func.ST_Intersects(
func.ST_setsrid(
func.ST_Buffer(func.ST_GeomFromText(input_geom),
buffer_size, "quad_segs=8"), 4326),
func.ST_setsrid(table.model.c.geom, 4326)))
results = connection.execute(sql).fetchall()
return results
def get_by_buffer(input_geom, buffer_size=100000):
sql = table.model.select().where(
func.ST_Intersects(
func.ST_setsrid(func.ST_Buffer(
input_geom,
buffer_size,
), 4326), table.model.c.geom
# func.ST_setsrid(table.model.c.geom,4326)
))
# new_geom = func.ST_GeomFromText(geom)
results = connection.execute(sql).fetchall()
return results
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 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 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))