8. Writing a pl/pgsql Stored Procedures

../_images/route.png

Other kind of functions are pl/pgsql. As applications requirements become more complex, using previously defined functions becomes necessary.

8.1. Requirements for Routing from A to B

The following function takes latitude/longitude 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

column Description
seq For ordering purposes
gid The edge identifier that can be used to JOIN the results to the ways table
name The street name
azimuth between start and end node of a and edge
length In kilometers
costs Costs in minutes
route_geom The road geometry with corrected directionality.

8.2. The Vertex Table

Graphs have a set of edges and set of vertices associated to it. osm2pgrouting provides the ways_vertices_pgr table which is associated with the ways table. When a subset of edges is used like in vehicle_net or in small_net, the set of vertices associated to each one must be used in order to, for example, locate the nearest vertex to a lat/lon location.

8.2.1. Exercise 1: Number of Vertices

Calculate the number of vertices in a graph

  • Get the set of vertices of:
    • ways
    • vehicle_net
    • little_net
  • Use them to calculate the number of vertices
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
-- Number of vertices in the original graph
SELECT count(*) FROM ways_vertices_pgr;

-- Number of vertices in the vehicles_net graph
SELECT count(*) FROM ways_vertices_pgr
WHERE id IN (
    SELECT source FROM vehicle_net
    UNION
    SELECT target FROM vehicle_net);

-- Number of vertices in the little_net graph
SELECT count(*) FROM ways_vertices_pgr
WHERE id IN (
    SELECT source FROM little_net
    UNION
    SELECT target FROM little_net);

Solution to Chapter 8 Exercise 1

8.2.2. Exercise 2: Nearest Vertex

Calculate the osm_id of the nearest vertex to 39.291852, -6.811437.

  • Get the set of vertices of:
    • ways
    • vehicle_net
    • little_net
  • Use them to calculate the nearest vertex to 39.291852, -6.811437.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Closest osm_id in the original graph
SELECT osm_id FROM ways_vertices_pgr
    ORDER BY the_geom <-> ST_SetSRID(ST_Point(39.291852, -6.811437), 4326) LIMIT 1;

-- Closest osm_id in the vehicle_net graph
WITH
vertices AS (
    SELECT * FROM ways_vertices_pgr
    WHERE id IN (
        SELECT source FROM vehicle_net
        UNION
        SELECT target FROM vehicle_net)
)
SELECT osm_id FROM vertices
    ORDER BY the_geom <-> ST_SetSRID(ST_Point(39.291852, -6.811437), 4326) LIMIT 1;

-- Closest osm_id in the little_net graph
WITH
vertices AS (
    SELECT * FROM ways_vertices_pgr
    WHERE id IN (
        SELECT source FROM little_net
        UNION
        SELECT target FROM little_net)
)
SELECT osm_id FROM vertices
    ORDER BY the_geom <-> ST_SetSRID(ST_Point(39.291852, -6.811437), 4326) LIMIT 1;

Solution to Chapter 8 Exercise 2

8.3. wrk_fromAtoB function

Incorporating all the requirements into the function wrk_fromAtoB. Additionally, it will show the query that is being executed, with the NOTICE statement.

8.3.1. Exercise 3: Creating the function

Create the function wrk_fromAtoB .

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
-- DROP FUNCTION wrk_fromAtoB(varchar, numeric, numeric, numeric, numeric);

CREATE OR REPLACE FUNCTION wrk_fromAtoB(
    IN edges_subset regclass,
    IN x1 numeric, IN y1 numeric,
    IN x2 numeric, IN y2 numeric,
    OUT seq INTEGER,
    OUT gid BIGINT,
    OUT name TEXT,
    OUT length FLOAT,
    OUT the_time FLOAT,
    OUT azimuth FLOAT,
    OUT geom geometry
)
RETURNS SETOF record AS
$BODY$
DECLARE
    final_query TEXT;
BEGIN

    final_query :=
        FORMAT( $$
            WITH
            vertices AS (
                SELECT * FROM ways_vertices_pgr
                WHERE id IN (
                    SELECT source FROM %1$I
                    UNION
                    SELECT target FROM %1$I)
            ),
            dijkstra AS (
                SELECT *
                FROM wrk_dijkstra(
                    '%1$I',
                    -- source
                    (SELECT osm_id FROM vertices
                        ORDER BY the_geom <-> ST_SetSRID(ST_Point(%2$s, %3$s), 4326) LIMIT 1),
                    -- target
                    (SELECT osm_id FROM vertices
                        ORDER BY the_geom <-> ST_SetSRID(ST_Point(%4$s, %5$s), 4326) LIMIT 1))
            )
            SELECT
                seq,
                dijkstra.gid,
                dijkstra.name,
                ways.length_m/1000.0 AS length,
                dijkstra.cost AS the_time,
                azimuth,
                route_geom AS geom
            FROM dijkstra JOIN ways USING (gid);$$,
        edges_subset, x1,y1,x2,y2); -- %1 to %5 of the FORMAT function
    RAISE notice '%', final_query;
    RETURN QUERY EXECUTE final_query;
END;
$BODY$
LANGUAGE 'plpgsql';

Solution to Chapter 8 Exercise 3

Save the function in the file wrk_fromAtoB

8.3.2. Exercise 4: Using the function

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT *  FROM wrk_fromAtoB(
    'vehicle_net',
    39.291852, -6.811437,
    39.287737, -6.811389);

SELECT *  FROM wrk_fromAtoB(
    'little_net',
    39.291852, -6.811437,
    39.287737, -6.811389);

-- saving results in a table
SELECT * INTO example
FROM wrk_fromAtoB(
    'ways',
    39.291852, -6.811437,
    39.287737, -6.811389);

Solution to Chapter 8 Exercise 4

Note

A Notice will show while executing the function, for example:

WITH
vertices AS (
    SELECT * FROM ways_vertices_pgr
    WHERE id IN (
        SELECT source FROM vehicle_net
        UNION
        SELECT target FROM vehicle_net)
),
dijkstra AS (
    SELECT *
    FROM wrk_dijkstra(
        'vehicle_net',
        -- source
        (SELECT osm_id FROM vertices
            ORDER BY the_geom <-> ST_SetSRID(ST_Point(39.291852, -6.811437), 4326) LIMIT 1),
        -- target
        (SELECT osm_id FROM vertices
            ORDER BY the_geom <-> ST_SetSRID(ST_Point(39.287737, -6.811389), 4326) LIMIT 1))
)
SELECT
    seq,
    dijkstra.gid,
    dijkstra.name,
    ways.length_m/1000.0 AS length,
    dijkstra.cost AS the_time,
    azimuth,
    route_geom AS geom
FROM dijkstra JOIN ways USING (gid)