def insmd5(MD5, pdfDate, pdfName):
"""
Add water quality md5 and other information to postgres database. After committing, call on the primary key, id,
to get the persisted, auto-incremented, id. The record must be committed before this value is assigned.
:param MD5: String of MD5 hash.
:param pdfDate: String of pdfDate.
:param pdfName: String of PDF name, without file location
:return:
Int, primary key of new MD5 entry
"""
session = Session()
# application.logger.debug(f"Inserting new md5 hash using the following details: md5:{MD5}, pdfdate:{pdfDate}",
# f" pdfname:{pdfName}, insdate:{datetime.now()}")
application.logger.debug(
f"Inserting new md5 hash using the following details:")
application.logger.debug(MD5)
application.logger.debug(pdfDate)
application.logger.debug(pdfName)
application.logger.debug(datetime.now())
# Get datetime in PST since records are from this time zone
newrec = waterQualityMD5(md5=MD5,
pdfdate=pdfDate,
pdfName=pdfName,
insdate=datetime.now(
pytz.timezone("America/Los_Angeles")))
session.add(newrec)
session.commit()
newId = newrec.id
session.close()
application.logger.debug("Data added to MD5 table!")
application.logger.debug(f"New water quality md5 hash id is {newrec.id}")
return newId
def insertSubUpdate(content):
"""
Inserts Strava webhook subscription data into Postgres database. This information will be used to get full activity
information from another query.
Parameters
----------
content. Subscription Update object of Strava webhook update generated by Stravalib
Returns
-------
Nothing. Updates database.
"""
# Verify is activity title is in update data, if not set to None. Some activities may have empty titles.
if "title" in content.updates.keys():
title = content.updates['title']
application.logger.debug(f"Title of new activity is {title}")
else:
title = None
session = Session()
insert = sub_update(aspect=content.aspect_type,
event_time=datetime.fromtimestamp(
content.event_time.timestamp),
object_id=content.object_id,
object_type=content.object_type,
owner_id=content.owner_id,
subscription_id=content.subscription_id,
update_title=title)
session.add(insert)
session.commit()
session.close()
application.logger.debug(f"New webhook update has been added to Postgres!")
def insertOriginalAct(actDict):
"""
Inserts new activity into database, POSTed by Strava webhook update or by manually triggering process activity
event route.
Parameters
----------
actDict. Dict. Generated by StravaWebHook.handle_sub_update() or by getStravaActivities.processActs().
Returns
-------
Nothing. Data are inserted into Postgres/PostGIS.
"""
insert = strava_activities(
actID=actDict['actId'],
upload_id=actDict['upload_id'],
name=actDict['name'],
distance=actDict['distance'],
moving_time=actDict['moving_time'],
elapsed_time=actDict['elapsed_time'],
total_elevation_gain=actDict['total_elevation_gain'],
elev_high=actDict['elev_high'],
elev_low=actDict['elev_low'],
type=actDict['type'],
start_date=actDict['start_date'],
start_date_local=actDict['start_date_local'],
timezone=actDict['timezone'],
utc_offset=actDict['utc_offset'],
start_latlng=actDict['start_latlng'],
end_latlng=actDict['end_latlng'],
start_latitude=actDict['start_latitude'],
start_longitude=actDict['start_longitude'],
achievement_count=actDict['achievement_count'],
pr_count=actDict['pr_count'],
private=actDict['private'],
gear_id=actDict['gear_id'],
average_speed=actDict['average_speed'],
max_speed=actDict['max_speed'],
average_watts=actDict['average_watts'],
kilojoules=actDict['kilojoules'],
description=actDict['description'],
workout_type=actDict['workout_type'],
calories=actDict['calories'],
device_name=actDict['device_name'],
manual=actDict['manual'],
athlete_id=actDict['athlete_id'],
type_extended=actDict['type_extended'],
avgtemp=actDict['average_temp'],
has_heartrate=actDict['has_heartrate'],
average_cadence=actDict["average_cadence"],
average_heartrate=actDict['average_heartrate'],
max_heartrate=actDict['max_heartrate'],
geom=actDict['geom_wkt'])
session = Session()
session.add(insert)
session.commit()
session.close()
application.logger.debug(
f"New webhook update for activity {actDict['actId']} has been added to Postgres!"
)
def create(name, url):
'''新增'''
job = Job()
job.name = name
job.url = url
job.create_time = datetime.now()
session = Session()
session.add(job)
session.commit()
session.close()
def insertVerifyToken(token):
"""
Inserts the provided generated token into database.
@param token: String. Strava verify token generated in script.
@return: Nothing
"""
# Open session
session = Session()
# Create new record
newRec = webhook_subs(verify_token=token)
# Add new record to session
session.add(newRec)
session.commit()
session.close()
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()
