# 8. Writing a pl/pgsql Stored Procedures¶

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 an 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 a 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)
```