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 insertWaterQual(beachDict, md5_fk):
"""
Inserts water quality results into water quality database table with md5 foreign key relationship.
Parameters
----------
beachDict: Dictionary. Dictionary containing values to be inserted into database.
md5_fk: String. Foreign key from md5 table.
Returns
-------
Print statement.
"""
session = Session()
inslist = []
# Iterate over beaches in dictionary creating waterQuality objects for each beach key
for key in beachDict.keys():
inslist.append(
waterQuality(
beach_id=beachDict[key]['fk'],
TotColi=beachDict[key]['Total Coliform Results (MPN*)'],
FecColi=beachDict[key]["Fecal Coliform Results (MPN*)"],
Entero=beachDict[key]['Enterococcus Results (MPN*)'],
ExceedsRatio=beachDict[key]['Exceeds FC:TC ratio standard **'],
BeachStatus=beachDict[key]['Beach Status'],
resample=beachDict[key]['resample'],
md5_id=int(md5_fk)))
# Add list of objects to session
session.add_all(inslist)
session.commit()
session.close()
application.logger.debug("Data added to water quality table!")
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 updateSubId(subId, verifytoken):
"""
Updates webhook subscriptions table with the new subscription id provided by Strava then updates all athletes
with the new subID foreign key.
@param subId: Int. Webhook subscription ID provided by Strava API
@param verifytoken: String. Script generated verification token
@return: Nothing. Database is updated
"""
session = Session()
try:
application.logger.debug(
f"Updating record with the following token: {verifytoken}")
# Update recently created record which only has the verify token populated
# application.logger.debug(session.query(webhook_subs.verify_token == verifytoken).first())
insQ = session.query(webhook_subs).filter(
webhook_subs.verify_token == verifytoken).update({
webhook_subs.sub_id:
subId,
webhook_subs.activesub:
"Yes"
})
session.commit()
# Get the primary key from the new webhook subscription
record = session.query(webhook_subs).filter(
webhook_subs.verify_token == verifytoken).first()
# Update all athletes with the new subscription entry foreign key
session.query(athletes).update({athletes.sub_id: record.id})
session.commit()
session.close()
except Exception as e:
application.logger.debug(
f"Update Strava athlete sub Id failed with the exception: {e}")
errorEmail.sendErrorEmail(script=updateSubId.__name__,
exceptiontype=e.__class__.__name__,
body=e)
def handleTrackerPOST(data):
"""
Parameters
----------
data
Returns
-------
"""
session = Session()
newObjDict = {}
# Create instance of a gps track
trackRecord = gpsTrackObj(data)
# Check if there has been movement, if so add to new object dictionary, otherwise no entry will be made
if trackRecord["activity"] == "Yes":
newObjDict["track"] = trackRecord["model"]
# Add new gps record object to new objects dictionary
newObjDict["gpspoint"] = newGPSRecord(data, trackRecord["activity"])
# Iterate over new objdict, can allow building out so many things can be committed to db
# This allows for empty models to be skipped
newObjs = []
for obj in newObjDict.keys():
newObjs.append(newObjDict[obj])
# Add new objects to session and commit them
session.add_all(newObjs)
session.commit()
session.close()
def update(job_id, name, url):
'''修改'''
session = Session()
job = session.query(Job).get(job_id)
job.name = name
job.url = url
session.commit()
session.close()
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 deleteVerifyTokenRecord(token):
"""
Deletes script generated Strava webhook verification token from database, called when an existing webhook is removed
@param token: String. Strava verify token generated in script
@return: Nothing
"""
# Open session
session = Session()
session.query(webhook_subs).filter(
webhook_subs.verify_token == token).delete()
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 getAuth():
"""
Loads Strava client authentication details from Postgres and creates a authorized client instance.
Checks if access token is expired, if so it is refreshed and updated.
Returns
-------
client. Stravalib model client instance. Contains access token to Strava API for the athlete, ID is hard coded for now.
"""
# Build empty stravalib client instance
client = Client()
# create db session
session = Session()
# Hard coded athlete id
athleteID = 7170058
authDict = {}
# Load tokens and expiration time from Postgres
query = session.query(athletes).filter(athletes.athlete_id == athleteID)
for i in query:
authDict["Access_Token"] = i.access_token
authDict["Expiration"] = i.access_token_exp
authDict["Refresh_Token"] = i.refresh_token
application.logger.debug(f"Auth token details are: {authDict}")
# Check if access token has expired, if so request a new one and update Postgres
if time.time() > authDict["Expiration"]:
application.logger.debug("Access token has expired, refreshing")
refresh_response = client.refresh_access_token(
client_id=int(os.environ.get('STRAVA_CLIENT_ID')),
client_secret=os.environ.get('STRAVA_CLIENT_SECRET'),
refresh_token=authDict["Refresh_Token"])
# Update access token and expiration date
session.query(athletes).filter(athletes.athlete_id == athleteID). \
update({athletes.access_token: refresh_response['access_token'],
athletes.access_token_exp: refresh_response['expires_at']})
# Commit update
session.commit()
# Set Strava auth details
client.access_token = refresh_response['access_token']
client.refresh_token = authDict["Refresh_Token"]
client.token_expires_at = refresh_response['expires_at']
else:
application.logger.debug("Access token is fresh, no refresh required")
# Access token is up-to-date, set client details
client.access_token = authDict["Access_Token"]
client.refresh_token = authDict["Refresh_Token"]
client.token_expires_at = authDict["Expiration"]
# Close out session
session.close()
return client
def setWebhookInactive(subID):
"""
Sets provided subscription to inactive status
@param subID: Int.
@return: Nothing
"""
# Open session
session = Session()
# Set active status to No
session.query(webhook_subs).filter(webhook_subs.sub_id == subID).update(
{webhook_subs.activesub: "No"})
# Commit changes
session.commit()
# Close out session
session.close()
def updateExistingActivity(update):
"""
Updates existing activity in database, currently only handles activity title updates.
@param update: Stravalib update instance
@return: Nothing.
"""
# Get object ID
objectID = update.object_id
# Get new activity title, if applicable
newTitle = update.updates['title']
session = Session()
# use SQL alchemy to update existing feature title
session.query(strava_activities).filter(strava_activities.actID == objectID). \
update({strava_activities.name: newTitle})
session.commit()
session.close()
def removeActivityFromDB(actID):
"""
Removes a activity from the original and public activities database tables.
@param actID: Int. Strava Activity ID
@return: Nothing.
"""
# Open session
session = Session()
# Delete from masked table
session.query(strava_activities_masked).filter(
strava_activities_masked.actID == actID).delete()
# Delete from original DB table
session.query(strava_activities).filter(
strava_activities.actID == actID).delete()
# Commit changes
session.commit()
# Close session
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()
def delete(job_id):
'''删除'''
session = Session()
session.query(Job).filter(Job.id == job_id).delete()
session.commit()
session.close()
