7. Writing a pl/pgsql Wrapper

../_images/route1.png

It is the user responiblility to write their own wrapper functions for their own use cases.

pgRouting functions provide a “low-level” interface to algorithms and return ordered identifiers rather than routes with geometries. Creating a complex queries, views or wrapper functions, can be used to connect to a high level application.

Just considering the different ways that the cost can be calculated, makes it almost impossible to create a general wrapper, that can work on all applications, for example:

  • The data may come from a source that is not OpenStreetMap.
  • The column names may be in other language than English.

Visualizing the result

Instead of looking at rows, columns and numbers on the terminal screen, it’s more interesting to visualize the route on a map. Here a few ways to do so:

  • Store the result as table with CREATE TABLE <table name> AS SELECT ...
  • Store the result as view with CREATE VIEW  <view name> AS SELECT ...

OSGeo Live provides FOSS4G software for visualization, for example:

  • QGIS (DB Manager, Layer Filter or pgRouting Plugin)
  • WMS/WFS server with Geoserver/Mapserver.

In this chapter we will look at some common wrappers examples.

7.1. One Route geometry

The following exercises only cover shortest path queries with a single route result.

  • For this chapter, all the examples will return a human readable geometry for analysis, except Exercise 12.
  • The chapter uses som PostGIS functions. PostGIS documentation

Exercise 11 - Route geometry (human readable)

  • The vehicle is going from vertex 13224 to vertex 6549.
  • The vehicle’s cost in this case will be in seconds.
  • Include the geometry of the path in human readable form.
SELECT a.*, ST_AsText(b.the_geom) FROM pgr_dijkstra('
    SELECT gid AS id,
         source,
         target,
         cost_s AS cost,
         reverse_cost_s as reverse_cost
        FROM ways',
    13224, 6549) AS a
 LEFT JOIN ways as b
 ON (a.edge = b.gid) ORDER BY seq;

Solution to Exercise 11

Note

The last record of the query doesn’t contain a geometry value since the shortest path function returns -1 for the last record to indicate the end of the route.

Exercise 12 - Route geometry (binary format)

  • The vehicle is going from vertex 13224 to vertex 6549.
  • The vehicle’s cost in this case will be in seconds.
  • Include the geometry of the path in default binary format.
SELECT a.*, b.the_geom FROM pgr_dijkstra('
    SELECT gid AS id,
         source,
         target,
         cost_s AS cost,
         reverse_cost_s as reverse_cost
        FROM ways',
    13224, 6549) AS a
 LEFT JOIN ways as b
 ON (edge = gid) ORDER BY seq;

Solution to Exercise 12

Exercise 13 - Route geometry for arrows

  • The vehicle is going from vertex 13224 to vertex 6549.
  • The vehicle’s cost in this case will be in seconds.
  • Include the geometry of the path in human readable form.
  • The first point of the segment must “match” with the last point of the previous segment.

Tip

WITH provides a way to write auxiliary statements for use in larger queries. It can be thought of as defining temporary tables that exist just for one query.

WITH
dijkstra AS (
    SELECT * FROM pgr_dijkstra('
        SELECT gid AS id,
        source,
        target,
        cost_s AS cost,
        reverse_cost_s as reverse_cost
        FROM ways',
        13224, 6549)
)
SELECT dijkstra.*,
    CASE
        WHEN dijkstra.node = ways.source THEN ST_AsText(the_geom)
        ELSE ST_AsText(ST_Reverse(the_geom))
    END AS route_geom
    FROM dijkstra JOIN ways
    ON (edge = gid) ORDER BY seq;

Solution to Exercise 13

Note

-- from Exercise 11
LINESTRING(7.1234212 50.7172365,7.1220583 50.7183785)
LINESTRING(7.1250564 50.7179702,7.1244554 50.7176698,7.1235463 50.7172858,7.1234212 50.7172365)

-- from Excercise 13
LINESTRING(7.1220583 50.7183785,7.1234212 50.7172365)
LINESTRING(7.1234212 50.7172365,7.1235463 50.7172858,7.1244554 50.7176698,7.1250564 50.7179702)
  • In Exercise 11 The first point of the second segment does not match the last point of the first segment
  • In Exercise 13 The first point of the second segment matches the last point of the first segment

Exercise 14 - Route using “osm_id”

  • The vehicle is going from vertex 33180347 to vertex 253908904.
  • Start and end vertex are given with their osm_id.
  • The result should contain:
    • seq for ordering and unique row identifier
    • the name of the road segments
    • the geometry of the road segments
    • the cost in seconds (travel time)
WITH
dijkstra AS (
    SELECT * FROM pgr_dijkstra('
        SELECT gid AS id,
        source,
        target,
        cost_s AS cost,
        reverse_cost_s as reverse_cost
        FROM ways',
        -- source
        (SELECT id FROM ways_vertices_pgr WHERE osm_id = 33180347),
        -- target
        (SELECT id FROM ways_vertices_pgr WHERE osm_id = 253908904))
)
SELECT dijkstra.seq, dijkstra.cost, ways.name,
    CASE
        WHEN dijkstra.node = ways.source THEN ST_AsText(the_geom)
        ELSE ST_AsText(ST_Reverse(the_geom))
    END AS route_geom
