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:

enter image description here

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
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.

  • Can you please post your full query? I'm having trouble understanding what's happening here, since pgr_dijkstra doesn't return a geom column yet your query is referencing "pgr.geom". Do you have a join to your original geometry table which you've omitted here?
    – ndawson
    May 14 '14 at 22:26
  • apologies, I have implemented the function as described here in section 8.3 pgrouting workshop where the pgr_dijkstra returns seq, edge, geom(linestring) as the result in a table, negating the need for joining back to the ways table
    – EconGeo
    May 14 '14 at 22:49
  • ok - if you run your query: "select * from pgr_dijkstra('ways',61789,64493)", are the sequences in order? ie, is seq sequential?
    – ndawson
    May 14 '14 at 23:55
  • yes it is in order. the function definition from the pgrouting workshop has an order by seq call at the end of it
    – EconGeo
    May 15 '14 at 8:12

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.

  • Yes, that is what I ended up doing and it works. But your description totally makes sense of why it would draw those weird lines not on the network,must have been a reverse direction edge in there and linked them up backwards in the st_makeline call. I had to do a st_multi(st_union)) since some of my routes only had one edge and it didn't like linestrings in a multilinestring column. Currently at 33671425 ms. Only need to do it once on my data since it's research. I also noticed that once I do ST_union and get my multilinestring I can knit it together with st_makeline and it works.
    – EconGeo
    May 15 '14 at 8:20

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:

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
            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,
FROM get_geom

The important construct in the above query is:

      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.


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.