Created
November 29, 2015 04:31
-
-
Save mbainrot/f17e650c3f40dec4b318 to your computer and use it in GitHub Desktop.
Queries an OSM PostGIS database for all the petrol stations along a GPX route (obtained via openrouteservice).
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/python3.4 | |
import psycopg2 | |
import xmltodict | |
import pickle | |
datafile = "/home/max/Desktop/map2.gpx" | |
with open(datafile) as fd: | |
oGpxFile = xmltodict.parse(fd.read()) | |
try: | |
conn = psycopg2.connect("dbname='osm_australia_dump_291115' user='postgres' host='localhost'") | |
except: | |
print("I am unable to connect to the database") | |
exit | |
i = 0 | |
last_lat = 0 | |
last_lon = 0 | |
hits = [] | |
#meh = ''' | |
for coord in oGpxFile["gpx"]["trk"]["trkseg"]["trkpt"]: | |
lat = float(coord["@lat"]) | |
lon = float(coord["@lon"]) | |
cur = conn.cursor() | |
query = """SELECT * FROM ST_Distance(ST_Transform(ST_GeomFromText('POINT(%s %s)',4326),900913),ST_Transform(ST_GeomFromText('POINT(%s %s)',4326),900913))""" | |
data = (last_lon,last_lat, lon, lat,) | |
cur.execute(query,data) | |
row = cur.fetchone() | |
# print(row[0]) | |
if(row[0] > 1000): | |
last_lon = lon | |
last_lat = lat | |
cur = conn.cursor() | |
query = """SELECT osm_id FROM planet_osm_point WHERE ST_Distance(way, ST_Transform(ST_GeomFromText('POINT(%s %s)',4326),900913)) <= 1000 AND amenity='fuel';""" | |
data = (lon,lat,) | |
cur.execute(query,data) | |
rows = cur.fetchall() | |
print(i,": Got",len(rows),"rows back") | |
for row in rows: | |
osm_id = row[0] | |
if not osm_id in hits: | |
hits.append(osm_id) | |
i = i + 1 | |
print("Found a total of",len(hits),"distinct entities along the GPX route") | |
with open('data.pickle', 'wb') as f: | |
# Pickle the 'data' dictionary using the highest protocol available. | |
pickle.dump(hits, f, pickle.HIGHEST_PROTOCOL) | |
##### | |
#exit() | |
#''' | |
with open('data.pickle', 'rb') as f: | |
# The protocol version used is detected automatically, so we do not | |
# have to specify it. | |
hits = pickle.load(f) | |
for hit in hits: | |
cur = conn.cursor() | |
query = """SELECT osm_id,name,ST_AsText(ST_Transform(way,4326)) FROM planet_osm_point WHERE osm_id = %s;""" | |
data = (str(hit),) | |
cur.execute(query,data) | |
rows = cur.fetchall() | |
for row in rows: | |
osm_id = row[0] | |
name = row[1] | |
way = row[2] | |
way = way.replace("POINT(","") | |
way = way.replace(")","") | |
way_parts = way.split(' ') | |
way = (float(way_parts[1]),float(way_parts[0])) | |
print(osm_id,",",name,",",way) | |
conn.close() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment