# 7. Writing a SQL Stored Procedures¶

pgRouting functions provide low level interface. When developing for a higher level application, the requirements need to be represented in the SQL queries. As these SQL queries get more complex, it is desirable to store them in postgreSQL stored procedures or functions. Stored procedures are an effective way to wrap application logic, in this case, related to routing logic.

## 7.1. The application requirements¶

The stored procedure that is going to be developed has the following requirements:

1. Vehicles are routed.
2. Starting and ending vertices are by selection using osm_id.
• In past chapters it was done using the id of the vertices.

3. Name of the road on the path.

4. The geometry segments along the route path with the correct orientation.
• Geometry is to be returned.

• Azimuth in degrees of the geometry is to be returned.

• Geometry handling is needed to get the correct orientation.

### 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 Bounding Box: `(26.08, 44.42, 26.11, 44.44)`

• The vehicle can only circulate inside the bounding box: `(26.08, 44.42, 26.11, 44.44)`

• 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(26.08, 44.42, 26.11, 44.44); -- 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 `6498351588`.

• The vehicle is going to the hotel at `255093299`.

• 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 = 6498351588), -- target (SELECT id FROM ways_vertices_pgr WHERE osm_id = 255093299)); ```

Solution to Chapter 7 Exercise 3

### 7.1.4. Exercise 4 - Get additional information¶

From the venue at National Theater Bucharest to the Hotel Capitol, additionally get the name of the roads.

• The vehicle is going from the venue at National Theater Bucharest at `6498351588`.

• The vehicle is going to the Hotel Capitol at `255093299`.

• 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 = 6498351588), (SELECT id FROM ways_vertices_pgr WHERE osm_id = 255093299) ) AS dijkstra LEFT JOIN ways ON (edge = gid) ORDER BY seq; ```

Solution to Chapter 7 Exercise 4

## 7.2. Geometry handling¶

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

From the venue at National Theater Bucharest to the Hotel Capitol, additionally get the geometry in human readable form.

• The vehicle is going from the venue at National Theater Bucharest at `6498351588`

• The vehicle is going to the Hotel Capitol at `255093299`.

• 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 = 6498351588), (SELECT id FROM ways_vertices_pgr WHERE osm_id = 255093299) ) AS dijkstra LEFT JOIN ways ON (edge = gid) ORDER BY seq; ```

Solution to Chapter 7 Exercise 5

Note

The last row of the result, does not contain a geometry value since the shortest path function returns `-1` for the last edge to indicate the end of the route.

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

From the venue at National Theater Bucharest to the Hotel Capitol by car, get the binary format geometry that can be used by a front end app.

Note

Not using `ST_AsText` gives the binary format.

Tip

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

• The vehicle is going from the venue at National Theater Bucharest at `6498351588`.

• The vehicle is going to the Hotel Capitol at `255093299`.

• 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 = 6498351588), (SELECT id FROM ways_vertices_pgr WHERE osm_id = 255093299)) ) 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 at National Theater Bucharest to the Hotel Capitol, calculate the azimuth in degrees.

• The vehicle is going from the venue at National Theater Bucharest at `6498351588`.

• The vehicle is going to the Hotel Capitol at `255093299`.

• Get the `seq`, `name`, `cost`, `azimuth` in degrees and the `geometry`.

• 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 = 6498351588), (SELECT id FROM ways_vertices_pgr WHERE osm_id = 255093299)) ), 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 at National Theater Bucharest, going to the Hotel Capitol by car, get the geometry with correct arrow directionality.

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

• The vehicle is going from the venue at National Theater Bucharest at `6498351588`.

• The vehicle is going to the Hotel Capitol at `255093299`.

• 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 = 6498351588), (SELECT id FROM ways_vertices_pgr WHERE osm_id = 255093299)) ), 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

Note

Comparing row 10 & 11 from Solution to Chapter 7 Exercise 5

```-- from Exercise 5
LINESTRING(26.1007594 44.4390039,26.1006676 44.4391489)
LINESTRING(26.1004837 44.4391168,26.1006676 44.4391489)

-- from Excercise 8
LINESTRING(26.1007594 44.4390039,26.1006676 44.4391489)
LINESTRING(26.1006676 44.4391489,26.1004837 44.4391168)
```
• In Exercise 5 the first point of row 11 does not match the last point of row 10

• In Exercise 8 the first point of row 11 matches the last point of row 10

## 7.3. Creating a Function¶

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

Tip

• Avoid the name of a function installed with pgRouting

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

### 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 `geometry`.

• 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', 6498351588, 255093299); ```

Solution to Chapter 7 Exercise 10

Note

Try the function with `little_net` and a combination of the interesting places:

• 255093299 Hotel Capitol

• 6159253045 Little Bucharest Hostal

• 6498351588 venue at National Theater Bucharest

• 123392877 workshops at Faculty of Geography of the University of Bucharest

• 1886700005 Parliament House

### 7.3.3. Exercise 11 - Saving the function¶

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

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

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