# 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 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 %1I UNION SELECT target FROM %1I) ), dijkstra AS ( SELECT * FROM wrk_dijkstra( '%1I', -- source (SELECT osm_id FROM vertices ORDER BY the_geom <-> ST_SetSRID(ST_Point(%2s, %3s), 4326) LIMIT 1), -- target (SELECT osm_id FROM vertices ORDER BY the_geom <-> ST_SetSRID(ST_Point(%4s, %5s), 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)