Issues while plotting the results of pgRouting shortest path

Question

I am trying to plot the shortest path returned by pgRouting. However, I am having some issues. Sometimes it comes out a mess.

I have just joined the coordinates of the edges returned by the shortestpath query. However as mentioned in this link http://underdark.wordpress.com/2011/02/07/a-beginners-guide-to-pgrouting/ it says that it uses the necessary geometries. The path is plotted fine by QGIS. It is mentioned in this line

Therefore, we’ll join the results of the previous query with the network table containing the necessary geometries. Suggestions?

---

SELECT x1,y1,x2,y2 
FROM shortest_path('
   SELECT id,
          source::integer,
          target::integer,
          cost::double precision 
   FROM at_2po_4pgr',
   $start_node, $end_node, false, false) 
INNER JOIN at_2po_4pgr 
ON edge_id = at_2po_4pgr.id. 

I used osm2po to generate the at_2po_4pgr table which contains the edges, their start and end vertices and other information.

I send the bunch of returned x1,y1,x2,y2 and then join these coordinates. Sometimes the path looks fine but sometimes it's a mess. The path looks fine when I use QGIS. But they have different implementation I guess.

---

Actually my question is, is it enough to send the start and end points of the edges returned by the query and then join these start and end points of each edge by straight line? Or I am supposed to use the geometry given by multilinestring which I have less idea about

---

I am trying to actually figure out what the "geom_way" column exactly represents and how to use that in my case. I created a network topology and persisted it to postgresql database using osm2po tool for a certain region. Now I am using pgrouting on it to find the shortest route. I am not sure how this column is used by qgis to display the route. It's just a bunch of number, how can I use it in my case?

I am actually using gpx format file to transfer the shortest path. How can I exploit this geometry and send that information in gpx so that it is displayed nicely.

Answer 1

Based on this old pgRouting bug report: ftp://ftp.remotesensing.org/pgrouting/forum/pgrouting.postlbs.org/ticket/159.html, I would say that joining the routing results with the network table returns the rows in their natural order (the way they were inserted to the table) instead of the routing order. The advise was:

To order shortest path search results please try to use wrapper functions - they allow you to order your result by id field.

Update:

The problem you might have run into is that start/end points are defined by the digitizing direction and not by the direction the link is traversed in the routing result.

eg. two links might run in route direction, one in the opposite direction:

      Link1                      Link2                            Link3
|-------------------------><-------------------------------||----------------->
1                         22                               11                 2

Note: pgRouting does not switch the directions of links!!!

You have to ensure that you are sending the points in the correct order so that x2/y2 of Link1 == x1/y1 of Link 2 and so on.

---

I am not sure how "geom_way" column is used by qgis to display the route. It's just a bunch of number, how can I use it in my case?

What you are seeing is the serialization of the geometry object. If you want something human-readable, I'd suggest wrapping "geom_way" in asText()

select asText("geom_way")