Fixing wrong direction of segments in pgRouting output

In my last post I covered the steps needed to set up a PostGIS database on Mac OS X together with pgRouting. Now, pgRouting solves my geospatial routing problems quite well, but I have run into a rather annoying problem: pgRouting returns the edges as they are stored in the database and not as traversed, so on some edges the direction (from-to or to-from) needs to be flipped around.

The following QGIS screenshot illustrates the problem (click for bigger version):

Some segments with wrong direction

You can see clearly that the line arrows do not all point in the same direction.

But don’t despair! My SQL skills are admittedly a bit rusty, but I managed to write a PL/pgSQL script that fixes the problem.

-- Wrapper for shortest_path() function to find the shortest route between two points
CREATE OR REPLACE FUNCTION dijkstra(
    geom_table varchar, source int4, target int4) 
    RETURNS SETOF GEOMS AS
$$
DECLARE 
    r record;
    path_result record;
    v_id integer;
    e_id integer;
    geom geoms;
    id integer;
BEGIN    
    id := 0;    
    FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
          'shortest_path(''SELECT gid AS id, start_id::integer AS source, end_id::integer AS target, ' || 
          'length(the_geom)::double precision AS cost FROM ' ||
      quote_ident(geom_table) || ''', ' || quote_literal(source) || 
          ' , ' || quote_literal(target) || ' , false, false), ' || 
          quote_ident(geom_table) || ' WHERE edge_id = gid ' 
        LOOP
            geom.gid      := path_result.gid;
            geom.the_geom := path_result.the_geom;
            id            := id + 1;
            geom.id       := id;                 
            RETURN NEXT geom;
        END LOOP;
    RETURN;
END;
$$ LANGUAGE 'plpgsql' VOLATILE STRICT; 

-- Returns records for the route from start to end with correct directions
CREATE OR REPLACE FUNCTION calc_route(
    geom_table varchar, start_id int, end_id int)
    RETURNS SETOF record AS
$$
DECLARE 
    r record;
    id int;
    prev int;
    i int;
BEGIN
    prev := 0;
    id   := start_id;
    FOR r IN EXECUTE 'SELECT start_id, end_id, route.* ' ||
      'FROM ' || quote_ident(geom_table) || ' JOIN ' ||
      '(SELECT * FROM dijkstra(' || quote_literal(geom_table) || ',' || start_id || ', ' || end_id || ')) AS route ' ||
      'ON ' || quote_ident(geom_table) || '.gid = route.gid ORDER BY route.id; '
    LOOP
        IF (r.start_id = id AND r.end_id <> prev) THEN
            RETURN NEXT r;
        ELSIF (r.end_id = id AND r.start_id <> prev) THEN
            i           := r.end_id;
            r.end_id    := r.start_id;
            r.start_id  := i;
            r.the_geom  := ST_Reverse(r.the_geom);
            RETURN NEXT r;
        ELSE
            RAISE NOTICE 'error: record % % %', r.start_id, r.end_id, r.id;
            RETURN;
        END IF;
        prev := r.start_id;
        id   := r.end_id;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE 'plpgsql' VOLATILE STRICT; 

Sample usage:

DROP TABLE IF EXISTS result;
SELECT start_id, end_id, gid, the_geom INTO result FROM calc_route('network', 22353, 21587)
AS (start_id int, end_id int, id int, gid int, the_geom geometry);

As a result, here is a QGIS screenshot with all segments in the correct direction (again, click for bigger version)

All segments with correct direction
All segments with correct direction

A note about line 59: this script assumes that pgRouting returns the segments in the correct order, which means that either the condition from line 50 or from line 52 evaluates to true. So, line 59 should never be executed. However, some people have reported on the pgrouting-users mailing list that the segments returned from pgRouting are not in the right order – in which case you would see the message from line 59.

5 thoughts on “Fixing wrong direction of segments in pgRouting output”

  1. Hi Dirk,
    Thank you for this post. I have only one question, I have QGIS 1.7.4 and I don’t know how to see segment direction? As I can see on your picture each segment has direction, how to do this?

    1. Hi,
      in the Layer Properties under Symbology, click on New symbology. Then click on Properties, and in the Symbol properties window, you can add additional symbol layers. Add a new symbol layer and choose Line decoration as symbol layer type.

  2. hi, I did same your directing, but when I query in Postgis, I found error:
    “NOTICE: error: record 193 189 1
    Query returned successfully with no result in 740 ms.”

    Can you help me fix this error? Thanks so much!

Leave a Reply