road_between_latlongs.py

import psycopg2
import random, string
hostname = 'localhost'
username = 'postgres'
password = '*****'
database = 'pgroute'
myConnection = psycopg2.connect(host=hostname, user=username, password=password, dbname=database )

# Simple routine to run a query on a database and print the results:
def doQuery( conn, query ) :
    cur = conn.cursor()
    cur.execute(query)
    if cur.rowcount>0:
        return cur.fetchall()
    cur.close()

    
def doQueryv2( conn, query ) :
    cur = conn.cursor()
    cur.execute(query)
    cur.close()
    
def distance_between_latlongs(long_s, lat_s,long_t, lat_t):
    s_geom = 'POINT({} {})'.format(long_s, lat_s)
    t_geom = 'POINT({} {})'.format(long_t, lat_t)
    doQuery(myConnection,"DROP TABLE IF EXISTS DD")
    s_query = "SELECT source FROM ways WHERE source_osm in (SELECT osm_id FROM ways_vertices_pgr ORDER BY the_geom <-> ST_GeometryFromText('{}',4326) LIMIT 1) LIMIT 1".format(s_geom)
    source = doQuery( myConnection, s_query )
    t_query =  "SELECT target FROM ways WHERE target_osm in (SELECT osm_id FROM ways_vertices_pgr ORDER BY the_geom <-> ST_GeometryFromText('{}',4326) LIMIT 1) LIMIT 1 ".format(t_geom)
    target = doQuery( myConnection, t_query )
    dist_query = "SELECT * INTO DD FROM pgr_dijkstra('SELECT gid AS id, source, target, length AS cost FROM ways',{}, {},directed := false)".format(source[0][0], target[0][0])
    doQueryv2( myConnection, dist_query )
    length_query = "SELECT SUM(length_m) FROM ways WHERE gid IN (SELECT edge FROM DD)"
    distance = doQuery( myConnection, length_query) # GETTING TOTAL DISTANCE
    return (distance[0][0]/1000)

print(distance_between_latlongs(77.6271,12.9279,77.5643, 13.0031))