3

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
  • 1
    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
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
  • Niccce. Thanx 4 feedback :) – Jendrusk Aug 20 '14 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.