[GIS] How to calculate the shortest distance for multiple source and targets using PgRouting

Networkpgroutingpostgispostgresqlroute

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

Best Answer

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
Related Question