problem transforming pgr route to a line - unexpected extra lines from St_MakeLine

Question

I have created a line geometry from a pgr_dijkstra querry and I am getting an unexpected looking line in qgis. The following image illustrates the problem:

The magenta line is the line created from mapping the route from my table where I have created a line geometry using the following code:

update analysis.routing
set
routeline = (select st_makeline(pgr.geom) ,
from (select * from pgr_dijkstra('ways',61789,64493)) as pgr)

The dotted red line is the line created in qgis from calling the same select statement in the DB manager and adding the results to canvas:

select * from pgr_dijkstra('ways',61798,64493)

Does anyone have any recommendations on how to fix this problem with ST_MakeLine. That is what appears to be the problem. It looks like ST_MakeLine is connecting additional points which is adding the additional lines above that are not part of the pgr network. Thanks for your help.

Answer 1

For someone trying to solve a similar issue, an official solution to the directionality problem (pointed to by Daniel Kastl) exists. Exercise 8 in Geometry Directionality from the PostGIS tutorial shows how to do it.

Code below in case the link does not work:

WITH
dijkstra AS (
    SELECT * FROM pgr_dijkstra(
        'SELECT gid AS id, * FROM vehicle_net',
        (SELECT id FROM ways_vertices_pgr WHERE osm_id = 252643343),
        (SELECT id FROM ways_vertices_pgr WHERE osm_id = 302057309))
),
get_geom AS (
    SELECT dijkstra.*, ways.name,
        -- adjusting directionality
        CASE
            WHEN dijkstra.node = ways.source THEN the_geom
            ELSE ST_Reverse(the_geom)
        END AS route_geom
    FROM dijkstra JOIN ways ON (edge = gid)
    ORDER BY seq)
SELECT seq, name, cost,
    degrees(ST_azimuth(ST_StartPoint(route_geom), ST_EndPoint(route_geom))) AS azimuth,
    ST_AsText(route_geom),
    route_geom
FROM get_geom
ORDER BY seq;

The important construct in the above query is:

   CASE
      WHEN dijkstra.node = ways.source THEN the_geom
      ELSE ST_Reverse(the_geom)
   END AS route_geom 

i.e. reverse the geometry if the Dijkstra node is not the source and leave as is otherwise. I then used it for ST_MakeLine() and things appear peachy for now.

The solution used by EconGeo did not work for me as ST_MakeLine() would not work on multilinestring returned from ST_Collect/ST_Union.

Answer 2

In my opinion trouble is that st_makeline function is making line from set of edges that have different directions. I'm using function ST_Union to join edges and don't have this problem. Maybe you should use this function insted.