pgrouting - network distance many-to-one shortest paths

Question

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?

Answer 1

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

Answer 2

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.