2

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?

  • Which version of pgRouting are you using? If you are using pgRouting 2.2+ you can use pgr_dijkstraVia which can take your sequence of nodes in one query – LR1234567 Jan 5 '17 at 15:02
  • I don't want to give multiple vertices in one route. I want multiple routing from a set of start and end ids – LSG Jan 9 '17 at 12:25
2

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)
  • Array function is not working. Even the following query is not creating any array containing the start_ids: "select array_agg(start_id) from edge_table_with_limits " – LSG Jan 9 '17 at 12:21
  • Array_agg is returning an empty array. Why? – LSG Jan 9 '17 at 13:14
  • @LSG updated answer to help with your new questions – kttii Jan 9 '17 at 15:15
  • @LSG You are welcome. If I answered your question, would you please mark it as answered? – kttii Jan 10 '17 at 14:13

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.