4. SQL function

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 or functions are an effective way to wrap application logic, in this case, related to routing logic and requirements.

4.1. The application requirements

In this chapter there are three requirements that follow the same logic. It consists on 2 types of vehicles and the pedestrian routing:

• Particular vehicle:

  • Circulate on the whole Firenze area. - Do not use steps, footway, path.

  • Speed is the default speed from OSM information.

• Taxi vehicle:

  • Circulate on a smaller area near “Hotel Albani”.

    • Bounding box: (11.24,43.77,11.27,43.79)

    • Do not use steps, footway, path

  • Speed is 10% faster than the Particular vehicles.

• Pedestrians:

  • Walk on the whole Firenze area.

  • Can not circulate on motorways and on primary segments.

  • The speed is 2 mts/sec.

A front end needs the following routing information:

• seq - A unique identifier of the rows

• gid - The segment’s identifier

• name - The segment’s name

• length - The segment’s length

• seconds - Number of seconds to traverse the segment

• azimuth - The azimuth of the segment

• route_geom - The routing geometry

• route_readable - The geometry in human readable form.

and it needs to work based on the graph, and the OSM identifiers of the vertices.

Design of the function

The function to be created wrk_dijkstra with the following input parameters and output columns:

Input parameters

Name	Type	Description
edges_subset	REGCLASS	The table/view that is going to be used for processing
source_osm	BIGINT	The OSM identifier of the departure location.
target_osm	BIGINT	The OSM identifier of the destination location.

output columns

Name	Type	Description
seq	INTEGER	A unique number for each result row.
id	BIGINT	The edge identifier.
name	TEXT	The name of the segment.
seconds	FLOAT	The number of seconds it takes to traverse the segment.
azimuth	FLOAT	The azimuth of the segment.
length_m	FLOAT	The leng in meters of the segment.
route_readable	TEXT	The geometry in human readable form.
route_geom	geometry	The geometry of the segment in the correct direction.

4.2. Preparing processing graphs

4.2.1. Exercise 1: Creating a view for routing

Problem

• Create a view with minimal amount of information for processing the particular vehicles.

• Routing cost and reverse_cost will be on seconds for routing calculations.

• Exclude steps, footway, path segments.

• Data needed in the view for further prossesing.

  • length_m The length in meters.

  • the_geom The geometry.

• Verify the number of edges was reduced.

Solution

• Creating the view:

  • The source and target requirements for the function are to be with OSM identifiers. (line 6)

  • The cost and reverse_cost are in terms of seconds. (line 7)

  • The additional parameters length_m and the_geom. (line 8)

  • JOIN with the configuration:

    • Exclude steps, footway, path. (line 11)

  • If you need to reconstruct the view, first drop it using the command on line 1.

  -- DROP VIEW vehicle_net CASCADE;
  
  CREATE VIEW vehicle_net AS
    SELECT
      gid AS id,
      source_osm AS source, target_osm AS target,
      cost_s AS cost, reverse_cost_s AS reverse_cost,
      name, length_m, the_geom
    FROM ways JOIN configuration AS c
    USING (tag_id)
    WHERE  c.tag_value NOT IN ('steps','footway','path');
  

• Verification:

  • Count the rows on the original ways (line 1)

  • Count the rows on the view vehicle_net (line 2)

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

Exercise: 1 (Chapter: SQL)

4.2.2. Exercise 2: Limiting the road network within an area

Problem

• Create a view taxi_net for the taxi:

  • The taxi can only circulate inside this Bounding Box: (11.24,43.77,11.27,43.79)

  • The taxi speed is 10% faster than the particular vehicle.

• Verify the reduced number of road segments.

Solution

• Creating the view:

  • The graph for the taxi is a subset of the vehicle_net graph. (line 9)

  • Can only circulate inside the bounding box: (11.24,43.77,11.27,43.79). (line 10)

  • Adjust the taxi’s cost and reverse_cost to be 90% of the particular vehicle. (line 7)

  -- DROP VIEW taxi_net;
  
  CREATE VIEW taxi_net AS
      SELECT
        id,
        source, target,
        cost * 0.90 AS cost, reverse_cost * 0.90 AS reverse_cost,
        name, length_m, the_geom
      FROM vehicle_net
      WHERE vehicle_net.the_geom && ST_MakeEnvelope(11.24,43.77,11.27,43.79);
  

• Verification:

  • Count the rows on the original taxi_net

  SELECT count(*) FROM taxi_net;
  

