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