FROM dijkstra JOIN ways
ON (edge = gid) ORDER BY seq;

Solution to Exercise 14

7.2. Wrapping with views

There can be different levels of wrapping with a view:

  • Create a view of selected edges (that will be used for routing)
  • Create a view of a pgRouting query

Exercise 15 - Road network within an area

  • The vehicle is not allowed to operate outside a bounding box: (7.11606541142, 50.7011037738), (7.14589528858, 50.7210993262)
  • Create a view of the network area (bounding box).
  • Verify the reduced number of road segments
CREATE VIEW my_area AS
    SELECT gid AS id,
    source,
    target,
    cost_s AS cost,
    reverse_cost_s AS reverse_cost
    FROM ways
    WHERE ways.the_geom && ST_MakeEnvelope(7.11606541142, 50.7011037738, 7.14589528858, 50.7210993262, 4326);

SELECT count(*) FROM ways;
SELECT count(*) FROM my_area;

Solution to Exercise 15

Exercise 16 - Route using “osm_id” within an area

  • Use my_area for the network selection.
  • The vehicle wants to go from vertex 33180347 to vertex 253908904.
  • Start and end vertex are given with their osm_id.
  • The result should contain:
    • seq for ordering and unique row identifier
    • the name of the road segments
    • the geometry of the road segments
    • the cost in seconds (travel time)
WITH
dijkstra AS (
    SELECT * FROM pgr_dijkstra(
        'SELECT * FROM my_area',
        -- source
        (SELECT id FROM ways_vertices_pgr WHERE osm_id = 33180347),
        -- target
        (SELECT id FROM ways_vertices_pgr WHERE osm_id = 253908904))
)
SELECT dijkstra.seq, dijkstra.cost, ways.name,
    CASE
        WHEN dijkstra.node = ways.source THEN ST_AsText(the_geom)
        ELSE ST_AsText(ST_Reverse(the_geom))
    END AS route_geom
FROM dijkstra JOIN ways
ON (edge = gid) ORDER BY seq;

Solution to Exercise 16

7.3. Wrapping with functions

The following function simplifies (and sets default values) when it calls the shortest path Dijkstra function.

Tip

pgRouting uses heavely function overloading:

  • Avoid the name of a function installed with pgRouting
  • Avoid the name of a function starting with pgr_ & ST_

Exercise 17 - Function for an application

  • Need to make many similar queries.
  • Should work for any given area.
  • Data tables:
    • the edges are found in ways
    • the vertices are found in ways_vertices_pgr
  • Allow the table/view as a parameter
  • Start and end vertex are given with their osm_id.
  • The result should contain:
    • seq for ordering and unique row identifier
    • the name of the road segments
    • the geometry of the road segments
    • the cost in seconds (travel time)

--DROP FUNCTION my_dijkstra(regclass, bigint, bigint);

CREATE OR REPLACE FUNCTION my_dijkstra(
        IN edges_subset regclass,
        IN source BIGINT,
        IN target BIGINT,
        OUT seq INTEGER,
        OUT cost FLOAT,
        OUT name TEXT,
        OUT geom geometry
    )
    RETURNS SETOF record AS
$BODY$
    WITH
    dijkstra AS (
        SELECT * FROM pgr_dijkstra(
            'SELECT * FROM ' || $1,
            -- source
            (SELECT id FROM ways_vertices_pgr WHERE osm_id = $2),
            -- target
            (SELECT id FROM ways_vertices_pgr WHERE osm_id = $3))
    )
    SELECT dijkstra.seq, dijkstra.cost, ways.name,
    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;
$BODY$
LANGUAGE 'sql';

SELECT seq, cost, name, ST_AsText(geom) FROM my_dijkstra('my_area', 33180347, 253908904);

Solution to Exercise 17

Exercise 18 - Function for an application with heading

  • Same conditions as in the previous exercise apply.
  • Additionally provide information for orientation (heading).
--DROP FUNCTION my_dijkstra_heading(regclass, bigint, bigint);

CREATE OR REPLACE FUNCTION my_dijkstra_heading(
        IN edges_subset regclass,
        IN source BIGINT,
        IN target BIGINT,
        OUT seq INTEGER,
        OUT cost FLOAT,
        OUT name TEXT,
        OUT geom geometry,
        OUT heading FLOAT
    )
    RETURNS SETOF record AS
$BODY$
    WITH
    dijkstra AS (
        SELECT * FROM pgr_dijkstra(
            'SELECT * FROM ' || $1,
            -- source
            (SELECT id FROM ways_vertices_pgr WHERE osm_id = $2),
            -- target
            (SELECT id FROM ways_vertices_pgr WHERE osm_id = $3))
    ),
    with_geom AS (
        SELECT dijkstra.seq, dijkstra.cost, ways.name,
            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 *,
        ST_azimuth(ST_StartPoint(route_geom), ST_EndPoint(route_geom))
    FROM with_geom;
$BODY$
LANGUAGE 'sql';


SELECT seq, cost, name, heading, ST_AsText(geom) FROM my_dijkstra_heading('my_area', 33180347, 253908904);

Solution to Exercise 18