Exercise: 2 (Chapter: SQL)

4.2.3. Exercise 3: Creating a materialized view for routing pedestrians

Problem

• Create a materialized view with minimal amount of information for processing pedestrians.

• Routing cost and reverse_cost will be on seconds for routing calculations.

  • The speed is 2 mts/sec.

• Exclude motorway and primary segments.

• Data needed in the view for further prossesing.

  • length_m The length in meters.

  • the_geom The geometry.

• Verify the number of edges was reduced.

Solution

• Creating the view:

  • Similar to Exercise 1: Creating a view for routing:

    • The cost and reverse_cost are in terms of seconds with speed of 2 mts/sec. (line 7)

    • Exclude motorway, primary. (line 11)

  -- DROP MATERIALIZED VIEW walk_net;
  
  CREATE MATERIALIZED VIEW walk_net AS
    SELECT
      gid AS id,
      source_osm AS source, target_osm AS target,
      length_m / 2 AS cost, length_m / 2 AS reverse_cost,
      name, length_m, the_geom
    FROM ways JOIN configuration AS c
    USING (tag_id)
    WHERE  c.tag_value NOT IN ('motorway','primary','unclassified');
  

• Verification:

  • Count the rows on the view walk_net (line 1)

  SELECT count(*) FROM walk_net;
  

Exercise: 3 (Chapter: SQL)

4.2.4. Exercise 4: Testing the views for routing

Problem

• Test the created views

In particular:

• From the “Palazzo dei Congressi” to the “Mercato Centrale” using the OSM identifier

• the views to be tested are:

  • vehicle_net

  • taxi_net

  • walk_net

• Only show the following results, as the other columns are to be ignored on the function.

  • seq

  • edge with the name id

  • cost with the name: seconds

Solution

• In general

  • The departure is “Palazzo dei Congressi” with OSM identifier 5020458299.

  • The destination is “Mercato Centrale” with OSM identifier 266939565.

• For vehicle_net:

  • vehicle_net is used.

  • Selection of the columns with the corresponding names are on line 1.

  • The view is prepared with the column names that pgRouting use.

    • There is no need to rename columns. (line 3)

  • The OSM identifiers of the departure and destination are used. (line 4)

  SELECT seq, edge AS id, cost AS seconds
  FROM pgr_dijkstra(
      'SELECT * FROM vehicle_net',
      5020458299, 266939565);
  

• For taxi_net:

  • Similar as the previous one but with taxi_net. (line 3)

  • The results give the same route as with vehicle_net but cost is lower

  SELECT seq, edge AS id, cost AS seconds
  FROM pgr_dijkstra(
      'SELECT * FROM taxi_net',
      5020458299, 266939565);
  

• For walk_net:

  • Similar as the previous one but with walk_net. (line 3)

  • The results give a different route than of the vehicles.

  SELECT seq, edge AS id, cost AS seconds
  FROM pgr_dijkstra(
      'SELECT * FROM walk_net',
      5020458299, 266939565);
  

Note

From these queries, it can be deduced that what we design for one view will work for the other views. On the following exercises only vehicle_net will be used, but you can test the queries with the other views.

Exercise: 4 (Chapter: SQL)

4.2.5. Exercise 5: Get additional information

Problem

• From the Palazzo dei Congressi to the Mercato Centrale, using OSM identifiers.

• additionally to the Exercise 4: Testing the views for routing results also get information found on the edges subset:

  • name

  • length_m

Solution

• The query from Exercise 4: Testing the views for routing used as a subquery named results (not highlighted lines 5 to 9)

• The SELECT clause contains

  • All the columns of results. (line 2)

  • The name and the length_m values. (line 3)

• A LEFT JOIN with vehicle_net is needed to get the additional information. (line 10)

  • Has to be LEFT because there is a row with id = -1 that does not exist on vehicle_net

SELECT
  results.*,
  name, length_m
FROM (
  SELECT seq, edge AS id, cost AS seconds
  FROM pgr_dijkstra(
      'SELECT * FROM vehicle_net',
      5020458299, 266939565)
  ) AS results
LEFT JOIN vehicle_net
  USING (id)
ORDER BY seq;

Exercise: 5 (Chapter: SQL)

4.3. Geometry handling

4.3.1. Exercise 6: Route geometry (human readable)

Problem

• From the “Palazzo dei Congressi” to the “Mercato Centrale”, additionally get the geometry in human readable form.

  • Additionally to the Exercise 4: Testing the views for routing results also get information found on the edges subset of:

    • the_geom in human readable form named as route_readable

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.

