# 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. – dkastl Aug 18 '14 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? – Stefan Aug 18 '14 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 – Jendrusk Aug 18 '14 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! – DPSSpatial Aug 18 '14 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. – Stefan Aug 19 '14 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 :) – Jendrusk Aug 20 '14 at 9:46