pgrouting shortest path result in wrong order

Question

I am using the Dijkstra shortest path algorithm to plot a route between two points using the latest version of pgrouting in postgresql 9.1 with postGIS 2.0.1.

I am able to get a correct result however the edges are in the wrong order so when I plot it in my app it does not draw correctly.

I have read several other posts with people having the same problem but none of the solutions provided have worked for me.

Here was the original query I was using:

SELET rt.gid, ST_AsGeoJson(rt.the_geom) AS coordinates, hh_2po_4pgr.gid 
FROM hh_2po_4pgr, (SELECT gid, the_geom
               FROM dijkstra_sp_delta('hh_2po_4pgr', 336, 557, 0.1)) as rt
WHERE hh_2po_4pgr.gid=rt.gid;

I then read a post which suggested doing it this way to order by the id:

select rank, gid, ST_AsGeoJson(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 hh_2po_4pgr', 557, 350, false, false)
             )
as route join hh_2po_4pgr h on h.gid = edge_id order by rank;

This worked for one of my route finding queries but I have tried it with others and it doesn't work.

I read this: ftp://ftp.remotesensing.org/pgrouting/forum/pgrouting.postlbs.org/ticket/156.html

which says the wrappers return an id which can be used to preserve order so I then created this query:

SELECT id, gid, ST_AsGeoJson(the_geom) FROM dijkstra_sp('hh_2po_4pgr', 557, 350)

but the ordering is still not correct.

Is there any way to fix this?

Thanks

Answer 1

try to order your result by sequence of your routing table. by doing that you will get your road segments in right order this is what i am doing this:

    SELECT pt.gid, seq, id1 AS node, id2 AS edge, cost, geom as line FROM pgr_dijkstra('SELECT 
      gid as id, 
      source::int4,
      target::int4,
      shape_leng::float8 as cost
      FROM my_table',
1543, 186, false, false  ) as di JOIN my_table pt  ON di.id2 = pt.gid )  order by seq

But when I tried to draw this route on the map for my mobile app (Android) as a complete route, road segments where not in the right order. Then I merged all the road segments as a single feature by using

       st_linemerge(st_union(geom))

and the result was perfect