def getNearestTrail(coordinate):
"""
Issues a SQLAlchemy/GeoAlchemy intersection query against the trails PostGIS table and returns a dictionary of nearest trail
and distance to trail in feet. Trails with name "Unknown" are excluded from results.
SQL expression is in raw form until it can be converted to SQLAlchemy format.
Query uses bounding box index location (<-> in SQL) to get candiate roads (40 records) then passes the results into the
ST_DistanceSphere function to get the nearest road. ST_DistanceSphere assumes the data are in WGS 84 and returns distance in meters.
The bounding box index calculations are fast but not entirely accurate, this method is useful for creating a smaller
list of results to run more costly ST_DistanceSphere calculations on.
Parameters
----------
coordinate: String
WKT representation of the incoming GPS point coordinates.
Returns
-------
result : dictionary
dictionary with the keys: "trail" and "trail_distance"
street:
String of nearest trail
distance:
Distance in feet to nearest trail
Results should never be empty, no matter how far nearest trail is.
"""
# Raw SQL query with triple quotes to maintain formatting, see function comments for descripton
sql = ('''
WITH nearestcanidates AS (
SELECT
trails.name,
trails.geom
FROM
"OSM_Central_CA_Trails" AS trails
WHERE
trails.name <> 'Unknown'
ORDER BY
trails.geom <-> (ST_GeomFromText(:param, 4326))
LIMIT 40)
SELECT
nearestcanidates.name,
ST_Distance(
ST_Transform(nearestcanidates.geom,2228),
ST_Transform(ST_GeomFromText(:param, 4326),2228)
) AS distance
FROM
nearestcanidates
ORDER BY
distance
LIMIT 1
''')
session = Session()
query = session.execute(sql, {"param": coordinate})
result = {}
query_count = 0
for dat in query:
result["trail"] = dat[0]
result["trail_distance"] = dat[1]
query_count += 1
if query_count == 0:
result['trail'], result['trail_distance'] = None, None
session.close()
return result
def getNearestRoad(coordinate):
"""
Issues a SQLAlchemy/GeoAlchemy intersection query against the roads PostGIS table and returns a dictionary of nearest road
and distance to road in feet.
SQL expression is in raw form until it can be converted to SQLAlchemy format.
Query uses bounding box index location (<-> in SQL) to get candiate roads (40 records) then passes the results into the
ST_DistanceSphere function to get the nearest road. ST_DistanceSphere assumes the data are in WGS 84 and returns distance in meters.
The bounding box index calculations are fast but not entirely accurate, this method is useful for creating a smaller
list of results to run more costly ST_DistanceSphere calculations on.
Parameters
----------
coordinate: String
WKT representation of the incoming GPS point coordinates.
Returns
-------
result : dictionary
dictionary with the keys: "street" and "distance".
street:
String of nearest street
distance:
Distance in feet to nearest road
Results should never be empty, no matter how far away nearest road is.
"""
sql = text("""WITH nearestcanidates AS (
SELECT
roads.name,
roads.geom
FROM
roads AS roads
WHERE
roads.name IS NOT NULL
ORDER BY
roads.geom <-> (ST_GeomFromText(:param, 4326))
LIMIT 40)
SELECT
nearestcanidates.name,
ST_DistanceSphere(
nearestcanidates.geom,
ST_GeomFromText(:param, 4326)
) AS distance
FROM
nearestcanidates
ORDER BY
distance
LIMIT 1""")
session = Session()
# Execute database query using the coordinates as a variable.
# print(f"Going to run query with coordinate: {coordinate}")
query = session.execute(sql, {"param": coordinate})
result = {}
query_count = 0
# Build out dict with each result in query
for dat in query:
result["street"] = dat[0]
# Convert meters (default unit of postgis function) to feet
result["distance"] = dat[1] * 3.28
query_count += 1
if query_count == 0:
result['street'], result['distance'] = None, None
session.close()
return result
