4. SQL function

../_images/route.png

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

View of roads for vehicles

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.

     1-- DROP VIEW vehicle_net CASCADE;
     2
     3CREATE VIEW vehicle_net AS
     4  SELECT
     5    gid AS id,
     6    source_osm AS source, target_osm AS target,
     7    cost_s AS cost, reverse_cost_s AS reverse_cost,
     8    name, length_m, the_geom
     9  FROM ways JOIN configuration AS c
    10  USING (tag_id)
    11  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)

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

Exercise: 1 (Chapter: SQL)

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

View of smaller set of roads for vehicles

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)

     1-- DROP VIEW taxi_net;
     2
     3CREATE VIEW taxi_net AS
     4    SELECT
     5      id,
     6      source, target,
     7      cost * 0.90 AS cost, reverse_cost * 0.90 AS reverse_cost,
     8      name, length_m, the_geom
     9    FROM vehicle_net
    10    WHERE vehicle_net.the_geom && ST_MakeEnvelope(11.24,43.77,11.27,43.79);
    
  • Verification:

    • Count the rows on the original taxi_net

    1SELECT count(*) FROM taxi_net;
    

Exercise: 2 (Chapter: SQL)

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

View of roads for vehicles

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:

     1-- DROP MATERIALIZED VIEW walk_net;
     2
     3CREATE MATERIALIZED VIEW walk_net AS
     4  SELECT
     5    gid AS id,
     6    source_osm AS source, target_osm AS target,
     7    length_m / 2 AS cost, length_m / 2 AS reverse_cost,
     8    name, length_m, the_geom
     9  FROM ways JOIN configuration AS c
    10  USING (tag_id)
    11  WHERE  c.tag_value NOT IN ('motorway','primary','unclassified');
    
  • Verification:

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

    1SELECT count(*) FROM walk_net;
    

Exercise: 3 (Chapter: SQL)

4.2.4. Exercise 4: Testing the views for routing

