Queries an OSM PostGIS database for all the petrol stations along a GPX route (obtained via openrouteservice).

get_all_petrol_stations2.py

#!/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()