# How to calculate the shortest distance for multiple source and targets using PgRouting?

I have a table with points and a road network which includes driveways (where you enter the highway).

I want to find the point which is closest to a driveway. So I was thinking I can calculate the distance from all the points to all the driveways (maybe within a specific boundary). And from there I can find which point is closest to a driveway. I have joined the points with the network, so I know which ‘source’ I can use from the network. I also know the ‘targets’.

And I can calculate the distance from one point (source-network) to one target using the source and target columns in the network. Using the following query:

``````    with subset as (
SELECT seq, id1 AS node, id2 AS edge, cost
FROM pgr_dijkstra(
'SELECT gid as id, source, target, st_length(geom) as cost FROM roadnetwork,
473384, 440012, FALSE, False
) as di
)
select sum(cost)::decimal (8,2) from subset
``````

But how can I calculate multiple sources to multiple target? So in the query above I want the numbers 473384 and 440012 to be ‘flexible’.

Or is there any other way to calculate this?

Hope someone can help me with this problem

• You might want to use pgr_kDijkstra as answered in gis.stackexchange.com/questions/74564/… already. Aug 18, 2014 at 12:45
• Thanks for the suggestion. I have looked at pgr_kDijkstra, but still: how can you calculate multiple sources to multiple targets. Do you perhaps have an example query? Aug 18, 2014 at 14:11
• You could paste your query into stored procedure with source and target as input and cost as output... then use it to update table with your points, or sth Aug 18, 2014 at 14:13
• I'm wondering this too, as in: how can the pgr_kDijkstra function be used in the way the ST_Distance function is implemented... now THAT would be huge! Aug 18, 2014 at 15:44
• @Jendrusk, I am not familiar with stored procedures (functions). I will do some reading and experiment and hope I will figure it out. Aug 19, 2014 at 9:47

This function code could be (it's an example - did not check if it's working) :

``````CREATE OR REPLACE FUNCTION give_me_cost(source integer, target integer)
RETURNS decimal(8,2) AS
\$BODY\$
with subset as (
SELECT seq, id1 AS node, id2 AS edge, cost
FROM pgr_dijkstra(
'SELECT gid as id, source, target, st_length(geom) as cost FROM roadnetwork',
\$1, \$2, FALSE, False
) as di
)
select sum(cost)::decimal (8,2) from subset

\$BODY\$
LANGUAGE sql VOLATILE;
``````

Then you can use it in another select:

``````Select
source_id, targer_id, give_me_cost(source_id, targer_id) as cost
from
sometable
``````
• Niccce. Thanx 4 feedback :) Aug 20, 2014 at 9:46