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) :
Then you can use it in another select: