5. pl/pgsql function

../_images/route.png

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

5.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

Column

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) = (43.784, 11.2384)

  • (lat,lon) = (43.778, 11.2492)

Saving this information on a table:

  • The X value of a geometry is the longitude.

  • The Y value of a geometry is the latitude.

  • Natural language to form the point is (latitude, longitude).

  • For geometry processing to form the point is (longitude, latitude).

  • lines 4 and 6 show the inverse order of the (lat,lon) pairs.

1SELECT *
2INTO points
3FROM (
4  SELECT 1 AS gid, ST_SetSRID(ST_Point(11.2384, 43.784), 4326) AS geom
5  UNION
6  SELECT 2, ST_SetSRID(ST_Point(11.2492, 43.778), 4326)
7  ) AS info;

5.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 taxi_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.

5.2.1. Exercise 1: Number of vertices

Problem

  • Calculate the number of vertices in a graph.

Depending on the graph calculate the number of vertices of:

  • ways

  • vehicle_net

  • taxi_net

  • walk_net

Solution

  • For ways:

    • osm2pgrouting automatically created the ways_vertices_pgr table that contains all the vertices in ways table.

    • Using aggregate function count. (line 1)

    • Count all the rows of ways_vertices_pgr table. (line 2)

    1SELECT count(*)
    2FROM ways_vertices_pgr;
    
  • For vehicle_net:

    • Extract the vertices identifiers of the source column. (line 3)

    • Extract the vertices identifiers of the target column. (line 8)

    • UNION both results (line 6)

     1SELECT count(*)
     2FROM (
     3  SELECT source
     4  FROM vehicle_net
     5
     6  UNION
     7
     8  SELECT target
     9  FROM vehicle_net
    10) AS id_list;
    
  • For taxi_net:

    • Similar solution as in previous query but on taxi_net. (lines 4 and 9)

     1SELECT count(*)
     2FROM (
     3  SELECT source
     4  FROM taxi_net
     5
     6  UNION
     7
     8  SELECT target
     9  FROM taxi_net
    10) AS id_list;
    
  • For walk_net:

    • Similar solution as in previous query but on walk_net. (lines 4 and 9)

     1SELECT count(*)
     2FROM (
     3  SELECT source
     4  FROM walk_net
     5
     6  UNION
     7
     8  SELECT target
     9  FROM walk_net
    10) AS id_list;
    

Exercise: 1 (Chapter: pl/pgsql)

5.2.2. Exercise 2: Vertices on a table

Problem

  • Create a vertices table.

  • Follow the suffix naming _vertices_pgr.

Depending on the graph create a vertices table of:

  • ways

  • vehicle_net

  • taxi_net

  • walk_net

The vertices table should contain:

Column

Description

osm_id

OSM Identifier of the vertex.

the_geom

The geometry of the vertex.

Solution

  • For ways:

    • osm2pgrouting automatically created the ways_vertices_pgr table that contains all the vertices in ways table.

    • The vertices are already on a table.

    • The table suffix follows is as requested.

    • There is no need to create a table.

    • The source and target columns are in terms of id column of ways_vertices_pgr

  • For vehicle_net:

    • Using the query id_list from Exercise 1: Number of vertices. (not highlighted lines 2 to 8)

    • JOIN with ways_vertices_pgr that has the OSM identifier and the geometry information. (line 13)

    • Extract the osm_id and the_geom. (line 10)

    • Save in table vehicle_net_vertices_pgr. (line 11)

    • The source and target columns values have the osm_id therefore the id column of vehicle_net_vertices_pgr must also have the osm_id values

     1WITH id_list AS (
     2  SELECT source AS id
     3  FROM vehicle_net
     4
     5  UNION
     6
     7  SELECT target
     8  FROM vehicle_net)
     9
    10SELECT id_list.id, the_geom
    11INTO vehicle_net_vertices_pgr
    12FROM id_list
    13JOIN ways_vertices_pgr ON (id_list.id = osm_id);
    
  • For taxi_net:

    • Similar solution as in previous query but on taxi_net. (lines 3, 8 and 11)

     1
     2WITH id_list AS (
     3  SELECT source AS id
     4  FROM taxi_net
     5
     6  UNION
     7
     8  SELECT target
     9  FROM taxi_net)
    10
    11SELECT id_list.id, the_geom
    12INTO taxi_net_vertices_pgr
    13FROM id_list
    14JOIN ways_vertices_pgr ON (id_list.id = osm_id);
    15
    
  • For walk_net:

    • Similar solution as in previous query but on taxi_net. (lines 3, 8 and 11)

     1
     2WITH id_list AS (
     3  SELECT source AS id
     4  FROM walk_net
     5
     6  UNION
     7
     8  SELECT target
     9  FROM walk_net)
    10
    11SELECT id_list.id, the_geom
    12INTO walk_net_vertices_pgr
    13FROM id_list
    14JOIN ways_vertices_pgr ON (id_list.id = osm_id);
    15
    

Exercise: 2 (Chapter: pl/pgsql)

5.2.3. Exercise 3: Nearest Vertex

Problem

  • Calculate the OSM identifier of the nearest vertex to a point.

In particular use the following (lat,lon) value: (43.784, 11.2384).

  • calculate the nearest OSM identifier of the vertex to:

    • vehicle_net_vertices_pgr

    • taxi_net_vertices_pgr

    • walk_net_vertices_pgr

Note

The ways and the ways_vertices_pgr tables are not used on the final applications

The net views and vertices tables have been prepared in such a way that the AS statement is not needed any more on a pgRouting function.

Solution

  • For ways_vertices_pgr:

    • Get the osm_id. (line 1)

    • Using the distance operator <-> to order by distance. (line 3)

    • Get only the first row, to obtain the nearest OSM identifier of the vertex. (line 4)

1SELECT osm_id
2FROM ways_vertices_pgr
3ORDER BY the_geom <-> ST_SetSRID(ST_Point(11.2384, 43.784), 4326)
4LIMIT 1;
  • For vehicle_net_vertices_pgr:

    • Similar solution as in previous query but:

      • Extracting the id columns. (line 1)

      • On vehicle_net_vertices_pgr. (line 2)

1SELECT id
2FROM vehicle_net_vertices_pgr
3ORDER BY the_geom <-> ST_SetSRID(ST_Point(11.2384, 43.784), 4326)
4LIMIT 1;
  • For taxi_net_vertices_pgr:

    • Similar solution as in previous query but on taxi_net_vertices_pgr. (line 2)

1SELECT id
2FROM taxi_net_vertices_pgr
3ORDER BY the_geom <-> ST_SetSRID(ST_Point(11.2384, 43.784), 4326)
4LIMIT 1;
  • For walk_net_vertices_pgr:

    • Similar solution as in previous query but on walk_net_vertices_pgr. (line 2)

1SELECT id
2FROM walk_net_vertices_pgr
3ORDER BY the_geom <-> ST_SetSRID(ST_Point(11.2384, 43.784), 4326)
4LIMIT 1;

Exercise: 3 (Chapter: pl/pgsql)

5.2.4. 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_NearestOSM.

  • 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 value. (line 5)

  • Using format to build the query. (line 10)

    • The structure of the query is similar to Exercise 3: Nearest Vertex solutions. (lines 12 to 16)

    • %1$I for the table name identifier. (line 13)

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

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

    • The additional parameters of function format, are the parameters of the function we are creating. (line 19)

 1CREATE OR REPLACE FUNCTION wrk_NearestOSM(
 2  IN vertex_table REGCLASS,
 3  IN lat NUMERIC,
 4  IN lon NUMERIC)
 5RETURNS BIGINT AS
 6$BODY$
 7DECLARE result BIGINT;
 8BEGIN
 9
10  EXECUTE format(
11    $$
12      SELECT id
13      FROM %1$I
14      ORDER BY the_geom <-> ST_SetSRID(
15                              ST_Point(%3$s, %2$s),
16                              4326)
17      LIMIT 1
18    $$,
19    vertex_table, lat, lon)
20  INTO result;
21  RETURN result;
22
23END
24$BODY$
25LANGUAGE 'plpgsql';

Exercise: 4 (Chapter: pl/pgsql)

5.2.5. Exercise 5: Test nearest vertex function

Problem

  • Test the wrk_NearestOSM function.

In particular use the following (lat,lon) values: (43.784, 11.2384).

  • 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_pgr

    • vehicle_net_vertices_pgr

    • taxi_net_vertices_pgr

    • walk_net_vertices_pgr

