6. pl/pgsql function

Other kind of functions are pl/pgsql. As the applications requirements become more complex, using wrappers of previously defined functions becomes necessary for clarity.

6.1. Requirements for routing from A to B

Chapter problem:

• Create a function wrk_fromAtoB that allows routing from 2 geometries.

• The function takes latitude/longitude points as input parameters.

• Returns a route that includes a geometry so that if can be displayed, for example, in QGIS.

• Will also return some other attributes.

The detailed description:

Input parameters

Parameter	type	Description
edges_subset	REGCLASS	Edge table name identifier.
lat1	NUMERIC	The latitude of the departure point.
lon1	NUMERIC	The longitude of the departure point.
lat2	NUMERIC	The latitude of the destination point.
lon2	NUMERIC	The longitude of the destination point.
do_debug	BOOLEAN	Flag to create a WARNING with the query that is been executed

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 meters.
minutes	Minutes taken to traverse the segment.
route_geom	The road geometry with corrected directionality.

For this chapter, the following points will be used for testing.

• (lat,lon) = (-1.455829, -48.446044)

• (lat,lon) = (-1.453448, -48.447142)

6.2. The Vertices Table

Graphs have a set of edges and a set of vertices associated to it. The views need their vertices table.

6.2.1. Exercise 1: Create vertices table

Problem

• Create a vertices table for the views:

  • vehicle_net

  • taxi_net

  • walk_net

Solution

• Use pgr_extractVertices (explained in Graph views) to create the vertices table

• JOIN the vertices table with ways_vertices (created in Graph views) to get the x, y, geom information.

For vehicle_net:

SELECT * INTO vehicle_net_vertices
FROM pgr_extractVertices(
  'SELECT id, source, target
  FROM vehicle_net ORDER BY id');

UPDATE vehicle_net_vertices AS v
SET (x,y,geom) = (w.x, w.y, w.geom)
FROM ways_vertices AS w WHERE v.id = w.id;

For taxi_net:

SELECT * INTO taxi_net_vertices
FROM pgr_extractVertices(
  'SELECT id, source, target
  FROM taxi_net ORDER BY id');

UPDATE taxi_net_vertices AS v
SET (x,y,geom) = (w.x, w.y, w.geom)
FROM ways_vertices AS w WHERE v.id = w.id;

For walk_net:

Modify the above queries to create the walk_net_vertices table.

Answer

SELECT * INTO walk_net_vertices
FROM pgr_extractVertices(
  'SELECT id, source, target
  FROM walk_net ORDER BY id');

UPDATE walk_net_vertices AS v
SET (x,y,geom) = (w.x, w.y, w.geom)
FROM ways_vertices AS w WHERE v.id = w.id;

Note

It is left to the reader to remove disconected components on the views.

See Graph views

6.2.2. Exercise 3: Nearest Vertex

Problem

Calculate the (OSM) identifier of the nearest vertex to a point.

In particular use the following (lat, lon) value: (-1.455829, -48.446044).

• calculate the nearest OSM identifier of the vertex to:

  • ways_vertices

  • vehicle_net_vertices

  • taxi_net_vertices

  • walk_net_vertices

Solution

• Remember that the id has an OSM vertex identifier on the vertices tables.

• Using the Postgis distance operator <-> to order by distance.

• Get only the first row, to obtain the nearest identifier of the vertex.

For ways_vertices:

SELECT id
FROM ways_vertices
ORDER BY geom <-> ST_SetSRID(ST_Point(-48.446044, -1.455829), 4326)
LIMIT 1;

Query results

     id     
------------
 4834707721
(1 row)

For vehicle_net_vertices:

Modify the previous query.

SELECT id
FROM vehicle_net_vertices
ORDER BY geom <-> ST_SetSRID(ST_Point(-48.446044, -1.455829), 4326)
LIMIT 1;

Query results

     id     
------------
 4834707721
(1 row)

For taxi_net_vertices:

Modify the previous query.

Query results

     id     
------------
 6948263223
(1 row)

For walk_net_vertices:

Modify the previous query.

Query results

     id     
------------
 4834707721
(1 row)

6.2.3. Exercise 4: Nearest vertex function

Problem

When operations look similar for different tables, a function can be created.

• Create a function that calculates the OSM identifier of the nearest vertex to a point.

• Function name: wrk_nearest.

• Needs to work only for the final application views and table.

The input parameters:

Column	type	Description
vertex_table	REGCLASS	Table name identifier.
lat	NUMERIC	The latitude of a point.
lon	NUMERIC	The longitude of a point.

The output:

type	Description
BIGINT	the OSM identifier that is nearest to (lat,lon).

Solution

• The function returns only one BIGINT value.

• Using format to build the query.

  • The structure of the query is similar to Exercise 3: Nearest Vertex solutions.

  • %1$I for the table name identifier.

  • %2$s and %3$s for the latitude and longitude.

    • The point is formed with (lon/lat) (%3$s, %2$s).

CREATE OR REPLACE FUNCTION wrk_Nearest(
  IN vertex_table REGCLASS,
  IN lat NUMERIC,
  IN lon NUMERIC)
RETURNS BIGINT AS
$BODY$
DECLARE result BIGINT;
BEGIN

  EXECUTE format(
    $$
      SELECT id
      FROM %1$I
      ORDER BY geom <-> ST_SetSRID(ST_Point(%3$s, %2$s), 4326)
      LIMIT 1
    $$,
    vertex_table, lat, lon)
  INTO result;
  RETURN result;

END
$BODY$
LANGUAGE 'plpgsql';

6.2.4. Exercise 5: Test nearest vertex function

Problem

• Test the wrk_Nearest function.

Use the following (lat,lon) values: (-1.455829, -48.446044).

• The point is the same as in Exercise 3: Nearest Vertex problem.

  • Verify the results are the same.

• calculate the nearest OSM identifier of the vertex to:

  • ways_vertices

  • vehicle_net_vertices

  • taxi_net_vertices

  • walk_net_vertices

Solution

For ways_vertices:

• Use the function with ways_vertices as the vertex_table parameter.

• Pass the (lat,lon) values as second and third parameters.

• Using the function on the original data does not return the OSM identifier.

  The value stored in id column is not the OSM identifier.

SELECT wrk_Nearest('ways_vertices', -1.455829, -48.446044);

Query results

 wrk_nearest 
-------------
  4834707721
(1 row)

For vehicles_net_vertices:

• Modify the previous query.

SELECT wrk_Nearest('vehicle_net_vertices', -1.455829, -48.446044);

Query results

 wrk_nearest 
-------------
  4834707721
(1 row)

For taxi_net_vertices:

• Modify the previous query.

Query results

 wrk_nearest 
-------------
  6948263223
(1 row)

For walk_net_vertices:

Query results

 wrk_nearest 
-------------
  4834707721
(1 row)

6.3. wrk_fromAtoB function

In this section, creation and testing the required function will be tackled.

6.3.1. Exercise 6: Creating the main function

Problem

• Create the function wrk_fromAtoB.

• Follow the description given at Requirements for routing from A to B.

• Use specialized functions:

  • wrk_NearestOSM created on Exercise 4: Nearest vertex function.

    • It receives the point in natural language format.

    • Obtains the OSM identifier needed by wrk_dijkstra.

  • wrk_dijkstra created in SQL function

Solution

The function’s signature:

• The input parameters highlighted.

• The output columns are not highlighted.

• The function returns a set of values.

CREATE OR REPLACE FUNCTION wrk_fromAtoB(
  IN edges_subset REGCLASS,
  IN lat1 NUMERIC, IN lon1 NUMERIC,
  IN lat2 NUMERIC, IN lon2 NUMERIC,
  IN do_debug BOOLEAN DEFAULT false,

  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 function’s body:

Call to the function wrk_dijkstra

• Using PostgreSQL format to make substitutions

  • The first parameter is the string to be replaced

  • The rest are the data parameters, are the strings use for replacement.

• wrk_dijkstra obtains the values for the output

• The edges_subset value will replace %1$I:

• For the source and target:

  • wrk_Nearest is used to find the identifier.

    • The vertices table name is formed with %1$I_vertices.

  • lat1, lon1 values will replace %2$s, %3$s respectively.

  • lat2, lon2 values will replace %4$s, %5$s respectively.

• To get the constructed query in form of a warning:

  • The WARNING will be issued only when do_debug is true.

  • No output will be generated.

$BODY$
DECLARE
final_query TEXT;
BEGIN
  final_query := format(
    $$
      SELECT *
      FROM wrk_dijkstra(
        '%1$I',
        (SELECT wrk_Nearest('%1$I_vertices', %2$s, %3$s)),
        (SELECT wrk_Nearest('%1$I_vertices', %4$s, %5$s))
      )
    $$,
    -- Subtitutions on the query are in order
    edges_subset, lat1, lon1, lat2, lon2);

    IF do_debug THEN
      RAISE NOTICE '%', final_query;
      RETURN;
    END IF;
    RETURN QUERY EXECUTE final_query;
END;
$BODY$
LANGUAGE 'plpgsql';

The complete function

CREATE OR REPLACE FUNCTION wrk_fromAtoB(
  IN edges_subset REGCLASS,
  IN lat1 NUMERIC, IN lon1 NUMERIC,
  IN lat2 NUMERIC, IN lon2 NUMERIC,
  IN do_debug BOOLEAN DEFAULT false,

  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
-- signature ends
$BODY$
DECLARE
final_query TEXT;
BEGIN
  final_query := format(
    $$
      SELECT *
      FROM wrk_dijkstra(
        '%1$I',
        (SELECT wrk_Nearest('%1$I_vertices', %2$s, %3$s)),
        (SELECT wrk_Nearest('%1$I_vertices', %4$s, %5$s))
      )
    $$,
    -- Subtitutions on the query are in order
    edges_subset, lat1, lon1, lat2, lon2);

    IF do_debug THEN
      RAISE NOTICE '%', final_query;
      RETURN;
    END IF;
    RETURN QUERY EXECUTE final_query;
END;
$BODY$
LANGUAGE 'plpgsql';

6.3.2. Exercise 7: Using the main function

Problem

Use wrk_fromAtoB

• Departure point is: (lat,lon) = (-1.455829, -48.446044)

• Destination point is: (lat,lon) = (-1.453448, -48.447142)

• For vehicle_net:

  • Use with default value of do_debug.

• For taxi_net:

  • Use with do_debug set to true.

• For walk_net:

  • Use with default value of do_debug.

  • Store results on a table.

  • Show the table contents.

Note

The function is not meant to be used with ways

Solution

For vehicle_net:

• The first parameter is the table name.

• The next two parameters are the latitude and longitude of the departure point.

• The next two parameters are the latitude and longitude of the destination point.

SELECT DISTINCT name
FROM wrk_fromAtoB(
  'vehicle_net',
  -1.455829, -48.446044,
  -1.453448, -48.447142);

Query results

       name        
-------------------
 
 Rua Nova
 Passagem São Luis
(3 rows)

For taxi_net:

• Do a dry run by adding true to get the query that is executed.

SELECT *
FROM wrk_fromAtoB(
  'taxi_net',
  -1.455829, -48.446044,
  -1.453448, -48.447142,
  true);

Query results

 seq | id | seconds | name | length_m | route_readable | route_geom | azimuth 
-----+----+---------+------+----------+----------------+------------+---------
(0 rows)

For walk_net:

SELECT *
INTO example
FROM wrk_fromAtoB(
  'walk_net',
  -1.455829, -48.446044,
  -1.453448, -48.447142);

SELECT * FROM example;

Query results

SELECT 5
 seq |  id   |      seconds      |       name        |      length_m      |                                                         route_readable                                                         |                                                                                         route_geom                                                                                         |      azimuth       
-----+-------+-------------------+-------------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------
   1 | 16167 | 2.861427813328004 | Passagem São Luis |  39.74205296288895 | LINESTRING(-48.4460365 -1.4557156,-48.4460753 -1.4557343,-48.4461357 -1.4557464,-48.4462825 -1.4557638,-48.4463871 -1.4557591) | 0102000020E61000000500000041295AB9173948C0AA6EE4709C4AF7BFD586D4FE183948C0B2A5A20CB04AF7BFFE8980F91A3948C0D550B4BCBC4AF7BF7923F3C81F3948C001367BFBCE4AF7BF9BED6536233948C099ABD50DCA4AF7BF |  262.9272846676941
   2 | 20152 | 5.582554669153525 | Passagem São Luis |  77.53548151602118 | LINESTRING(-48.4463871 -1.4557591,-48.4464837 -1.455735,-48.4466674 -1.4556928,-48.447061 -1.4555817)                          | 0102000020E6100000040000009BED6536233948C099ABD50DCA4AF7BF3AD9BC60263948C0ED478AC8B04AF7BF3D98B9652C3948C0DA5C9088844AF7BF95287B4B393948C0517F6209104AF7BF                                 |  284.7481662181404
   3 | 20176 | 7.240785806093086 | Rua Nova          | 100.56646952907064 | LINESTRING(-48.447061 -1.4555817,-48.4470423 -1.454802,-48.4470462 -1.4546725)                                                 | 0102000020E61000000300000095287B4B393948C0517F6209104AF7BFDD369DAE383948C07C7C4276DE46F7BF8A6654CF383948C0BC3FDEAB5646F7BF                                                                 | 0.9325810062371391
   4 |  3140 | 8.585486262069729 | Rua Nova          | 119.24286475096845 | LINESTRING(-48.4470462 -1.4546725,-48.4470845 -1.4535948)                                                                      | 0102000020E6100000020000008A6654CF383948C0BC3FDEAB5646F7BF36069D103A3948C0AB2BFA9EEC41F7BF                                                                                                 |   357.964642104661
   5 |    -1 |                 0 |                   |                    |                                                                                                                                |                                                                                                                                                                                            |                   
(5 rows)