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

Question

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

Answer 1

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