How to give start and end vertex ids dynamically in pgr_dijkstra?

Question

I am new to PostGIS and Postgresql. I am using pgrouting and pgr_dijkstra query. pgr_dijkstra requires a start vertex and an end vertex and produces the shortest path between that. How do I give these dynamically (i.e. no static value)? I have columns of start ids and end ids. I have cluster number of each vertex. I need all the shortest paths between all combination of start and end provided that the start and end are not from same cluster. For example:

start_id     end_id
 1             2
 2             25
 5             12
 .             .
 .             . (and so on)

vertex id     cluster id
 1             A
 2             B
 5             C
 7             A
 25            C
 12            B 
 .             .
 .             . (and so on)        

So, I need all the shortest paths between 1 to 2, 1 to 25, 1 to 12 then between 2 to 25, 2 to 12 then 5 to 2, 5 to 25, 5 to 12 and so on if start and end are not from same cluster. And I will make a line (using st_makeline) by the vertices or nodes come as the pgr_dijkstra output. I have made the following query but unable to give the start id and end id dynamically with where clause.

select st_makeline(the_geom) from (SELECT edge.start_geom as the_geom FROM public.edge_table_with_limits edge INNER JOIN (SELECT node 
                                FROM pgr_dijkstra('SELECT gid AS id, 
                                start_id AS source, 
                                end_id AS target, 
                                (distance*weight_d) AS cost, 
                                (reverse_di*weight_d) AS reverse_cost 
                                FROM edge_table_with_limits',....,....,TRUE)) route ON edge.start_id = route.node
    UNION SELECT edge.end_geom as the_geom FROM public.edge_table_with_limits edge INNER JOIN (SELECT node 
                                FROM pgr_dijkstra('SELECT gid AS id, 
                                start_id AS source, 
                                end_id AS target, 
                                (distance*weight_d) AS cost, 
                                (reverse_di*weight_d) AS reverse_cost 
                                FROM edge_table_with_limits',....,....,TRUE)) route ON edge.end_id = route.node) as subquery

In the above query, if I put any start id and end id as constant number in the dotted blanks, this query is working fine. But, I need to do it dynamically. Query for selecting start id and end id is almost following:

select start_id, end_id from public.edge_table_with_limits where start_cls is not end_cls

I know these queries are needed to be changed, but how to do it?

Answer 1

You can use the array() function around a SELECT statement to pass in multiple vertex IDs. See example below:

 SELECT  ( SUM(w.km)*0.621371 ) As miles,r.start_vid,r.end_vid
 FROM pgr_dijkstra('SELECT id, source, target, cost FROM public.na_2po_4pgr as d,(SELECT ST_Expand(ST_Extent(geom_way),0.01) as box  FROM public.na_2po_4pgr as l1 WHERE l1.id=24518955 or l1.id=12125969) as box WHERE d.geom_way && box.box',
  array(SELECT nearest_vid as id FROM public.irs_tcn_geocoded as src where not nearest_vid is null order by 1) ,
  array(SELECT nearest_vid as id FROM public.hsoil_dest_geocoded as dest order by id)) AS r
  LEFT JOIN public.na_2po_4pgr AS w ON r.edge = w.id
   group by r.start_vid,r.end_vid;

---

Update for testing

"Array function is not working..." - LSG

You did not pass an array into the array() function.

First test to make sure your query is returning results:

SELECT start_id FROM edge_table_with_limits LIMIT 10

Then put the whole statement into the array:

SELECT array(SELECT start_id FROM edge_table_with_limits LIMIT 10)