Solution

  • For ways_vertices_pgr:

    • Use the function with ways_vertices_pgr as the vertex_table parameter. (line 2)

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

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

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

    1SELECT wrk_NearestOSM(
    2  'ways_vertices_pgr',
    3  43.784, 11.2384);
    
  • For vehicles_net_vertices_pgr:

    • Similar solution as in previous query but on vehicles_net_vertices_pgr. (lines 2)

    1SELECT wrk_NearestOSM(
    2  'vehicle_net_vertices_pgr',
    3  43.784, 11.2384);
    
  • For taxi_net_vertices_pgr:

    • Similar solution as in previous query but on taxi_net_vertices_pgr. (lines 2)

    1SELECT wrk_NearestOSM(
    2  'taxi_net_vertices_pgr',
    3  43.784, 11.2384);
    
  • For walk_net_vertices_pgr:

    • Similar solution as in previous query but on walk_net_vertices_pgr. (lines 2)

    1SELECT wrk_NearestOSM(
    2  'walk_net_vertices_pgr',
    3  43.784, 11.2384);
    

Exercise: 5 (Chapter: pl/pgsql)

5.3. wrk_fromAtoB function

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

5.3.1. Exercise 6: Creating the main function

Problem

Solution

The function’s signature:

  • The input parameters highlited on lines 2 to 5.

  • The output columns are not higlighted on lines 7 to 13.

  • The function returns a set of values. (line 15)

 1CREATE OR REPLACE FUNCTION wrk_fromAtoB(
 2  IN edges_subset REGCLASS,
 3  IN lat1 NUMERIC, IN lon1 NUMERIC,
 4  IN lat2 NUMERIC, IN lon2 NUMERIC,
 5  IN do_debug BOOLEAN DEFAULT false,
 6
 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)
16RETURNS SETOF record AS

The function’s body:

  • Call to the function wrk_dijkstra (line 8)

    • wrk_dijkstra obtains many of the result values

    • Parameters are passed on lines 9 to 13.

    • The edges_subset:

      • First parameters of the format function is the table name. (line 16)

      • Is passed as %1$I. (line 9)

    • For the departure point:

      • wrk_NearestOSM is used to find the OSM identifier. (line 10)

        • The vertices table name is formed with %1$I_vertices_pgr. (line 11)

        • Second and third parameters of the format function are %2$s, %3$s. (line 17)

        • The latitude and longitude are given in natural language form. (line 12)

    • For the destination point:

      • Similar query is constructed but with the destination information. (line 13)

      • Fourth and fifth parameters of the format function. (line 18)

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

    • The WARNING will be issued only when do_debug is true. (lines 20 to 22)

 1$BODY$
 2DECLARE
 3final_query TEXT;
 4BEGIN
 5  final_query := format(
 6    $$
 7      SELECT *
 8      FROM wrk_dijkstra(
 9        '%1$I',
10        (SELECT wrk_NearestOSM(
11            '%1$I_vertices_pgr',
12            %2$s, %3$s)),
13        (SELECT wrk_NearestOSM('%1$I_vertices_pgr', %4$s, %5$s))
14      )
15    $$,
16    edges_subset,
17    lat1,lon1,
18    lat2,lon2);
19
20    IF do_debug THEN
21      RAISE WARNING '%', final_query;
22    END IF;
23    RETURN QUERY EXECUTE final_query;
24END;
25$BODY$
26LANGUAGE 'plpgsql';

Exercise: 6 (Chapter: pl/pgsql)

5.3.2. Exercise 7: Using the main function

View of roads for taxis along with source and destination

Problem

Use wrk_fromAtoB

  • Departure point is: (lat,lon) = (43.784, 11.2384)

  • Destination point is: (lat,lon) = (43.778, 11.2492)

  • 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. (line 2)

    • The next two parameters are the latitude and longitude of the departure point. (line 3)

    • The next two parameters are the latitude and longitude of the destination point. (line 4)

    1SELECT *  FROM wrk_fromAtoB(
    2  'vehicle_net',
    3  43.784, 11.2384,
    4  43.778, 11.2492);
    
  • For taxi_net:

    • Similar to previous solution, but with taxi_net (line 2)

    • Adding true to get the query that is executed. (line 5)

    1SELECT *  FROM wrk_fromAtoB(
    2  'taxi_net',
    3  43.784, 11.2384,
    4  43.778, 11.2492,
    5  true);
    
  • For walk_net:

    • Similar to a previous solution, but with ways (line 4)

    • Store results on a table. (line 2)

    • Show the table contents using a SELECT clause (lines 8 and 9).

    1SELECT *
    2INTO example
    3FROM wrk_fromAtoB(
    4  'walk_net',
    5  43.784, 11.2384,
    6  43.778, 11.2492);
    7
    8SELECT *
    9FROM example;
    

Exercise: 7 (Chapter: pl/pgsql)