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)