From the Venue to the hotel using the osm_id.

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)

    1SELECT seq, edge AS id, cost AS seconds
    2FROM pgr_dijkstra(
    3    'SELECT * FROM vehicle_net',
    4    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

    1SELECT seq, edge AS id, cost AS seconds
    2FROM pgr_dijkstra(
    3    'SELECT * FROM taxi_net',
    4    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.

    1SELECT seq, edge AS id, cost AS seconds
    2FROM pgr_dijkstra(
    3    'SELECT * FROM walk_net',
    4    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

Route showing names

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

 1SELECT
 2  results.*,
 3  name, length_m
 4FROM (
 5  SELECT seq, edge AS id, cost AS seconds
 6  FROM pgr_dijkstra(
 7      'SELECT * FROM vehicle_net',
 8      5020458299, 266939565)
 9  ) AS results
10LEFT JOIN vehicle_net
11  USING (id)
12ORDER BY seq;

Exercise: 5 (Chapter: SQL)

4.3. Geometry handling

4.3.1. Exercise 6: Route geometry (human readable)

From the Venue to the Brewry

Problem

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

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)

 1WITH results AS (
 2  SELECT seq, edge AS id, cost AS seconds
 3  FROM pgr_dijkstra(
 4      'SELECT * FROM vehicle_net',
 5      5020458299, 266939565)
 6  )
 7SELECT
 8  results.*,
 9  ST_AsText(the_geom) AS route_readable
10FROM results
11LEFT JOIN vehicle_net
12  USING (id)
13ORDER BY seq;

Exercise: 6 (Chapter: SQL)

4.3.2. Exercise 7: Route geometry (binary format)

From |place_3| to the |place_1| showing arrows.

Problem

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

Solution

 1WITH results AS (
 2  SELECT seq, edge AS id, cost AS seconds
 3  FROM pgr_dijkstra(
 4      'SELECT * FROM vehicle_net',
 5      5020458299, 266939565)
 6  )
 7SELECT
 8  results.*,
 9  the_geom AS route_geom
10FROM results
11LEFT JOIN vehicle_net
12  USING (id)
13ORDER BY seq;

Exercise: 7 (Chapter: SQL)

4.3.3. Exercise 8: Route geometry directionality

From |place_3| to the |place_1|

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

detail

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

1   2 | 12857 |   1.2075070194870985 | LINESTRING(11.2492507 43.7777478,11.2490531 43.7777)
2   3 | 13203 |  0.20005398086256934 | LINESTRING(11.2490531 43.7777,11.2490204 43.777692)
3   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)

 1WITH results AS (
 2  SELECT seq, edge AS id, cost AS seconds,
 3    node
 4  FROM pgr_dijkstra(
 5      'SELECT * FROM vehicle_net',
 6      5020458299, 266939565)
 7  )
 8SELECT
 9  seq, id, seconds,
10  CASE
11      WHEN node = source THEN ST_AsText(the_geom)
12      ELSE ST_AsText(ST_Reverse(the_geom))
13  END AS route_readable,
14
15  CASE
16      WHEN node = source THEN the_geom
17      ELSE ST_Reverse(the_geom)
18  END AS route_geom
19
20FROM results
21LEFT JOIN vehicle_net USING (id)
22ORDER BY seq;

Exercise: 8 (Chapter: SQL)

4.3.4. Exercise 9: Using the geometry

From |place_3| to the |place_1| show azimuth

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)

 1WITH
 2results AS (
 3  SELECT seq, edge AS id, cost AS seconds,
 4    node
 5  FROM pgr_dijkstra(
 6      'SELECT * FROM vehicle_net',
 7      5020458299, 266939565)
 8  ),
 9additional AS (
10  SELECT
11    seq, id, seconds,
12    CASE
13        WHEN node = source THEN ST_AsText(the_geom)
14        ELSE ST_AsText(ST_Reverse(the_geom))
15    END AS route_readable,
16
17    CASE
18        WHEN node = source THEN the_geom
19        ELSE ST_Reverse(the_geom)
20    END AS route_geom
21
22  FROM results
23  LEFT JOIN ways ON (gid = id)
24)
25SELECT seq, id, seconds,
26  degrees(ST_azimuth(ST_StartPoint(route_geom), ST_EndPoint(route_geom))) AS azimuth
27FROM additional
28ORDER 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)

 1-- DROP FUNCTION wrk_dijkstra(regclass, bigint, bigint);
 2
 3CREATE OR REPLACE FUNCTION wrk_dijkstra(
 4        IN edges_subset REGCLASS,
 5        IN source BIGINT,  -- in terms of osm_id
 6        IN target BIGINT,  -- in terms of osm_id
 7        OUT seq INTEGER,
 8        OUT id BIGINT,
 9        OUT seconds FLOAT,
10        OUT name TEXT,
11        OUT length_m FLOAT,
12        OUT route_readable TEXT,
13        OUT route_geom geometry,
14        OUT azimuth FLOAT
15    )
16    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)

 1$BODY$
 2  WITH
 3  results AS (
 4    SELECT seq, edge AS id, cost AS seconds,
 5      node
 6    FROM pgr_dijkstra(
 7        'SELECT * FROM ' || edges_subset,
 8        source, target)
 9    ),
10  additional AS (
11    SELECT
12      seq, id, seconds,
13      name, length_m,
14      CASE
15          WHEN node = source THEN ST_AsText(the_geom)
16          ELSE ST_AsText(ST_Reverse(the_geom))
17      END AS route_readable,
18
19      CASE
20          WHEN node = source THEN the_geom
21          ELSE ST_Reverse(the_geom)
22      END AS route_geom
23
24    FROM results
25    LEFT JOIN ways ON (gid = id)
26  )
27  SELECT *,
28    degrees(ST_azimuth(ST_StartPoint(route_geom), ST_EndPoint(route_geom))) AS azimuth
29  FROM additional
30  ORDER BY seq;
31$BODY$
32LANGUAGE '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

1SELECT *
2FROM wrk_dijkstra('vehicle_net',  5020458299, 266939565);
3
4SELECT *
5FROM wrk_dijkstra('taxi_net',  5020458299, 266939565);
6
7SELECT *
8FROM 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