7. Writing a SQL Stored Procedures¶

7.1.1. Exercise 1 - Segments for Vehicle Routing¶

The vehicle can not circulate on non pedestrian roads

• Create a view of the allowed road network for circulation.
• Routing costs will be based on minutes.
• Verify the reduced number of road segments

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- DROP VIEW vehicle_net CASCADE;

CREATE VIEW vehicle_net AS
    SELECT gid,
        source,
        target,
        -- converting to minutes
        cost_s / 60 AS cost,
        reverse_cost_s / 60 AS reverse_cost,
        the_geom
    FROM ways JOIN configuration AS c
    USING (tag_id)
    WHERE  c.tag_value NOT IN ('steps','footway','path');

-- Verification
SELECT count(*) FROM ways;
SELECT count(*) FROM vehicle_net;

Solution to Chapter 7 Exercise 1

7.1.2. Exercise 2 - Limiting the Road Network within an Area¶

The vehicle can only circulate inside this Boundig Box: (-71.05 42.34, -71.03 42.35)

• The vehicle can only circulate inside the bounding box:

  (-71.05 42.34, -71.03 42.35)

• Create a view of the allowed road network for circulation.
• Use the vehicle_net VIEW.
• Verify the reduced number of road segments

1
2
3
4
5
6
7
8
9

-- DROP VIEW little_net;

CREATE VIEW little_net AS
    SELECT *
    FROM vehicle_net
    WHERE vehicle_net.the_geom && ST_MakeEnvelope(39.27, -6.79, 39.30, -6.83);

-- Verification
SELECT count(*) FROM little_net;

Solution to Chapter 7 Exercise 2

7.1.3. Exercise 3 - Route using 「osm_id」¶

From the Venue to the hotel using the osm_id.

• The vehicle is going from the Venue at 252643343
• The vehicle is going to the hotel at 302057309.
• Start and end vertex are given with their osm_id.
• The result should contain:
  • seq for ordering and unique row identifier

1
2
3
4
5
6
7

SELECT *
FROM pgr_dijkstra(
    'SELECT gid AS id, * FROM vehicle_net',
    -- source
    (SELECT id FROM ways_vertices_pgr WHERE osm_id = 252643343),
    -- target
    (SELECT id FROM ways_vertices_pgr WHERE osm_id = 302057309));

Solution to Chapter 7 Exercise 3

7.1.4. Exercise 4 - Get additional information¶

From the Venue to the Brewry, additionally get the name of the roads.

• The vehicle is going from the Venue at 252643343
• The vehicle is going to the hotel at 302057309.
• The result should contain:
  • seq for ordering and unique row identifier
  • the name of the road segments

1
2
3
4
5
6
7
8

SELECT dijkstra.*, ways.name
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)
    ) AS dijkstra
LEFT JOIN ways
ON (edge = gid) ORDER BY seq;

Solution to Chapter 7 Exercise 4

7.2.1. Exercise 5 - Route geometry (human readable)¶

From the Venue to the hotel, additionally get the geometry in human readable form.

• The vehicle is going from the Venue at 252643343
• The vehicle is going to the hotel at 302057309.
• The result should contain:
  • seq for ordering and unique row identifier
  • the name of the road segments
  • the geometry of the path in human readable form.

1
2
3
4
5
6
7
8

SELECT dijkstra.*, ways.name, ST_AsText(ways.the_geom)
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)
    ) AS dijkstra
LEFT JOIN ways
ON (edge = gid) ORDER BY seq;

Solution to Chapter 7 Exercise 5

7.2.2. Exercise 6 - Route geometry (binary format)¶

From the Venue to the hotel by car, get the binary format geometry that can be used by a front end app.

• The vehicle is going from the Venue at 252643343
• The vehicle is going to the hotel at 302057309.
• The result should contain:
  • seq for ordering and unique row identifier
  • the name of the road segments
  • the geometry of the path in human readable form.
  • the geometry of the path in default binary format.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

WITH
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))
)
SELECT dijkstra.*, ways.name, ways.the_geom AS route_geom
FROM dijkstra LEFT JOIN ways ON (edge = gid)
ORDER BY seq;

Solution to Chapter 7 Exercise 6

7.2.3. Exercise 7 - Using the geometry¶

From the Venue to the hotel, calculate the azimuth in degrees.

