# pgrouting - network distance many-to-one shortest paths

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. Commented Dec 16, 2014 at 19:46
• Apologies, I've clarified my question. Commented Dec 16, 2014 at 19:57

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
);
``````

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.