[GIS] How to use pgr_dijkstra many-to-many

arraypgroutingpostgispostgresqlshortest path

I am using pgr_dijkstra(or pgr_dijkstraCost) to find the shortest path and/or cost for drawing isochrone map. Since I need to calculate the shortest path from each of over 100,000 nodes to multiple destinations, I am planning to use pgr_dijkstraCost many-to-many function ↓

http://docs.pgrouting.org/2.2/en/src/dijkstra/doc/pgr_dijkstraCost.html

And, as shown in the example below, I have no problems when I input start_vids and end_vids as a form like ARRAY[2, 7] or ARRAY[3, 11]. 

SELECT * FROM pgr_dijkstraCost(
    'select id, source, target, cost, reverse_cost from edge_table',
    ARRAY[2, 7],
    ARRAY[3, 11]);

However, what I really want to do is to input arrays by selecting a column from a table. In other words, I want to calculate the shortest path from every single node in my table. For example, I've tried that

SELECT * FROM pgr_dijkstraCost(
    'select id, source, target, cost, reverse_cost from edge_table',
    select array(select source from edge_table),
    ARRAY[3, 11]);

But this returns an error: "ERROR: syntax error at or near "select" / SQL state: 42601 / Character: 171." What's wrong with my code? And, please tell me how to deal with the query result as an input parameter.

Best Answer

You don't need the SELECT of the array, just return the array of the SELECT (in other words, remove the "select"):

SELECT * FROM pgr_dijkstraCost(
    'select id, source, target, cost, reverse_cost from edge_table',
    array(select source from edge_table),
    ARRAY[3, 11]);
Related Question