Solution

• The query from Exercise 4: Testing the views for routing used as a subquery named results this time in a WITH clause. (not highlighted lines 2 to 6)

• The SELECT clause contains:

  • All the columns of results. (line 8)

  • The the_geom processed with ST_AsText to get the human readable form. (line 9)

    • Renames the result to route_readable

• Like before LEFT JOIN with vehicle_net. (line 11)

WITH results AS (
  SELECT seq, edge AS id, cost AS seconds
  FROM pgr_dijkstra(
      'SELECT * FROM vehicle_net',
      5020458299, 266939565)
  )
SELECT
  results.*,
  ST_AsText(the_geom) AS route_readable
FROM results
LEFT JOIN vehicle_net
  USING (id)
ORDER BY seq;

Exercise: 6 (Chapter: SQL)

4.3.2. Exercise 7: Route geometry (binary format)

Problem

• From the “Palazzo dei Congressi” to the “Mercato Centrale”, the geometry in binary format.

  • Additionally to the Exercise 4: Testing the views for routing results also get information found on the edges subset of:

    • the_geom in binary format with the name route_geom

Solution

• The query from Exercise 6: Route geometry (human readable) used;

• The SELECT clause contains:

  • The the_geom including the renaming (line 9)

WITH results AS (
  SELECT seq, edge AS id, cost AS seconds
  FROM pgr_dijkstra(
      'SELECT * FROM vehicle_net',
      5020458299, 266939565)
  )
SELECT
  results.*,
  the_geom AS route_geom
FROM results
LEFT JOIN vehicle_net
  USING (id)
ORDER BY seq;

Exercise: 7 (Chapter: SQL)

4.3.3. Exercise 8: Route geometry directionality

Inspecting the detail image of Exercise 7: Route geometry (binary format) there are arrows that do not match the directionality of the route.

Inspecting the a detail of the results of Exercise 6: Route geometry (human readable)

• To have correct directionality, the ending point of a geometry must match the starting point of the next geometry

• Lines 2 and 3 do not match that criteria

   2 | 12857 |   1.2075070194870985 | LINESTRING(11.2492507 43.7777478,11.2490531 43.7777)
   3 | 13203 |  0.20005398086256934 | LINESTRING(11.2490531 43.7777,11.2490204 43.777692)
   4 | 26751 |  0.08031320045680243 | LINESTRING(11.2490204 43.777692,11.2490066 43.7776911)

Problem

• From the “Palazzo dei Congressi” to the “Mercato Centrale”,

  • Additionally to the Exercise 4: Testing the views for routing results also get information found on the edges subset of:

    • the_geom in human readable form named as route_readable

    • the_geom in binary format with the name route_geom

    • Both columns must have the geometry fixed for directionality.

Solution

• To get the correct direction some geometries need to be reversed:

  • Reversing a geometry will depend on the node column of the query to dijkstra (line 3)

    • That node is not needed on the ouput of the query, so explicitly naming required columns at line 9.

  • A conditional CASE statement that returns the geometry in human readable form:

    • Of the geometry when node is the source column. (line 11)

    • Of the reversed geometry when node is not the source column. (line 12)

  • A conditional CASE statement that returns:

    • The reversed geometry when node is not the source column. (line 16)

    • The geometry when node is the source column. (line 17)

WITH results AS (
  SELECT seq, edge AS id, cost AS seconds,
    node
  FROM pgr_dijkstra(
      'SELECT * FROM vehicle_net',
      5020458299, 266939565)
  )
SELECT
  seq, id, seconds,
  CASE
      WHEN node = source THEN ST_AsText(the_geom)
      ELSE ST_AsText(ST_Reverse(the_geom))
  END AS route_readable,

  CASE
      WHEN node = source THEN the_geom
      ELSE ST_Reverse(the_geom)
  END AS route_geom

FROM results
LEFT JOIN vehicle_net USING (id)
ORDER BY seq;

Exercise: 8 (Chapter: SQL)

4.3.4. Exercise 9: Using the geometry

There are many geometry functions in PostGIS, the workshop already covered some of them like ST_AsText, ST_Reverse, ST_EndPoint, etc. This exercise will make use an additional function ST_Azimuth.

Problem

• Modify the query from Exercise 8: Route geometry directionality.

• Aditionally obtain the azimuth of the correct geometry.

• keep the output small:

  • Even that other columns are calculated only output:

    • seq, id, seconds and the azimuth

