8. Routing from A to B¶

The following function takes lat/lon points as input parameters and returns a route that can be displayed in QGIS or WMS services such as Mapserver and Geoserver:

Input parameters

Table name
x1, y1 for start point and x2, y2 for end point

Output columns

Sequence (for example to order the results afterwards)
Gid (for example to link the result back to the original table)
Street name
Heading in degree (simplified as it calculates the Azimuth between start and end node of a link)
Costs as length in kilometer
The road link geometry

What the function does internally:

Finds the nearest nodes to start and end point coordinates
Runs shortest path Dijkstra query
Flips the geometry if necessary, that target node of the previous road link is the source of the following road link
Calculates the azimuth from start to end node of each road link
Returns the result as a set of records

-- DROP FUNCTION pgr_fromAtoB(varchar, double precision, double precision, double precision, double precision);

CREATE OR REPLACE FUNCTION pgr_fromAtoB(
    IN tbl varchar,
    IN x1 double precision,
    IN y1 double precision,
    IN x2 double precision,
    IN y2 double precision,
    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 gid as id, source, target, length_m AS cost FROM ' || $1,
        -- source
        (SELECT id FROM ways_vertices_pgr
            ORDER BY the_geom <-> ST_SetSRID(ST_Point(x1,y1),4326) LIMIT 1),
        -- target
        (SELECT id FROM ways_vertices_pgr
            ORDER BY the_geom <-> ST_SetSRID(ST_Point(x2,y2),4326) LIMIT 1),
        false) -- undirected
    ),
    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';

What the function does not do:

It does not restrict the selected road network by BBOX (necessary for large networks)
It does not return road classes and several other attributes
It does not take into account one-way streets
There is no error handling

Example query

SELECT seq, cost, name, heading, ST_AsText(geom) FROM pgr_fromAtoB('ways',7.1192,50.7149,7.0979,50.7346);

Solution to Exercise 19

To store the query result as a table run

CREATE TABLE temp_route AS
        SELECT * FROM pgr_fromAtoB('ways',7.1192,50.7149,7.0979,50.7346);
--DROP TABLE temp_route;

Save the function code above into a file ~/Desktop/workshop/fromAtoB.sql. We can then install this function into the database with:

psql -U user -d city_routing -f ~/Desktop/workshop/fromAtoB.sql