Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

When running

SELECT * FROM dijkstra_sp('ways',232, 421);

I get the result ordered by gid, rather than by the order for reaching the target by starting at the source.

What is the reason for that and is there a way to get the results in the natural routing order?

Im using pgrouting 1.05-1~lucid1 from http://ppa.launchpad.net/georepublic/pgrouting

share|improve this question
    
I found a related thread on the pgrouting-users mailinglist, but it does not yet contain a solution. –  Florian Oct 12 '11 at 6:49
add comment

3 Answers 3

The reason seems to be that SQL does not specify the ordering of results when using joins. So postgre re-orders the results when doing a join. The wrapper functions (dijkstra_sp) join the results from the core routing function with the geom_table to obtain additional information.

A workaround is to generate artificial row numbers along with the results from the core routing function and order by that in the outer query where the join happens. Unfortunately that is only compatible with postgre 8.4 onwards.

SELECT rank,gid,the_geom FROM (
  select edge_id, ROW_NUMBER() OVER (PARTITION BY 1) as rank from    
    shortest_path(
      'SELECT gid as id, source::integer, target::integer, length::double precision as cost FROM ways',
      232, 421, false, false
    )
  ) as route join ways w on w.gid = edge_id order by rank;
share|improve this answer
    
+1 Thanks for the plain SQL solution. –  diciu Oct 13 '11 at 4:22
add comment

This is one of the flaws of pgRouting. I would have been much easier for the C++ implementation (shortest_path is directly mapped over a C++ function) to return a sequence number column than to have to resort to re-ordering in SQL.

The other option is to do it in PLPGSQL. You build an array out of the results and then you use the position in the array as the ordering indicator:

select array_agg(edge_id) from shortest_path('
SELECT gid as id,
source,
target,
length as cost
FROM trail_network',
src, dst,false,false) into edges;

FOR i in 1..array_upper(edges, 1) LOOP
    select name, symbol, st_length(the_geom) from trail_network where gid = edges[i] into tn;
    RAISE NOTICE 'Edge % on trail [%] symbol [%]', edges[i], tn.name, tn.symbol;
END LOOP;
share|improve this answer
add comment

I am having the exact same problem with my project: I need to have a correct order of gid's to retrace the path from the SQL result.

Looking at the implementations of dijkstra_sp and at the function from the pgRouting workshop dijkstra_sp_delta, it seems to me that the rank from Florian's answer is already implemented with a row returned by the identifier 'id'. So check it out - maybe it is a newer feature.

That is 'id' as in 'SELECT gid , id , ....' instead of 'SELECT gid , ...' though I didn't check and I may still be wrong .

PS: I also have Ubuntu 10.04 and ppa version of the pgRouting software library.

PPS: Please correct me if I am completely wrong and id is doing something different.

Edit : I seem to be completely wrong . Apparently the id field is a renumbering of the gid field in the query result to be at increasing by 1 difference , so if the result is ordered by id then all the numbers in the gid field will also be in order .

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.