• Because vehicle_net is a subgraph of ways, do the JOIN with ways.

Solution

• Moving the query that gets the additional information into the WITH statement.

  • Naming it additional. (line 9)

• Final SELECT statements gets:

  • The requested information. (line 25)

  • Calculates the azimuth of route_geom. (line 26)

WITH
results AS (
  SELECT seq, edge AS id, cost AS seconds,
    node
  FROM pgr_dijkstra(
      'SELECT * FROM vehicle_net',
      5020458299, 266939565)
  ),
additional AS (
  SELECT
    seq, id, seconds,
    CASE
        WHEN node = source THEN ST_AsText(the_geom)
        ELSE ST_AsText(ST_Reverse(the_geom))
    END AS route_readable,

    CASE
        WHEN node = source THEN the_geom
        ELSE ST_Reverse(the_geom)
    END AS route_geom

  FROM results
  LEFT JOIN ways ON (gid = id)
)
SELECT seq, id, seconds,
  degrees(ST_azimuth(ST_StartPoint(route_geom), ST_EndPoint(route_geom))) AS azimuth
FROM additional
ORDER BY seq;

Exercise: 9 (Chapter: SQL)

4.4. Creating the Function

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

Warning

pgRouting uses heavely function overloading:

• Avoid creating functions with a name of a pgRouting routing function

• Avoid the name of a function to start with pgr_, _pgr or ST_

4.4.1. Exercise 10: Function for an application

Problem

Putting all together in a SQL function

• function name wrk_dijkstra

• Should work for any given view.

  • Allow a view as a parameter

    • A table can be used if the columns have the correct names.

• source and target are in terms of osm_id.

• The result should meet the requirements indicated at the begining of the chapter

Solution

• The signature of the function:

  • The input parameters are from line 4 to 6.

  • The output columns are from line 7 to 14 (not highlited).

  • The function returns a set. (line 16)

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

CREATE OR REPLACE FUNCTION wrk_dijkstra(
        IN edges_subset REGCLASS,
        IN source BIGINT,  -- in terms of osm_id
        IN target BIGINT,  -- in terms of osm_id
        OUT seq INTEGER,
        OUT id BIGINT,
        OUT seconds FLOAT,
        OUT name TEXT,
        OUT length_m FLOAT,
        OUT route_readable TEXT,
        OUT route_geom geometry,
        OUT azimuth FLOAT
    )
    RETURNS SETOF record AS

• The body of the function:

  • Appending the view name on line 7 in the SELECT query to pgr_dijkstra.

  • Using the data to get the route from source to target. (line 8)

  • The JOIN with ways is necessary, as the views are subset of ways (line 25)

$BODY$
  WITH
  results AS (
    SELECT seq, edge AS id, cost AS seconds,
      node
    FROM pgr_dijkstra(
        'SELECT * FROM ' || edges_subset,
        source, target)
    ),
  additional AS (
    SELECT
      seq, id, seconds,
      name, length_m,
      CASE
          WHEN node = source THEN ST_AsText(the_geom)
          ELSE ST_AsText(ST_Reverse(the_geom))
      END AS route_readable,

      CASE
          WHEN node = source THEN the_geom
          ELSE ST_Reverse(the_geom)
      END AS route_geom

    FROM results
    LEFT JOIN ways ON (gid = id)
  )
  SELECT *,
    degrees(ST_azimuth(ST_StartPoint(route_geom), ST_EndPoint(route_geom))) AS azimuth
  FROM additional
  ORDER BY seq;
$BODY$
LANGUAGE 'sql';

Exercise: 10 (Chapter: SQL)

4.4.2. Exercise 11: Using the function

Problem

• Test the function with the three views

• From the “Palazzo dei Congressi” to the Mercato Centrale using the OSM identifier

Solution

• Use the function on the SELECT statement

• The first parameter changes based on the view to be tested

SELECT *
FROM wrk_dijkstra('vehicle_net',  5020458299, 266939565);

SELECT *
FROM wrk_dijkstra('taxi_net',  5020458299, 266939565);

SELECT *
FROM wrk_dijkstra('walk_net',  5020458299, 266939565);

Exercise: 11 (Chapter: SQL)

Use the function

• Try the function with a combination of the interesting places:

  • 266939565 Mercato Centrale

  • 2531656518 Stazione di Santa Maria Novella

  • 5020458299 Palazzo dei Congressi

  • 6483155124 Hotel Albani

  • 1798374718 Cattedrale di Santa Maria del Fiore