Get km route between two points with lat / lng

Question

I'm doing the following query to get the distance between two points with pgrouting:

SELECT
        sum(cost) AS cost
FROM pgr_dijkstra(
                'SELECT id, source, target, km as cost FROM at_2po_4pgr',
                50347, 32988, false, false
        );

It turns out that the source and destination comes from nominatim geocode coordinates in, and I do not know how to use the coordinates as parameter in sql.

Can anyone help me?

Answer 1

If you have the points, then it's as simple as

SELECT ST_DistanceSpheroid(pt1, pt2);

Or alternately

SELECT ST_Distance(pt1::geography, pt2::geography);

Answer 2

The start and end parameter are node ID's. If you have the vertices table, then you can use the following query to find the nearest node to your point coordinates.

SELECT * FROM vertices 
    ORDER BY the_geom <-> ST_GeometryFromText('POINT(lon lat)',4326) 
    LIMIT 1;

If you don't have the vertices table, then you can (re)create it with the pgr_createVerticesTable function.

You could write your own custom wrapper function to put all this together. The pgRouting workshop gives a simple example, that you can use to get started.