def _load_boundaries_tables(self, extent): multipolygon_cast = Geometry(geometry_type='MULTIPOLYGON', srid=4326) multilinestring_cast = Geometry(geometry_type='MULTILINESTRING', srid=4326) table_casts = { 'sea_a': multipolygon_cast, 'landmass_a': multipolygon_cast, 'coastline_l': multilinestring_cast, } for table_name in self._osm_boundaries_tables: source_table_meta = self._table_metas[table_name] query = select([ source_table_meta.c.ogc_fid, source_table_meta.c.fid, source_table_meta.c.wkb_geometry ]) query = query.where( func.ST_Intersects(source_table_meta.c.wkb_geometry, extent.ewkt)) self._execute_and_insert_into_local_db( query, source_table_meta, source_engine=self._osm_boundaries_db_engine) from sqlalchemy_views import CreateView view_definition_query = select([ source_table_meta.c.ogc_fid, source_table_meta.c.fid, expression.cast( func.ST_Multi( func.ST_Intersection(source_table_meta.c.wkb_geometry, extent.ewkt)), table_casts[table_name]).label('geom') ]).where( func.ST_Intersects(source_table_meta.c.wkb_geometry, extent.ewkt)) view_meta = MetaData() view = Table(table_name, view_meta, schema='view_osmaxx') from sqlalchemy.dialects import postgresql from sqlalchemy.sql import text query_defintion_string = str( view_definition_query.compile( dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True})) query_defintion_string = query_defintion_string.replace( 'ST_AsEWKB(CAST', 'CAST') query_defintion_string = query_defintion_string.replace( '))) AS geom', ')) AS geom') query_defintion_text = text(query_defintion_string) create_view = CreateView(view, query_defintion_text, or_replace=True) self._local_db_engine.execute(create_view)
def test_get_intersecting(self): """ 1.) determine potential source ts based on variable/unit/location(bbox) (omitted) 2.) Query all sampling features that belong to this set of series ids 3.) for each target feature, determine the points that intersect it """ # this will already be known on the client (querying from the db for testing purposes only) targets = [ 'POLYGON ((-111.961138093451495 41.896360920478401,-111.752777525493116 41.893660783528617,-111.756504446950089 41.606066677767103,-111.964865014908469 41.608766814716887,-111.961138093451495 41.896360920478401))', 'POLYGON ((-111.752777525493116 41.893660783528617,-111.499522658320416 41.892196863718198,-111.501743961064676 41.507916435626953,-111.754998828237376 41.509380355437372,-111.752777525493116 41.893660783528617))', 'POLYGON ((-111.340098770929785 41.625102365813746,-111.132370933565824 41.620863022194158,-111.135345259823112 41.475121035587051,-111.343073097187073 41.479360379206639,-111.340098770929785 41.625102365813746))' ] res = None # isolate only the samplingfeature ids that I am interested in (i.e. a set of points) sourceids = [ 'points_nad83_0', 'points_nad83_1', 'points_nad83_2', 'points_nad83_3', 'points_nad83_4' ] sources = {} for target in targets: try: #ST_Equals(geometry, geometry) #return self._session.query(Samplingfeature).filter(func.ST_AsText(Samplingfeature.FeatureGeometry) == func.ST_AsText(wkt_geometry)).first() res = self._session.query(Samplingfeature).filter( Samplingfeature.SamplingFeatureCode.in_(sourceids)).filter( func.ST_Intersects(Samplingfeature.FeatureGeometry, target)).all() sources[target] = res except Exception, e: print e return None
def _load_tables(self, extent): for table_name in self._osm_derived_tables: table_meta = self._table_metas[table_name] query = select([table_meta]) query = query.where( func.ST_Intersects(table_meta.c.way, extent.ewkt)) self._execute_and_insert_into_local_db(query, table_meta)
def get(self): """GET all jobs.""" args = get_parser.parse_args() validate_get(args) # collect all filters filters = [] bbox = args.get(JobFields.BBOX) if bbox: bbox = [float(x) for x in args[JobFields.BBOX].split(',')] bbox_wkt = bbox_to_wkt(bbox) filters.append(func.ST_Intersects(Job.bbox, bbox_wkt)) router = args.get(JobFields.ROUTER) if router: filters.append(Job.router == router) provider = args.get(JobFields.PROVIDER) if provider: filters.append(Job.provider == provider) interval = args.get(JobFields.INTERVAL) if interval: filters.append(Job.interval == interval) status = args.get(JobFields.STATUS) if status: filters.append(Job.status == status) return Job.query.filter(*filters).all()
def query(self, source, target, core, column, pkey): # ST_Buffer is not yet implemented so BigQueryCore won't work # (groups.google.com/d/msg/bq-gis-feedback/Yq4Ku6u2A80/ceVXU01RCgAJ) if isinstance(core, BigQueryCore): raise ValueError( "The LengthOf feature is currently incompatible with \ BigQueryCore because ST_Buffer is not yet implemented") # Get all lines-of-interests (LOIs) of fclass `on` lois = select( [source.c[self.source_id], source.c.WKT], source.c[self.source_column] == self.source_filter, ).cte("lois") # Create a buffer `within` a distance/radius around each centroid. # The point has to be converted to EPSG:3857 so that meters can be # used instead of decimal degrees for EPSG:4326. buff = select([ target, func.ST_Buffer(core.ST_GeoFromText(target.c[column]), self.within).label("__buffer__"), ]).cte("buff") # Clip the LOIs with the buffers then calculate the length of all # LOIs inside each buffer. clip = select( [ buff, func.ST_Intersection( core.ST_GeoFromText(lois.c.WKT), func.ST_Transform(buff.c["__buffer__"], 4326), ).label("__geom__"), func.ST_Length( func.ST_Intersection( func.ST_Transform(core.ST_GeoFromText(lois.c.WKT), 3857), buff.c["__buffer__"], )).label("__len__"), ], func.ST_Intersects( core.ST_GeoFromText(lois.c.WKT), func.ST_Transform(buff.c["__buffer__"], 4326), ), ).cte("clip") # Sum the length of all LOIs inside each buffer sum_length = (select([ clip.c[pkey], func.sum(clip.c["__len__"]).label(self.feature_name), ]).select_from(clip).group_by(clip.c[pkey]).cte("sum_length")) # Join the sum of the length of all LOIs inside each buffer query = select( [ col for col in sum_length.columns if col.key not in ("__len__", "__geom__", "__buffer__") ], sum_length.c[pkey] == buff.c[pkey], ) return query
def calc_overlap(geom_a, geom_b): """Calculate area of overlap between two geometry columns.""" with loader.database.session() as sess: q = sess.query( func.sum(func.ST_Intersection(geom_a, geom_b).ST_Area())).filter( func.ST_Intersects( geom_a, geom_b)) return q.scalar()
def _query_from_catalog(self, main_catalog): """Query for overlapping catalogs given a principal catalog. Returns a list of overlapping catalogs. """ q = self._s.query(Catalog)\ .filter(func.ST_Intersects( Catalog.footprint, self._main_footprint))\ .filter(Catalog.id != main_catalog.id) return q
def crop_osm(osm_table, bounding_box): if bounding_box is not None: return session.query(osm_table.wkb_geometry, osm_table.fclass) \ .filter(func.ST_Intersects(osm_table.wkb_geometry, bounding_box)) \ .filter(osm_table.fclass is not None).subquery() else: return session.query(osm_table.wkb_geometry, osm_table.fclass) \ .filter(osm_table.fclass is not None).subquery()
async def filter_pdv_by_lat_and_long(self, lat, lng): return self.session.query(PDV) \ .filter( func.ST_Intersects( func.Geometry(PDV.coverage_area), func.Geometry( func.ST_GeographyFromText(f'POINT({lat} {lng})') ) ) ) \ .all()
def _query_from_footprint(self): """Query for overlapping catalogs given a footprint, and possibly an exclusion of catalogs. """ q = self._s.query(Catalog)\ .filter(func.ST_Intersects( Catalog.footprint, self._main_footprint)) if self._excluded_catalogs is not None: catalog_ids = [c.id for c in self._excluded_catalogs] q = q.filter(not_(Catalog.id.in_(catalog_ids))) return q
def fetch_street_edges_intersecting(bounding_box: BoundingBox): """Fetch all the polygons intersecting the given rectangular bounding box Reference http://geoalchemy-2.readthedocs.io/en/0.3/orm_tutorial.html#spatial-query """ envelope_geom = StreetEdge._envelope(bounding_box, geom=True) query = session.query(StreetEdge).filter( func.ST_Intersects(func.ST_Transform(StreetEdge.geom, 4326), envelope_geom)) return query
def get_pq(): s = Serializer(get_app().config['SECRET_KEY']) user_id = s.loads(request.cookies['token']) user = userModels.AppUser.query\ .filter(userModels.AppUser.id_role==user_id['id_role'])\ .one() data = db.session.query(models.PqData)\ .join(models.Communes, func.ST_Intersects(models.PqData.geom, models.Communes.geom))\ .filter(models.Communes.code_insee == user.code_insee)\ .all() return jsonify(FeatureCollection([liste.as_geofeature() for liste in data]))
def generate_for_neighbour_polygons(): polygons = Polygon.query.all() for polygon in polygons: print(polygon.id) neighbours = db.session.query(Polygon).filter( (Polygon.id != polygon.id) & (func.ST_Intersects(Polygon.geo, polygon.geo))).all() neighbours_ids = [ x.id for x in neighbours if 'POINT' not in db.session.scalar( functions.ST_AsText( functions.ST_Intersection(polygon.geo, x.geo))) ] polygon_cross_locations = Location.query.filter( (Location.polygon_id == polygon.id) & (Location.is_cross_location == True)).all() neighbours_cross_locations = Location.query.filter( (Location.polygon_id.in_(neighbours_ids)) & (Location.is_cross_location == True)).all() if len(polygon_cross_locations) + len(neighbours_cross_locations) == 1: continue size = len(polygon_cross_locations) + len(neighbours_cross_locations) osrm_table = _get_osrm_table_response_s2d(polygon_cross_locations, neighbours_cross_locations) if not osrm_table: return False for i in range(len(polygon_cross_locations)): nearest_idx = _find_nearest(i, osrm_table) row = MatrixRow.query.filter( (MatrixRow.from_location_id == polygon_cross_locations[i].id) & (MatrixRow.to_location_id == neighbours_cross_locations[nearest_idx].id)).first() if not row: row = MatrixRow( from_location_id=polygon_cross_locations[i].id, to_location_id=neighbours_cross_locations[nearest_idx].id, distance=osrm_table['distances'][i][nearest_idx], duration=osrm_table['durations'][i][nearest_idx]) db.session.add(row) db.session.commit() return True
def fetch_polygons_intersecting(bounding_box: BoundingBox): """Fetch all the polygons intersecting the given rectangular bounding box Reference http://geoalchemy-2.readthedocs.io/en/0.3/orm_tutorial.html#spatial-query """ envelope = """POLYGON(( %f %f, %f %f, %f %f, %f %f, %f %f ))""" % ( bounding_box.west, bounding_box.south, bounding_box.west, bounding_box.north, bounding_box.east, bounding_box.north, bounding_box.east, bounding_box.south, bounding_box.west, bounding_box.south) envelope_geom = func.ST_GeomFromText(envelope, 4326) query = session.query(SidewalkPolygon).filter( func.ST_Intersects(func.ST_Transform(SidewalkPolygon.geom, 4326), envelope_geom)) return query
def roadbed_query(session, detection): """Find roadbeds that intersect the detection's footprint.""" car_lla = detection.lonlat # pylint: disable-msg=E1101 roadbeds4326 = func.ST_Transform(Roadbed.geom, 4326) car_roadbed_dist = func.ST_Distance(roadbeds4326, car_lla) query = session.query( car_roadbed_dist, Roadbed.gid) \ .filter(func.ST_Intersects(car_lla, roadbeds4326)) \ .order_by(car_roadbed_dist.asc()) # pylint: enable-msg=E1101 roadbed = query.first() return roadbed
def intersect_grids(geom_expr, geom_tables: List[Table]): """Generate Intersection Query Conditions with Grid Tables.""" sub_where = [] outer_join = [] for geom_table in geom_tables: if not hasattr(geom_table.c, "tile"): break get_srid_expr = func.ST_SRID(geom_table.c.geom) sub_where.append( and_( func.ST_Intersects(func.ST_Transform(geom_expr, get_srid_expr), geom_table.c.geom), Tile.name == geom_table.c.tile, )) outer_join.append((geom_table, [Tile.name == geom_table.c.tile])) return [or_(*sub_where)], outer_join
def chunk_n(self, n): n = max(1, n) (south, north, west, east) = self.bbox ns = (north - south) / n ew = (east - west) / n chunks = [] for row in range(n): for col in range(n): chunk = (south + ns * row, south + ns * (row + 1), west + ew * col, west + ew * (col + 1)) want_chunk = func.ST_Intersects(Place.geom, envelope(chunk)) want = (session.query(want_chunk).filter( Place.place_id == self.place_id).scalar()) if want: chunks.append(chunk) return chunks
def get_local_land_charges(): """Get a list of land charges Returns all if no parameter is required, otherwise it will return those contained in bounding box. """ current_app.logger.info("Get local land charges by geometry search") geo_json_extent = request.args.get('boundingBox') if geo_json_extent: try: json_extent = json.loads( base64.b64decode(geo_json_extent).decode()) extent_shape = asShape(json_extent) features = GeometryFeature.query.filter( func.ST_Intersects( GeometryFeature.geometry, shape.from_shape(extent_shape, srid=27700))).options( db.joinedload( GeometryFeature.local_land_charge)).all() res_set = set() for feature in features: res_set.add(feature.local_land_charge) llc_result = list(res_set) except (ValueError, TypeError) as err: raise ApplicationError("Unprocessable Entity. {}".format(err), 422, 422) else: current_app.logger.warning( "No bounding box supplied - returning all local land charges") llc_result = LocalLandCharge.query.all() if not llc_result or len(llc_result) == 0: raise ApplicationError("No land charges found", 404, 404) current_app.logger.info("Returning local land charges") return json.dumps(model_mappers.map_llc_result_to_dictionary_list(llc_result)), \ 200, {'Content-Type': 'application/json'}
def coverage_query(session, detection): """Computes the percentage of the vehicles on the roadbeds.""" # pylint: disable-msg=E1101 car_polygon = Detection.geom car_polygon102718 = func.ST_Transform(car_polygon, 102718) car_road_intersection = func.ST_Area( func.ST_Intersection(Roadbed.geom, car_polygon102718)) car_area = func.ST_Area(car_polygon102718) car_filter = func.ST_Intersects( Roadbed.geom, car_polygon102718) query = session.query( func.sum(car_road_intersection / car_area)) \ .filter(Detection.id == detection.id) \ .filter(car_filter) # pylint: enable-msg=E1101 coverage, = query.one() if coverage is None: coverage = 0 return coverage
def add_locations(data): for l in data: location = Location.query.filter_by(name=l['name']).first() if not location: point_geo_str = 'POINT({:s})'.format('{:.6f}'.format(l['lon']) + ' ' + '{:.6f}'.format(l['lat'])) polygon = db.session.query(Polygon).filter( func.ST_Intersects(point_geo_str, Polygon.geo)).first() if not polygon: response = { 'status': 'fail', 'message': 'Location ' + l['name'] + ' does not belong to any polygon' } return response, 400 new_location = Location(name=l['name'], latitude=l['lat'], longitude=l['lon'], geo=point_geo_str, polygon_id=polygon.id, is_cross_location=l['is_cross_location']) save(new_location) else: response = { 'status': 'fail', 'message': 'Location ' + l['name'] + ' name already exists' } return response, 400 response = {'status': 'success', 'message': 'Locations successfully added'} return response, 201
def search_nearest_partner(): try: longitude = float(request.args.get('lng')) latitude = float(request.args.get('lat')) except ValueError: message = { 'message': f'Bad request: lng and lat are ' 'required and should be float type' } return make_response(message, 400, HEADERS) else: partner = (Partner.query.filter( func.ST_Intersects( Partner.coverage_area, f'POINT({longitude} {latitude})')).order_by( func.ST_Distance( Partner.address, f'POINT({longitude} {latitude})')).first()) if partner: return make_response(schema.dump(partner), 200, HEADERS) return make_response('', 204, HEADERS)
def compute_features_from_osm(config): osm_tables = config['OSM'] bounding_box = WKTElement(config['BOUNDING_BOX'], srid=4326) grid_obj = config['GRID_OBJ'] geo_feature_obj = config['GEO_FEATURE_OBJ'] try: for feature_name, osm_table in osm_tables.items(): geo_feature_type = osm_table.wkb_geometry.type.geometry_type cropped_osm = crop_osm( osm_table, bounding_box) # crop the OSM data with a bounding box sub_query = session.query(grid_obj.gid, cropped_osm.c.fclass, func.ST_GeogFromWKB( func.ST_Intersection(grid_obj.geom, cropped_osm.c.wkb_geometry)) .label('intersection')) \ .filter(func.ST_Intersects(grid_obj.geom, cropped_osm.c.wkb_geometry)).subquery() results = [] if geo_feature_type == 'MULTIPOLYGON': results = session.query(sub_query.c.gid.label('gid'), sub_query.c.fclass.label('feature_type'), literal(feature_name).label('geo_feature'), func.SUM(func.ST_AREA(sub_query.c.intersection)).label('value'), literal('area').label('measurement')) \ .group_by(sub_query.c.gid, sub_query.c.fclass).all() elif geo_feature_type == 'MULTILINESTRING': results = session.query(sub_query.c.gid.label('gid'), sub_query.c.fclass.label('feature_type'), literal(feature_name).label('geo_feature'), func.SUM(func.ST_LENGTH(sub_query.c.intersection)).label('value'), literal('length').label('measurement')) \ .group_by(sub_query.c.gid, sub_query.c.fclass).all() elif geo_feature_type == 'POINT': results = session.query(sub_query.c.gid.label('gid'), sub_query.c.fclass.label('feature_type'), literal(feature_name).label('geo_feature'), func.COUNT(sub_query.c.intersection).label('value'), literal('count').label('measurement')) \ .group_by(sub_query.c.gid, sub_query.c.fclass).all() else: pass obj_results = [] for res in results: obj_results.append( geo_feature_obj(gid=res[0], feature_type=res[1], geo_feature=res[2], value=res[3], measurement=res[4])) # session.add_all(obj_results) # session.commit() print('{} has finished'.format(feature_name)) return except Exception as e: print(e) exit(-1)
def get_collection_items( collection_id=None, roles=[], item_id=None, bbox=None, datetime=None, ids=None, collections=None, intersects=None, page=1, limit=10, query=None, **kwargs, ): """Retrieve a list of collection items based on filters. :param collection_id: Single Collection ID to include in the search for items. Only Items in one of the provided Collection will be searched, defaults to None :type collection_id: str, optional :param item_id: item identifier, defaults to None :type item_id: str, optional :param bbox: bounding box for intersection [west, north, east, south], defaults to None :type bbox: list, optional :param datetime: Single date+time, or a range ('/' seperator), formatted to RFC 3339, section 5.6. Use double dots '..' for open date ranges, defaults to None. If the start or end date of an image generated by a temporal composition intersects the given datetime or range it will be included in the result. :type datetime: str, optional :param ids: Array of Item ids to return. All other filter parameters that further restrict the number of search results are ignored, defaults to None :type ids: list, optional :param collections: Array of Collection IDs to include in the search for items. Only Items in one of the provided Collections will be searched, defaults to None :type collections: list, optional :param intersects: Searches items by performing intersection between their geometry and provided GeoJSON geometry. All GeoJSON geometry types must be supported., defaults to None :type intersects: dict, optional :param page: The page offset of results, defaults to 1 :type page: int, optional :param limit: The maximum number of results to return (page size), defaults to 10 :type limit: int, optional :return: list of collectio items :rtype: list """ columns = [ func.concat(Collection.name, "-", Collection.version).label("collection"), Collection.collection_type, Collection._metadata.label("meta"), Item._metadata.label("item_meta"), Item.name.label("item"), Item.id, Item.collection_id, Item.start_date.label("start"), Item.end_date.label("end"), Item.assets, Item.created, Item.updated, cast(Item.cloud_cover, Float).label("cloud_cover"), func.ST_AsGeoJSON(Item.geom).label("geom"), func.Box2D(Item.geom).label("bbox"), Tile.name.label("tile"), ] where = [ Collection.id == Item.collection_id, or_(Collection.is_public.is_(True), Collection.id.in_([int(r.split(":")[0]) for r in roles])), ] if ids is not None: where += [Item.name.in_(ids.split(","))] else: if collections is not None: where += [ func.concat(Collection.name, "-", Collection.version).in_(collections.split(",")) ] elif collection_id is not None: where += [ func.concat(Collection.name, "-", Collection.version) == collection_id ] if item_id is not None: where += [Item.name.like(item_id)] if query: filters = create_query_filter(query) if filters: where += filters if intersects is not None: where += [ func.ST_Intersects(func.ST_GeomFromGeoJSON(str(intersects)), Item.geom) ] elif bbox is not None: try: split_bbox = [float(x) for x in bbox.split(",")] if split_bbox[0] == split_bbox[2] or split_bbox[ 1] == split_bbox[3]: raise InvalidBoundingBoxError("") where += [ func.ST_Intersects( func.ST_MakeEnvelope( split_bbox[0], split_bbox[1], split_bbox[2], split_bbox[3], func.ST_SRID(Item.geom), ), Item.geom, ) ] except: raise ( InvalidBoundingBoxError(f"'{bbox}' is not a valid bbox.")) if datetime is not None: date_filter = None if "/" in datetime: matches_open = ("..", "") time_start, time_end = datetime.split("/") if time_start in matches_open: # open start date_filter = [ or_(Item.start_date <= time_end, Item.end_date <= time_end) ] elif time_end in matches_open: # open end date_filter = [ or_(Item.start_date >= time_start, Item.end_date >= time_start) ] else: # closed range date_filter = [ or_( and_(Item.start_date >= time_start, Item.start_date <= time_end), and_(Item.end_date >= time_start, Item.end_date <= time_end), and_(Item.start_date < time_start, Item.end_date > time_end), ) ] else: date_filter = [ and_(Item.start_date <= datetime, Item.end_date >= datetime) ] where += date_filter outer = [Item.tile_id == Tile.id] query = session.query(*columns).outerjoin( Tile, *outer).filter(*where).order_by(Item.start_date.desc(), Item.id) result = query.paginate(page=int(page), per_page=int(limit), error_out=False, max_per_page=BDC_STAC_MAX_LIMIT) return result
def create_outputs(self, hazardset): adminlevel_reg = AdminLevelType.get(self.dbsession, "REG") self.bbox = None for reader in self.readers.values(): polygon = polygon_from_boundingbox(reader.bounds) if self.bbox is None: self.bbox = polygon else: self.bbox = self.bbox.intersection(polygon) regions_ids = [r.id for r in hazardset.regions] # get the divisions which parents (country) are in the regions set in # the hazardset regions_filter = AdministrativeDivision.parent.has( AdministrativeDivision.parent.has( AdministrativeDivision.regions.any(Region.id.in_(regions_ids)) ) ) admindivs = ( self.dbsession.query(AdministrativeDivision) .filter(AdministrativeDivision.leveltype_id == adminlevel_reg.id) .filter( func.ST_Intersects( AdministrativeDivision.geom, func.ST_GeomFromText(self.bbox.wkt, 4326), ) ) .filter(regions_filter) .order_by(AdministrativeDivision.id) ) # Needed by windowed querying current = 0 last_percent = 0 outputs = [] total = admindivs.count() logger.info(" Iterating over {} administrative divisions".format(total)) # Windowed querying to limit memory usage limit = 1000 # 1000 records <=> 10 Mo admindivs = admindivs.limit(limit) for offset in range(0, total, limit): admindivs = admindivs.offset(offset) for admindiv in admindivs: current += 1 if admindiv.geom is None: logger.warning( " {}-{} has null geometry".format( admindiv.code, admindiv.name ) ) continue shape = to_shape(admindiv.geom) # Try block to include admindiv.code in exception message try: if "values" in list(self.type_settings.keys()): # preprocessed layer hazardlevel = self.preprocessed_hazardlevel(shape) else: hazardlevel = self.notpreprocessed_hazardlevel( hazardset.hazardtype.mnemonic, shape ) except: error = "Processing of div. {} failed".format(admindiv.code) logger.error(error, exc_info=True) return [], error # Create output record if hazardlevel is not None: output = Output() output.hazardset = hazardset output.admin_id = admindiv.id output.hazardlevel = hazardlevel outputs.append(output) # Remove admindiv from memory self.dbsession.expunge(admindiv) percent = int(100.0 * current / total) if percent % 10 == 0 and percent != last_percent: logger.info(" ... processed {}%".format(percent)) last_percent = percent return outputs, None
def create_outputs(hazardset, layers, readers): type_settings = settings['hazard_types'][hazardset.hazardtype.mnemonic] adminlevel_reg = AdminLevelType.get(u'REG') bbox = None for reader in readers.itervalues(): polygon = polygon_from_boundingbox(reader.bounds) if bbox is None: bbox = polygon else: bbox = bbox.intersection(polygon) admindivs = DBSession.query(AdministrativeDivision) \ .filter(AdministrativeDivision.leveltype_id == adminlevel_reg.id) \ .filter(func.ST_Intersects(AdministrativeDivision.geom, func.ST_GeomFromText(bbox.wkt, 4326))) \ .order_by(AdministrativeDivision.id) # Needed for windowed querying current = 0 last_percent = 0 outputs = [] total = admindivs.count() logger.info(' Iterating over {} administrative divisions'.format(total)) # Windowed querying to limit memory usage limit = 1000 # 1000 records <=> 10 Mo admindivs = admindivs.limit(limit) for offset in xrange(0, total, limit): admindivs = admindivs.offset(offset) for admindiv in admindivs: current += 1 if admindiv.geom is None: logger.warning(' {}-{} has null geometry'.format( admindiv.code, admindiv.name)) continue shape = to_shape(admindiv.geom) # Try block to include admindiv.code in exception message try: if 'values' in type_settings.keys(): # preprocessed layer hazardlevel = preprocessed_hazardlevel( type_settings, layers[0], readers[0], shape) else: hazardlevel = notpreprocessed_hazardlevel( hazardset.hazardtype.mnemonic, type_settings, layers, readers, shape) except Exception as e: e.message = ("{}-{} raises an exception :\n{}".format( admindiv.code, admindiv.name, e.message)) raise # Create output record if hazardlevel is not None: output = Output() output.hazardset = hazardset output.admin_id = admindiv.id output.hazardlevel = hazardlevel # TODO: calculate coverage ratio output.coverage_ratio = 100 outputs.append(output) # Remove admindiv from memory DBSession.expunge(admindiv) percent = int(100.0 * current / total) if percent % 10 == 0 and percent != last_percent: logger.info(' ... processed {}%'.format(percent)) last_percent = percent return outputs
def geom_overlapping(table, key_name, output_table_name): """ Export overlapping geometries from a table into another table. The exported table contains the following columns: key_name_a, key_name_b: identifiers of the overlapping pair relation: DE-9IM representation of their spatial relation geom_a, geom_b: corresponding geometries overlap: 2D overlapping region (polygons) Parameters ---------- table : sqlalchemy.ext.declarative.DeclarativeMeta Table ORM class to query for overlapping geometries. key_name : str Name of column in the queried table containing a unique identifier, such as a primary key, to use for cross join and to identify geometries in the exported table. output_table_name : str Name of exported table. Table is created in the same schema as the queried table. Returns ------- None """ # Create table aliases to cross join table to self. table_a = aliased(table) table_b = aliased(table) table_a_key = getattr(table_a, key_name).label(key_name + '_a') table_b_key = getattr(table_b, key_name).label(key_name + '_b') # Query for overlaps. with db.session() as sess: q = sess.query( table_a_key, table_b_key, func.ST_Relate(table_a.geom, table_b.geom).label('relation'), table_a.geom.label('geom_a'), table_b.geom.label('geom_b'), # Extract only polygon geometries from intersection. func.ST_CollectionExtract( func.ST_Intersection(table_a.geom, table_b.geom), 3 ).label('overlap') ).filter( # Use "<" instead of "!=" to prevent duplicates and save time. table_a_key < table_b_key, func.ST_Intersects(table_a.geom, table_b.geom), # Polygon interiors must not intersect. ~func.ST_Relate(table_a.geom, table_b.geom, 'FF*F*****') # Alternatively, can use ST_Overlaps, ST_Contains, and ST_Within # to check for overlap instead of ST_Relate, but this was # slightly slower in my testing. # or_( # table_a.geom.ST_Overlaps(table_b.geom), # table_a.geom.ST_Contains(table_b.geom), # table_a.geom.ST_Within(table_b.geom) # ) ) # Create new table from query. This table does not contain constraints, # such as primary keys. schema = getattr(db.tables, table.__table__.schema) io.db_to_db(q, output_table_name, schema)
def check_scenes(cls, collections: str, start_date: datetime, end_date: datetime, catalog: str = None, dataset: str = None, grid: str = None, tiles: list = None, bbox: list = None, catalog_kwargs=None, only_tiles=False): """Check for the scenes in remote provider and compares with the Collection Builder.""" bbox_list = [] if grid and tiles: grid = GridRefSys.query().filter(GridRefSys.name == grid).first_or_404(f'Grid "{grid}" not found.') geom_table = grid.geom_table rows = db.session.query( geom_table.c.tile, func.ST_Xmin(func.ST_Transform(geom_table.c.geom, 4326)).label('xmin'), func.ST_Ymin(func.ST_Transform(geom_table.c.geom, 4326)).label('ymin'), func.ST_Xmax(func.ST_Transform(geom_table.c.geom, 4326)).label('xmax'), func.ST_Ymax(func.ST_Transform(geom_table.c.geom, 4326)).label('ymax'), ).filter(geom_table.c.tile.in_(tiles)).all() for row in rows: bbox_list.append((row.tile, (row.xmin, row.ymin, row.xmax, row.ymax))) else: bbox_list.append(('', bbox)) instance, provider = get_provider(catalog) collection_map = dict() collection_ids = list() for _collection in collections: collection, version = _collection.split('-') collection = Collection.query().filter( Collection.name == collection, Collection.version == version ).first_or_404(f'Collection "{collection}-{version}" not found.') collection_ids.append(collection.id) collection_map[_collection] = collection options = dict(start_date=start_date, end_date=end_date) if catalog_kwargs: options.update(catalog_kwargs) redis = current_app.redis output = dict( collections={cname: dict(total_scenes=0, total_missing=0, missing_external=[]) for cname in collections} ) items = {cid: set() for cid in collection_ids} external_scenes = set() for tile, _bbox in bbox_list: with redis.pipeline() as pipe: if only_tiles: entry = tile options['tile'] = tile else: options['bbox'] = _bbox entry = _bbox periods = _generate_periods(start_date.replace(tzinfo=None), end_date.replace(tzinfo=None)) for period_start, period_end in periods: _items = db.session.query(Item.name, Item.collection_id).filter( Item.collection_id.in_(collection_ids), func.ST_Intersects( func.ST_MakeEnvelope( *_bbox, func.ST_SRID(Item.geom) ), Item.geom ), or_( and_(Item.start_date >= period_start, Item.start_date <= period_end), and_(Item.end_date >= period_start, Item.end_date <= period_end), and_(Item.start_date < period_start, Item.end_date > period_end), ) ).order_by(Item.name).all() for item in _items: items[item.collection_id].add(item.name) options['start_date'] = period_start.strftime('%Y-%m-%d') options['end_date'] = period_end.strftime('%Y-%m-%d') key = f'scenes:{catalog}:{dataset}:{period_start.strftime("%Y%m%d")}_{period_end.strftime("%Y%m%d")}_{entry}' pipe.get(key) provider_scenes = [] if not redis.exists(key): provider_scenes = provider.search(dataset, **options) provider_scenes = [s.scene_id for s in provider_scenes] pipe.set(key, json.dumps(provider_scenes)) external_scenes = external_scenes.union(set(provider_scenes)) cached_scenes = pipe.execute() for cache in cached_scenes: # When cache is True, represents set the value were cached. if cache is not None and cache is not True: external_scenes = external_scenes.union(set(json.loads(cache))) output['total_external'] = len(external_scenes) for _collection_name, _collection in collection_map.items(): _items = set(items[_collection.id]) diff = list(external_scenes.difference(_items)) output['collections'][_collection_name]['total_scenes'] = len(_items) output['collections'][_collection_name]['total_missing'] = len(diff) output['collections'][_collection_name]['missing_external'] = diff for cname, _internal_collection in collection_map.items(): if cname != _collection_name: diff = list(_items.difference(set(items[_internal_collection.id]))) output['collections'][_collection_name][f'total_missing_{cname}'] = len(diff) output['collections'][_collection_name][f'missing_{cname}'] = diff return output
with session_scope() as sess: q = sess.query(Slick_Ext) # Order by time: q = q.unique_join(Posi_Poly_Ext).unique_join(Inference_Ext).unique_join( Grd_Ext) q = q.order_by(Grd_Ext.starttime) if MockRequest.get("startdate"): q = q.filter(Grd_Ext.starttime >= MockRequest.get("startdate")) if MockRequest.get("enddate"): q = q.filter(Grd_Ext.starttime <= MockRequest.get("enddate")) if MockRequest.get("eez_sov"): q = q.unique_join(Posi_Poly_Ext).unique_join( Eez_Ext, func.ST_Intersects(Posi_Poly_Ext.geometry, Eez_Ext.geometry)) q = q.filter( or_((func.array_to_string(Eez_Ext.sovereigns, "||").ilike(f"%{sov}%") for sov in MockRequest.get("eez_sov")))) if MockRequest.get("min_score"): q = q.unique_join(Posi_Poly_Ext).unique_join(Coincident_Ext) q = q.filter(Coincident_Ext.score >= MockRequest.get("min_score")) if MockRequest.get("vessel_mmsi"): q = (q.unique_join(Posi_Poly_Ext).unique_join( Coincident_Ext).unique_join(Vessel_Ext)) q = q.filter( or_((Vessel_Ext.mmsi == m for m in MockRequest.get("vessel_mmsi")))) if MockRequest.get("vessel_flag"): q = (q.unique_join(Posi_Poly_Ext).unique_join(
def centerline_query(session, detection): """Finds the centerline orientation that most closely agrees with detection-intersected roadbeds.""" # pylint: disable-msg=E1101 car_polygon = Detection.geom car_polygon102718 = func.ST_Transform(car_polygon, 102718) car_filter = func.ST_Intersects( Roadbed.geom, car_polygon102718 ) query = session.query( Roadbed.gid) \ .filter(Detection.id == detection.id) \ .filter(car_filter) road_gids = query.all() if len(road_gids) == 0: return lat, lon, alt = session.query( func.ST_Y(Detection.lla), func.ST_X(Detection.lla), func.ST_Z(Detection.lla)) \ .filter(Detection.id == detection.id) \ .one() lla = numpy.array([[lat, lon, alt]]) enu = pygeo.LLAToENU(lla).reshape((3, 3)) roadbeds4326 = func.ST_Transform(Roadbed.geom, 4326) centerlines4326 = PlanetOsmLine.way centerline_filter = func.ST_Intersects(roadbeds4326, centerlines4326) centerline_frac = func.ST_Line_Locate_Point( centerlines4326, Detection.lla) centerline_start_frac = func.least(1, centerline_frac + 0.01) centerline_end_frac = func.greatest(0, centerline_frac - 0.01) centerline_start = func.ST_Line_Interpolate_Point(centerlines4326, centerline_start_frac) centerline_end = func.ST_Line_Interpolate_Point(centerlines4326, centerline_end_frac) segments = session.query( func.ST_Y(centerline_start).label('lats'), func.ST_X(centerline_start).label('lons'), func.ST_Y(centerline_end).label('late'), func.ST_X(centerline_end).label('lone'), PlanetOsmLine.oneway) \ .filter(Detection.id == detection.id) \ .filter(centerline_filter) \ .filter(Roadbed.gid.in_(road_gids)) \ .filter(PlanetOsmLine.osm_id >= 0) \ .filter(PlanetOsmLine.railway.__eq__(None)) # pylint: enable-msg=E1101 for segment in segments: segment_start = pygeo.LLAToECEF(numpy.array( [[segment.lats, segment.lons, alt]], dtype=numpy.float64 )) segment_end = pygeo.LLAToECEF(numpy.array( [[segment.late, segment.lone, alt]], dtype=numpy.float64 )) segment_dir = (segment_end - segment_start) segment_dir /= numpy.linalg.norm(segment_dir) segment_rot = enu.T.dot(segment_dir.T) segment_angle = math.atan2(segment_rot[1], segment_rot[0]) yield segment_angle, segment.oneway
def argoQuery(dbcon, geoWKT=None, tspan=None, withinDmeter=None, tsort=None): tbl = dbcon.getTable('argo2', 'oceanobs') #first create a subquery to quickly discard argo profiles subqry = select([tbl]) if tspan: subqry = subqry.where( func.overlaps(tbl.c.tstart, tbl.c.tend, tspan[0], tspan[1])) # Apply initial geospatial constraints if geoWKT: if withinDmeter: #only base initial constraints ont he bounding box subqry = subqry.where( func.ST_DWithin( literal_column('ST_Envelope(geom::geometry)::geography'), func.ST_GeogFromText(geoWKT), withinDmeter)) else: subqry = subqry.where( func.ST_Intersects(literal_column('geom::geometry'), func.ST_GeomFromText(geoWKT, 4326))) #we need to assign an alias to this subquery in order to work with it subqry = subqry.alias("ar") #expand the arrays and points int he subquery qry = select([ subqry.c.wmoid, subqry.c.uri, subqry.c.datacenter, func.unnest(subqry.c.mode).label('mode'), func.unnest(subqry.c.ascend).label('ascend'), func.unnest(subqry.c.tlocation).label('tlocation'), func.unnest(subqry.c.cycle).label('cycle'), func.unnest(subqry.c.iprof).label('iprof'), ST_Dump(literal_column("ar.geom::geometry")).geom.label('geom') ]) #additional spatial constraints finalqry = qry qry = qry.alias("arex") if tspan: finalqry = select([qry]).where( between(qry.c.tlocation, tspan[0], tspan[1])) if geoWKT: if withinDmeter: #only base initial constraints ont he bounding box finalqry = finalqry.where( func.ST_DWithin(qry.c.geom, func.ST_GeogFromText(geoWKT), withinDmeter)) else: finalqry = finalqry.where( func.ST_Within(literal_column("arex.geom"), func.ST_GeomFromText(geoWKT, 4326))) if tsort: finalqry = finalqry.order_by(qry.c.tlocation) return dbcon.dbeng.execute(finalqry)