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))