def getDist(coordinate1, coordinate2):
"""
Get the distance between the newest incoming point and the most recent previously recorded
point, distance is reported as meters by default by ST_DistanceSphere.
Parameters
----------
coordinate:1 String
WKT representation of the most recently recorded GPS point coordinates.
coordinate2: String
WKT representation of the incoming GPS point coordinates.
Returns
-------
dist : Float
Distance in meters between the newest and most recent recorded points.
"""
session = Session()
# Geoalchemy ORM expression
res = session.query(
sqlfunc.ST_DistanceSphere(sqlfunc.ST_GeomFromText(coordinate1),
sqlfunc.ST_GeomFromText(coordinate2)))
# coordinate1 = coordinate1
# res = db.session.query(sqlfunc.ST_DistanceSphere(coordinate1,coordinate2))
dist = None
for i in res:
# Make sure the data comes through as a float, not a ORM object
dist = float(i[0])
# Round off number, don't need high precision
dist = round(dist, 1)
session.close()
return dist
def checkmd5(hash, pdfDate):
"""
Checks if the downloaded PDF's MD5 hash is already in Postgres and returns result.
:param hash: String
New MD5 hash
:param pdfDate: String
New PDF Date
:return: String
"Exists" - Hash is already in Postgres
"New" - Hash is not in Postgres and no other hashes exist for the PDF result week
"Update" - Hash is not in Postgres but other hashes exist for the PDF result week
"""
# Query Postgres with pdfDate of newly downloaded PDF
session = Session()
application.logger.debug(
f"Querying water quality MD5 hashes with the date {pdfDate}")
query = session.query(waterQualityMD5).filter(
waterQualityMD5.pdfdate == pdfDate).all()
hashList = []
application.logger.debug(f"Iterating ")
for i in query:
hashList.append(i.md5)
session.close()
if hash in hashList:
return "Exists"
elif len(hashList) == 0:
return "New"
else:
return "Update"
def getroles(username):
"""
Queries PostgreSQL database for user roles using username supplied by Flask HTTP Basic Auth.
Parses roles into a list.
Parameters
----------
username : String
Username as provided by user in the auth prompt.
Returns
-------
res : List
List of strings containing user roles.
All roles need to be returned.
None if empty results, user not in database.
"""
session = Session()
query = session.query(Roles.roles).filter(Roles.user == username).all()
# res += row
res = query[0]
if len(res) == 0:
res = None
# Roles are stored as comma seperated strings
# Convert result tuple into a list of strings split by commas
else:
res = res[0].split(",")
session.close()
return res
def getBeachWaterQual():
"""
Queries Postgres AWS RDS to return the most recent water quality report data for each beach that is tested in SB
County.
Data are spread across tables with mapped relationships.
This query joins the relevant tables and uses "distinct" on the waterQuality beach ID field, selecting only one
record per beach, then "order_by" is used on the joined MD5 table to grab only the most recent record per beach.
:return: List:
Nested lists containing SQL Alchemy query results:
3 query result objects:
waterQuality, waterqualityMD5 beaches
1 string:
geometry type of associated beach
2 floats:
x and y coordinates of the associated beach
"""
session = Session()
records = session.query(waterQuality, waterQualityMD5, beaches, sqlfunc.ST_GeometryType(beaches.geom),
sqlfunc.st_x(beaches.geom), sqlfunc.st_y(beaches.geom)) \
.join(waterQualityMD5) \
.join(beaches) \
.distinct(waterQuality.beach_id) \
.order_by(waterQuality.beach_id, waterQualityMD5.insdate.desc()).all()
# Can't close session here because these results are used in another function
# session.close()
return records
def getHashPass(username):
"""
Get the hashed password from PostgreSQL database using the username supplied by HTTP Basic Auth.
Parameters
----------
username : String
Username as provided by user in the auth prompt.
Returns
-------
res_dict : dictionary
keys:
username (string, parameter)
value:
hashed password (string)
None (if no matching user in table)
A dictionary is returned to maintain connection between username supplied and password.
"""
session = Session()
query = session.query(User.hashpass).filter(User.user == username).all()
res_dict = {}
for row in query:
res_dict[username] = row.hashpass
session.close()
if len(res_dict) == 0:
return None
else:
return res_dict
def getActiveSubID():
"""
Gets the active Strava webhook subscription ID from the database.
@return: Int. Active subscription ID
"""
session = Session()
query = session.query(webhook_subs).filter(
webhook_subs.activesub == "Yes").first()
session.close()
if query:
return int(query.sub_id)
def checkathleteID(athID):
"""
Checks if the provided actID is already within the database.
@param actID: Int. Strava Activity ID
@return: String. "True" or "False" depending if record exists in databse
"""
# Open session
session = Session()
# Query database
record = session.query(athletes.athlete_id == athID).first()
session.close()
return record
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 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 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 checkAthleteAndSub(athID, subID):
"""
Checks if provided athlete and subscription ID are in the database with an active subscription status
@param athID: Int. Strava athlete ID
@param subID: Int. Strava Webhook Subscription ID
@return: Object Instance. Instance of Athletes Model with results
"""
# Open session
session = Session()
# Query database
record = session.query(athletes). \
join(webhook_subs). \
filter(athletes.athlete_id == athID, webhook_subs.sub_id == subID, webhook_subs.activesub == "Yes").first()
session.close()
return record
def checkVerificationToken(token):
"""
Verifies that the provided verification token is in the database. Used as part Strava Webhook subscription callback
verification and setup process. Only needed on setup, further POST requests won't contain the token.
@param token: String. Strava verify token generated in script
@return: Instance of webhook sub model if exists, None otherwise
"""
# Open session
session = Session()
# Query database, get most recent record in case the token is in the database multiple times
record = session.query(webhook_subs.verify_token == token).order_by(
webhook_subs.id.desc()).first()
session.close()
if record:
return record
def getSubIdList():
"""
Gets list of subscription webhook IDs from database.
Check if used, likely can delete
Returns
-------
List. Subscription webhook IDs (Int) stored in database.
"""
session = Session()
query = session.query(athletes).all()
subIdList = []
for i in query:
subIdList.append(i.sub_id)
session.close()
return subIdList
def getAthleteList():
"""
Gets list of athlete IDs from database.
Returns
-------
List. Athlete IDs (int) stored in database.
"""
session = Session()
query = session.query(athletes).all()
athleteList = []
for i in query:
athleteList.append(i.athlete_id)
session.close()
return athleteList
def getStandards():
"""
Get the state health standards for ocean water quality tests.
Returns
-------
recDict : Dictionary
Dict of State health standards, with the standard name as the keys and values as values.
"""
session = Session()
records = session.query(stateStandards).all()
recDict = {}
for i in records:
recDict[i.Name] = i.StandardMPN
session.close()
return recDict
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 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 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 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 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 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 getNullBeaches(pdfDate):
"""
Returns list of beaches with null values for the given PDF test week. Only called when a update/re-sample PDF is
downloaded.
:param pdfDate: String
Date of new weekly PDF results
:return: List[Strings,]
Names of beaches with null test results
"""
session = Session()
query = session.query(waterQuality) \
.join(waterQualityMD5) \
.join(beaches) \
.filter(waterQualityMD5.pdfdate == pdfDate) \
.filter(or_(waterQuality.FecColi == None, waterQuality.Entero == None, waterQuality.TotColi == None)) \
.all()
nullbeaches = []
for i in query:
nullbeaches.append(i.beach_rel.BeachName)
session.close()
return nullbeaches
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 getBeachResults(beach):
"""
@param beach:
@return:
"""
session = Session()
records = session.query(waterQuality, waterQualityMD5) \
.join(waterQualityMD5) \
.join(beaches) \
.filter(beaches.BeachName == beach) \
.order_by(waterQuality.id.desc()) \
.limit(10)
resultDict = {}
for i in records:
resultDict[i[0].id] = {}
resultDict[i[0].id]["status"] = i[0].BeachStatus
resultDict[i[0].id]["date"] = i[1].pdfdate.isoformat()
resultDict[i[0].id]["s3PDFURL"] = create_presigned_url(i[1].pdfName)
# print(i[0].FecColi)
session.close()
return resultDict
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 AOIIntersection(geomdat):
"""
Issues a SQLAlchemy/GeoAlchemy intersection query against the AOI PostGIS table and returns a string of results.
The AOI table contains hand digitized locations of interest.
Currently only single POI results are returned, however this will change as POIs are added.
Parameters
----------
geomdat : String
WKT representation of the incoming GPS point coordinates.
Returns
-------
result : String
AOI results as a string with individual results comma seperated or None in case
of empty result.
A comma seperated string is returned for easier processing and insertion into
dictionary created by the handleTrackerQueries function.
"""
session = Session()
# SQLAlchemy and GeoAlchemy SQL query
query = session.query(AOI).filter(AOI.geom.ST_Intersects(geomdat))
# Get the size of the result, used for building out the string result.
query_count = 0
for i in query:
query_count += 1
if query_count > 0:
result = ""
count = 0
# Iterate over SQL Alchemy result object, if greater than 1 result build out with comma seperation.
for POI in query:
if count > 0:
# result object columns can be individually called with their column names, only want location info.
result += "," + POI.location
count += 1
else:
result += POI.location
count += 1
else:
session.close()
return None
session.close()
return result
def getPathPointRecords():
"""
Gets the most recently recorded GPS point, used to check for movement and to generate a GPS track. This entire function may not be needed
and can likely be combined with "getrecords".
Returns a dictionary with a single top level key and a nested dictionary of record details, kept logic for multiple top level keys in
case I need to build this function out.
Parameters
----------
Returns
-------
res_dict : Dictionary
Results of gps point query with details as keys.
"""
session = Session()
# records = session.query(gpsdatmodel.id, gpsdatmodel.lat, gpsdatmodel.lon, gpsdatmodel.geom, gpsdatmodel.timeutc,
# gpsdatmodel.date). \
# filter(gpsdatmodel.getLocalTime() >= dateTime). \
# order_by(gpsdatmodel.timeutc.desc()).limit(1).all()
records = session.query(gpsPointModel.id, gpsPointModel.lat, gpsPointModel.lon, gpsPointModel.geom,
gpsPointModel.timeutc, gpsPointModel.date). \
order_by(gpsPointModel.timeutc.desc()).limit(1).all()
res_dict = {}
row_count = 0
for row in records:
row_count += 1
if row_count > 0:
for row in records:
res_dict[row.id] = {
"id": row.id,
"lat": row.lat,
"lon": row.lon,
"utc": row.timeutc,
"date": row.date,
"geom": row.geom
}
session.close()
return res_dict
else:
session.close()
return None
def countyIntersection(geomdat):
"""
Issues a SQLAlchemy/GeoAlchemy intersection query against the county PostGIS table and returns a string of results.
Parameters
----------
geomdat : String
WKT representation of the incoming GPS point coordinates.
Returns
-------
TYPE
String of intersecting city or None if empty result.
Should only ever be a single result but logic is included in case of multiple
records returned.
"""
session = Session()
query = session.query(CACounty).filter(
CACounty.geom.ST_Intersects(geomdat))
query_count = 0
for i in query:
query_count += 1
# Logic to create a comma separted string of all results in case multiple counties
# are returned, this should not happen under normal circumstances
if query_count > 0:
result = ""
count = 0
for county in query:
if count > 0:
result += "," + county.name
count += 1
else:
result += county.name
count += 1
else:
session.close()
return "Out of State!"
session.close()
return result
