def validate(table=None, column=None):
"""
Attempt to fix invalid geometries.
Either a table or a column must be specified. If a table is specified,
the geom column will be validated.
Parameters
----------
table : sqlalchemy.ext.declarative.DeclarativeMeta, optional
Table ORM class containing geom column to validate.
column : sqlalchemy.orm.attributes.InstrumentedAttribute, optional
Column ORM object to validate.
Returns
-------
None
"""
# Get Table and Column objects.
if column:
geom = column.property.columns[0]
t = geom.table
else:
t = table.__table__
geom = t.c.geom
# Get column data and geometry type.
data_type = geom.type
geom_type = data_type.geometry_type
if "point" in geom_type.lower():
geom_type_num = 1
elif "linestring" in geom_type.lower():
geom_type_num = 2
elif "polygon" in geom_type.lower():
geom_type_num = 3
else:
geom_type_num = None
with db.session() as sess:
# Fix geometries using ST_MakeValid. If geometry type is
# point/linestring/polygon, only extract elements of those types,
# to prevent invalid data type errors.
if geom_type_num:
valid_geom = func.ST_CollectionExtract(
func.ST_MakeValid(geom),
geom_type_num
)
else:
valid_geom = func.ST_MakeValid(geom)
sess.query(t).filter(
~geom.ST_IsValid()
).update(
{geom: valid_geom}, synchronize_session=False
)
def pg_geometry(feature):
geom = dumps(feature["geometry"])
_ = func.ST_GeomFromGeoJSON(geom)
return func.ST_SetSRID(func.ST_MakeValid(func.ST_Multi(_)), 4326)
def add_airspace(self, country_code, airspace_class, name, base, top, geom_str):
try:
geom = loads(geom_str)
except ReadingError:
print name + "(" + airspace_class + ") is not a polygon (maybe not enough points?)"
return False
# orient polygon clockwise
geom = polygon.orient(geom, sign=-1)
if not airspace_class:
print name + " has no airspace class"
return False
base = self.normalise_height(base, name)
top = self.normalise_height(top, name)
flightlevel_re = re.compile(r'^FL (\d+)$')
match = flightlevel_re.match(base)
if match and int(match.group(1)) >= 200:
print name + " has it's base above FL 200 and is therefore disregarded"
return False
airspace = Airspace()
airspace.country_code = country_code
airspace.airspace_class = airspace_class
airspace.name = name
airspace.base = base
airspace.top = top
# Check geometry type, disregard everything except POLYGON
if geom.geom_type != 'Polygon':
print name + " is not a polygon (it's a " + geom.geom_type + ")"
return False
wkb = from_shape(geom, srid=4326)
# Try to fix invalid (self-intersecting) geometries
valid_dump = (func.ST_Dump(func.ST_MakeValid(wkb))).geom
valid_query = db.session.query(func.ST_SetSRID(valid_dump, 4326)).order_by(func.ST_Area(valid_dump).desc()).first()
if not valid_query:
print 'Error importing ' + name
print 'Could not validate geometry'
return False
else:
wkb = valid_query[0]
geom_type = db.session.query(func.ST_GeometryType(wkb)).first()[0]
if geom_type != 'ST_Polygon':
print name + " got some errors makeing it valid..."
return False
tolerance = 0.0000001
simplify = lambda x: func.ST_SimplifyPreserveTopology(x, tolerance)
airspace.the_geom = case(
[
(func.ST_IsValid(wkb), wkb),
(func.ST_IsValid(simplify(wkb)), simplify(wkb)),
],
else_=None)
db.session.add(airspace)
return True
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()