4

I want to find the distance (km) between each origin point to the closest destination point (restaurants) using pgrouting.

I have 3 tables

  1. my road network with nodes and edges
  2. 140 origin points - centroids of neighbourhoods
  3. 3000 destinations - restaurants

The road network already has source, target, length (cost) and topology.

I have used tutorials that specify in the SQL query which nodes to calculate cost between such as this one:

SELECT seq, id1 AS source, id2 AS target, cost FROM pgr_kdijkstraCost(
'SELECT id, source, target, cost FROM edge_table WHERE cost >= 0',
10, array[4,12], false, false

Instead of explicitly referencing origin as "Node 10" and Destinations as "Nodes[4,12]" is there a way to reference my tables that list all 3,000 destinations?

  • Welcome to gis.stackexchange! Please note that good questions are expected to contain information about what you have tried so far and where you are stuck. Asking for somebody else to do your work completely is generally frowned upon. – underdark Dec 16 '14 at 19:46
  • Apologies, I've clarified my question. – nvijaya Dec 16 '14 at 19:57
4

The answer of @underdark was close, but does not work, because the destination ID's must be passed as an array.

Here is the same example of the workshop using queries instead of passing the ID's directly.

SELECT seq, id1 AS source, id2 AS target, cost FROM pgr_kdijkstraCost(
    'SELECT id, source, target, cost FROM edge_table WHERE cost >= 0', 
    (SELECT id FROM edge_table WHERE id=10), 
    (SELECT array_agg(id) FROM edge_table WHERE id IN (4,12)), 
    false, false
);
1

You could try something like this

SELECT seq, id1 AS source, id2 AS target, cost FROM pgr_kdijkstraCost( 'SELECT id, source, target, cost FROM edge_table WHERE cost >= 0', (select nodeid FROM origins), (select nodeid FROM destinations), false, false)

not tested, might be with or without brackets.

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.