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 clips(self):
"""Polygons representing the intersecting areas between the principal
catalog and all overlapping catalogs.
Returns a list of tuples of :class:``WKBElement`` for each
intersection, corresponding to the list of overlapping catalogs.
That is, each overlap has a tuple of :class:``WKBElement`` since
an overlap can have multiple polygon parts.
The clipping polygons are cached.
To get the WKT representation of these::
>> for clip in overlaps.clips:
>> for part in clip:
>> print session.query(func.ST_AsText(part)).one()
"""
if self._overlapping_catalogs is None:
self._query_overlaps()
if self._clips is None:
self._clips = []
for catalog in self._overlapping_catalogs:
clip = self._s.\
query(func.ST_Intersection(
catalog.footprint, self._main_footprint)).\
one()
self._clips.append(clip)
return self._clips
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 geojson_chunks(self):
chunks = []
for chunk in self.polygon_chunk(size=place_chunk_size):
clip = func.ST_Intersection(Place.geom, envelope(chunk))
geojson = (session.query(func.ST_AsGeoJSON(
clip, 4)).filter(Place.place_id == self.place_id).scalar())
chunks.append(geojson)
return chunks
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 polygons(place_identifier):
place = get_place(place_identifier)
chunk_size = utils.calc_chunk_size(place.area_in_sq_km)
place_geojson = (database.session.query(func.ST_AsGeoJSON(
Place.geom, 4)).filter(Place.place_id == place.place_id).scalar())
# print(place_geojson)
for chunk in place.chunk_n(chunk_size):
print(', '.join('{:.3f}'.format(i) for i in chunk))
(ymin, ymax, xmin, xmax) = chunk
clip = func.ST_Intersection(
Place.geom, func.ST_MakeEnvelope(xmin, ymin, xmax, ymax))
chunk_geojson = (database.session.query(func.ST_AsGeoJSON(
clip, 4)).filter(Place.place_id == place.place_id).scalar())
print(chunk_geojson)
def getIntersectingPoints(wktStr):
"""
Takes an EWKT string of a Strava Activity Stream's latlngs and returns a list of float points which reside within
the privacy areas.
@param wktStr: String. EWKT representation of Strava Activity Stream latlngs
@return: List of strings. Points are returned as WGS 1984 coordinate strings in the format lon,lat
"""
# geometricProj = 32610
collectionExtract = 3
# Open session
session = Session()
# Get coordinates from within privacy zones
try:
# Create a labled common table expression to query privacy zones geometries collected into a single multi-polygon
privacy_cte = session.query(
sqlfunc.ST_CollectionExtract(sqlfunc.ST_Collect(
AOI.geom), collectionExtract).label("ctelab")).filter(
AOI.privacy == "Yes").cte()
# points_cte = session.query(sqlfunc.ST_DumpPoints(sqlfunc.st_geomfromewkt(wktStr)))
# Take provided EWKT string and convert to GeoAlchemy geometry
# lineString = sqlfunc.ST_GeomFromEWKT(wktStr)
# application.logger.debug(f"Geoalchemy Geom is: \n{dir(lineString)}")
# Get a list of points from the linestring which fall inside the privacy zone
# ST_DumpPoints provides a point geometry per iterative loop which is converted to a text representation using As_Text
pointQuery = session.query(
sqlfunc.ST_AsText(
sqlfunc.ST_DumpPoints(
sqlfunc.ST_Intersection(sqlfunc.ST_GeomFromEWKT(wktStr),
privacy_cte.c.ctelab)).geom))
# pointQuery = session.query(sqlfunc.ST_AsText(
# sqlfunc.ST_DumpPoints(sqlfunc.ST_Intersection(sqlfunc.ST_GeomFromEWKT(wktStr),
# privacy_cte.c.ctelab)).geom)).filter(privacy_cte.c.ctelab.
# ST_Intersects(sqlfunc.ST_GeomFromEWKT(wktStr)))
coordinateList = []
for i in pointQuery:
# application.logger.debug(f"Point query response is: {i}")
# strip out the WKT parts of the coordinates, only want list of [lon,lat]
coordinateList.append(formatPointResponse(i))
finally:
session.close()
return coordinateList
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 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 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 find_intersected_area(self, search_area: str) -> Iterable[object]:
return self.session.query(
func.ST_Union(func.ST_Intersection(func.ST_GeomFromText(search_area, 4326), Polygon.geom))
.label('intersected')) \
.filter(Polygon.geom.ST_Intersects(func.ST_GeomFromText(search_area, 4326)))