• The vehicle is going from the Venue at 252643343
• The vehicle is going to the hotel at 302057309.
• Get the seq, name, cost, azimuth in degrees and the geomtery
• The geometry of the route path in human readable form & binary form

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

WITH
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, ways.the_geom AS route_geom
    FROM dijkstra JOIN ways ON (edge = gid)
    ORDER BY seq)
SELECT seq, name, cost,
    -- calculating the azimuth
    degrees(ST_azimuth(ST_StartPoint(route_geom), ST_EndPoint(route_geom))) AS azimuth,
    ST_AsText(route_geom),
    route_geom
FROM get_geom
ORDER BY seq;

Solution to Chapter 7 Exercise 7

7.2.4. Exercise 8 - Geometry directionality¶

From the Venue, going to the hotel by car, get the geometry with correct arrow directionality.

When we generate a route the segements are returned as the geometry in the database. that means the segments can be reverserd relative to the direction of the route path. Goal is to have all segments oriented correctly along the route path.

• The vehicle is going from the Venue at 252643343
• The vehicle is going to the hotel at 302057309.
• The first point of the segment must 「match」 with the last point of the previous segment.
• Get the seq, name, cost, azimuth and the geomtery
• The geometry of the route path in human readable form & binary form

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

WITH
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
        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 seq, name, cost,
    degrees(ST_azimuth(ST_StartPoint(route_geom), ST_EndPoint(route_geom))) AS azimuth,
    ST_AsText(route_geom),
    route_geom
FROM get_geom
ORDER BY seq;

Solution to Chapter 7 Exercise 8

-- from Exercise 5
LINESTRING(39.2902655 -6.8114116, ... ,39.2910718 -6.8102817)
LINESTRING(39.2888771 -6.8127504, ... ,39.2902655 -6.8114116)

-- from Excercise 8
LINESTRING(39.2910718 -6.8102817, ... ,39.2902655 -6.8114116)
LINESTRING(39.2902655 -6.8114116, ... ,39.2888771 -6.8127504)

7.3.1. Exercise 9 - Function for an application¶

Putting all together in a SQL function

• Should work for any given area.
• Data tables:
  • the edges are found in ways
  • the vertices are found in ways_vertices_pgr
• Allow a view as a parameter
  • A table can be used if the columns have the correct names
• Start and end vertex are given with their osm_id.
• The result should contain:
  • seq, name, cost, azimuth and the geomtery
  • The geometry of the route path in human readable form & binary form

 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

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

CREATE OR REPLACE FUNCTION wrk_dijkstra(
        IN edges_subset regclass,
        IN source BIGINT,
        IN target BIGINT,
        OUT seq INTEGER,
        OUT gid BIGINT,
        OUT name TEXT,
        OUT cost FLOAT,
        OUT azimuth FLOAT,
        OUT route_readable TEXT,
        OUT route_geom geometry
    )
    RETURNS SETOF record AS
$BODY$
    WITH
    dijkstra AS (
        SELECT * FROM pgr_dijkstra(
            -- using parameters instead of specific values
            'SELECT gid AS id, * FROM ' || $1,
            (SELECT id FROM ways_vertices_pgr WHERE osm_id = $2),
            (SELECT id FROM ways_vertices_pgr WHERE osm_id = $3))
    ),
    get_geom AS (
        SELECT dijkstra.*, 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
        seq,
        edge,  -- will get the name "gid"
        name,
        cost,
        degrees(ST_azimuth(ST_StartPoint(route_geom), ST_EndPoint(route_geom))) AS azimuth,
        ST_AsText(route_geom),
        route_geom
    FROM get_geom
    ORDER BY seq;
$BODY$
LANGUAGE 'sql';

Solution to Chapter 7 Exercise 9

7.3.2. Exercise 10 - Using the function¶

• The osm_id must exist on the ways_vertices_pgr table.
• If an osm_id falls outside the view, No path will be returned.

1
2

SELECT *
FROM wrk_dijkstra('vehicle_net',  252643343, 302057309);

Solution to Chapter 7 Exercise 10

7.3.3. Exercise 11 - Saving the function¶

Save the function code above into a file ~/Desktop/workshop/dijkstraHeading.sql.

Saving functions in a file can be used to install the functionin another database. Install the function into the database with:

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