def make_grid(self, resolution, geom=None, conditions=None):
"""
:param resolution: length of side of grid square in meters
:type resolution: int
:param geom: string representation of geojson fragment
:type geom: str
:param conditions: conditions on columns to filter on
:type conditions: list of SQLAlchemy binary operations
(e.g. col > value)
:return: grid: result proxy with all result rows
size_x, size_y: the horizontal and vertical size
of the grid squares in degrees
"""
if conditions is None:
conditions = []
# We need to convert resolution (given in meters) to degrees
# - which is the unit of measure for EPSG 4326 -
# - in order to generate our grid.
center = self.get_bbox_center()
# center[1] is longitude
size_x, size_y = get_size_in_degrees(resolution, center[1])
# Generate a count for each resolution by resolution square
t = self.point_table
q = session.query(func.count(t.c.hash),
func.ST_SnapToGrid(t.c.geom, size_x, size_y)
.label('squares'))\
.filter(*conditions)\
.group_by('squares')
if geom:
q = q.filter(t.c.geom.ST_Within(func.ST_GeomFromGeoJSON(geom)))
return session.execute(q), size_x, size_y
def grid():
raw_query_params = request.args.copy()
buff = request.args.get('buffer', 100)
resolution = request.args.get('resolution')
if not resolution:
resolution = 500
else:
del raw_query_params['resolution']
center = request.args.getlist('center[]')
if not center:
center = [41.880517, -87.644061]
else:
del raw_query_params['center[]']
location_geom = request.args.get('location_geom__within')
if raw_query_params.get('buffer'):
del raw_query_params['buffer']
agg, datatype, queries = parse_join_query(raw_query_params)
size_x, size_y = getSizeInDegrees(float(resolution), float(center[0]))
if location_geom:
location_geom = json.loads(location_geom)['geometry']
if location_geom['type'] == 'LineString':
shape = asShape(location_geom)
lat = shape.centroid.y
# 100 meters by default
x, y = getSizeInDegrees(int(buff), lat)
size_x, size_y = getSizeInDegrees(50, lat)
location_geom = shape.buffer(y).__geo_interface__
location_geom['crs'] = {
"type": "name",
"properties": {
"name": "EPSG:4326"
}
}
mt = MasterTable.__table__
valid_query, base_clauses, resp, status_code = make_query(
mt, queries['base'])
if valid_query:
base_query = session.query(
func.count(mt.c.dataset_row_id),
func.ST_SnapToGrid(mt.c.location_geom, size_x, size_y))
dname = raw_query_params['dataset_name']
dataset = Table('dat_%s' % dname,
Base.metadata,
autoload=True,
autoload_with=engine,
extend_existing=True)
valid_query, detail_clauses, resp, status_code = make_query(
dataset, queries['detail'])
if valid_query:
pk = [p.name for p in dataset.primary_key][0]
base_query = base_query.join(dataset,
mt.c.dataset_row_id == dataset.c[pk])
for clause in base_clauses:
base_query = base_query.filter(clause)
for clause in detail_clauses:
base_query = base_query.filter(clause)
base_query = base_query.group_by(
func.ST_SnapToGrid(mt.c.location_geom, size_x, size_y))
values = [d for d in base_query.all()]
resp = {'type': 'FeatureCollection', 'features': []}
for value in values:
d = {
'type': 'Feature',
'properties': {
'count': value[0],
},
}
if value[1]:
pt = loads(value[1].decode('hex'))
south, west = (pt.x - (size_x / 2)), (pt.y - (size_y / 2))
north, east = (pt.x + (size_x / 2)), (pt.y + (size_y / 2))
d['geometry'] = box(south, west, north,
east).__geo_interface__
resp['features'].append(d)
resp = make_response(json.dumps(resp, default=dthandler), status_code)
resp.headers['Content-Type'] = 'application/json'
return resp
def processActivitiesPublic(recordID):
"""
Processes Strava activity by simplifying geometry and removing private areas. This prepares the activity to be
shared publicly on a Leaflet map. These functions greatly reduce the number of vertices, reducing JSON file size,
and process the data to be topoJSON friendly, preventing geometries from failing to be converted.
SQLAlchemy and GeoAlchemy2 ORM queries are used to do the following:
1. Create a common table expression(CTE) to select privacy zones geometry. This expression selects AOI polygons
flagged as privacy zones, combines them into a single multi-part polygon contained inside a geometry.
collection(ST_Collect), extracts the multi-polygon from the collection(ST_CollectionExtract), and transforms
(ST_transform) the geometry to the projected coordinate system geometricProj. This CTE is used to create a
single multi-part polygon containing all privacy zones. This ensures that ST_Difference only calculates the
difference between each activity and the privacy zones only once. If the privacy zones are not combined, then
the difference between each privacy zone record and the activity would be calculated, resulting in duplicated
results.
Using a projected coordinate allows for faster geometric calculations and allows for meters to be used in
PostGIS function parameters which use the geometry's units.
2. Select strava_activities activity linestring geometry based on Record ID and transform(ST_Transform) to
geometricProj.
3. Snap activity linestrings to a 0.0001m grid (ST_SnapToGrid, variant 3). This solves a non-node intersection error
when running ST_Difference. See this thread: https://gis.stackexchange.com/q/50399 for explanation for this
problem and solution.
4. Calculate difference(ST_Difference) between activity linestring and privacy zone CTE result. ST_Difference
subtracts geometry B from A, removing the vertices from A that are within B and segments that touch B.
5. Snap activity linestring vertices to a 5m grid(ST_SnapToGrid, variant 3). This removes some messy areas by
combining and removing excess vertices while also reducing resulting geometry memory/file size. This also solves
geometric errors when exporting data to a topoJSON format. However, resulting linestring geometries have a
step-shaped appearance resembling the grid.
6. Simplify activity linestring with a 15m tolerance(ST_Simplify). This further removes messy areas and bends in
the linestring by removing vertices to create longer straight line segments. This provides large reductions in
resulting geometry memory/file sizes and mitigates the step-shaped results created by ST_SnapToGrid.
7. Convert linestrings to multi-linestrings(ST_Multi). Geometries in the strava_activities table are stored as
linestrings since activity data provided by Strava are contiguous and don't need to be stored in a multi-part
format. However, ST_Difference may create multi-linestrings that must be stored as such, so all geometries
are converted to this format.
8. Fix any invalid activity linestring geometries(ST_MakeValid) that were generated during prior processing.
9. Transform activity linestring geometry(ST_Transform) back into WGS 1984, SRID 4326. WGS 1984 is best for database
storage and required for display in Leaflet.
10. Convert linestring geometry representation to Extended Well Known Binary(ST_AsEWKB). This ensures that data can
be be easily inserted into the strava_activities_masked table.
11. Query Activity ID of strava_activities record. Will be inserted as a foreign in strava_activities_masked table.
Parameters
----------
recordID. Int. Strava activity record ID.
Returns
-------
Nothing. Data are processed and committed to PostgresSQL/PostGIS database.
"""
session = Session()
# Simplification tolerance in geometry's units, which is meters here. Higher values more aggressively simplify
# geometries
simplifyFactor = 15
# Projected coordinate system SRID to transform geometries into. WGS84 UTM 10N is used since most
# activities are in within its zone in California.
geometricProj = 32610
# SRID of final data product, WGS 1984, to be used in Leaflet
webSRID = 4326
# Grid snapping grid size geometry's units, which is meters here. Larger values mean larger cells and greater
# vertex snapping
gridSnap = 5
# See https://gis.stackexchange.com/a/90271, fixes non-noded intersection error
nonNodedSnap = 0.0001
# Extract polygons from geometry collection
collectionExtract = 3
# Create CTE to query privacy zone polygons, combine them, extract polygons, and transform to geometricProj
privacy_cte = session.query(
sqlfunc.ST_Transform(
sqlfunc.ST_CollectionExtract(sqlfunc.ST_Collect(AOI.geom),
collectionExtract),
geometricProj).label("priv_aoi")).filter(
AOI.privacy == "Yes").cte("privacy_aoi")
if recordID == "All":
privacyClipQuery = session.query(
strava_activities.actID,
sqlfunc.ST_AsEWKB(
sqlfunc.ST_Transform(
sqlfunc.ST_MakeValid(
sqlfunc.ST_Multi(
sqlfunc.ST_Simplify(
sqlfunc.ST_SnapToGrid(
sqlfunc.ST_Difference(
sqlfunc.ST_SnapToGrid(
sqlfunc.ST_Transform(
strava_activities.geom,
geometricProj), nonNodedSnap),
privacy_cte.c.priv_aoi), gridSnap),
simplifyFactor), )), webSRID)))
else:
privacyClipQuery = session.query(strava_activities.actID, sqlfunc.ST_AsEWKB(
sqlfunc.ST_Transform(
sqlfunc.ST_MakeValid(
sqlfunc.ST_Multi(
sqlfunc.ST_Simplify(
sqlfunc.ST_SnapToGrid(
sqlfunc.ST_Difference(
sqlfunc.ST_SnapToGrid(sqlfunc.ST_Transform(strava_activities.geom, geometricProj),
nonNodedSnap), privacy_cte.c.priv_aoi)
, gridSnap),
simplifyFactor),
)), webSRID))) \
.filter(strava_activities.actID == recordID)
# Iterate over query to process data, add data to strava_activities_masked instance, and add instance to session
for i in privacyClipQuery:
session.add(strava_activities_masked(actID=i[0], geom=i[1]))
session.commit()
session.close()