4. Función SQL

../_images/route.png

Las funciones pgRouting proporcionan una interfaz de bajo nivel.

Al desarrollar para una aplicación de nivel superior, los requisitos deben estar representados en las consultas SQL. A medida que estas consultas SQL se vuelven más complejas, es conveniente almacenarlas en funciones o procedimientos almacenados de postgreSQL. Los procedimientos o funciones almacenados son una forma eficaz de envolver la lógica de la aplicación, en este caso, relacionada con la lógica y los requisitos de ruteo.

4.1. Los requisitos de aplicación

En este capítulo hay tres requisitos que siguen la misma lógica. Consta de 2 tipos de vehículos y el trazado peatonal:

  • Vehículo en particular:

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

    • La velocidad es la velocidad predeterminada de la información de OSM.

  • Taxi vehicle:

    • Circular en un área más pequeña cerca de «Hotel Albani».

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

      • No usar steps, footway, path

    • Speed is 10% faster than the Particular vehicles.

  • Peatones:

    • Walk on the whole Firenze area.

    • No se puede circular en autopistas ni en segmentos primarios.

    • La velocidad es de 2 mts/sec.

Una interfaz necesita la siguiente información de ruteo:
  • seq - Un identificador único de las filas.

  • gid - El identificador del segmento

  • name - El nombre del segmento

  • length - La longitud del segmento

  • seconds - Número de segundos para atravesar el segmento

  • azimuth - El azimuth del segmento

  • route_geom - La geometría del ruteo

  • route_readable - La geometría en forma humanamente legible.

y necesita trabajar en función del grafo y los identificadores OSM de los vértices.

Diseño de la función

La función wrk_dijkstra se creará con los siguientes parámetros de entrada y columnas de salida:

Parámetros de entrada

Nombre

Tipo

Descripción

edges_subset

REGCLASS

La tabla/vista que se utilizará para procesar

source_osm

BIGINT

El identificador OSM de la ubicación de la salida.

target_osm

BIGINT

El identificador OSM de la ubicación del destino.

columnas de salida

Nombre

Tipo

Descripción

seq

INTEGER

Un único número para cada fila de resultados

id

BIGINT

El identificador de arista

name

TEXT

El nombre del segmento.

seconds

FLOAT

El número de segundos que se tarda en atravesar el segmento.

azimuth

FLOAT

El azimuth del segmento.

length_m

FLOAT

Longitud en metros del segmento.

route_readable

TEXT

La geometría en forma humanamente legible.

route_geom

geometry

La geometría del segmento en la dirección correcta.

4.2. Preparación de grafos de procesamiento

4.2.1. Ejercicio 1: Creación de una vista para el ruteo

Vista de carreteras para vehículos

Problema

  • Cree una vista con una cantidad mínima de información para procesar los vehículos en particular.

  • El costo de ruteo en cost y reverse_cost será en segundos para los cálculos de ruteo.

  • Excluir los segmentos de camino peatonal, senda, caminitos

  • Datos necesarios en la vista para su posterior procesamiento.

    • length_m La longitud en metros.

    • the_geom La geometría|.

  • Comprobar que se ha reducido el número de aristas.

Solución

  • Creación de la vista:

    • Los requisitos de source and target para la función deben ser con identificadores OSM. (línea 6)

    • cost y reverse_cost son en términos de segundos. (línea 7)

    • Los parámetros adicionales length_m y the_geom. (línea 8)

    • JOIN con configuration:

      • Excluir pasos, senda, recorrido. (línea 11)

    • Si necesita reconstruir la vista, primero borrarla usando el comando en línea 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');
    
  • Verificación:

    • Contar las filas en los caminos originales (línea 1)

    • Contar las filas en la vista vehicle_net (línea 2)

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

Ejercicio: 1 (Capítulo: SQL)

4.2.2. Ejercicio 2: Limitar la red viaria dentro de un área

Vista de un conjunto más pequeño de carreteras para vehículos

Problema

  • Cree una vista taxi_net para el 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.

  • Verifique el número reducido de segmentos de carretera.

Solución

  • Creación de la vista:

    • El grafo del taxi es un subconjunto del grafo vehicle_net. (línea 9)

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

    • Ajuste el cost y reverse_cost del taxi para que sea el 90% del vehículo en particular. (línea 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);
    
  • Verificación:

    • Cuente las filas en el taxi_net original.

    1SELECT count(*) FROM taxi_net;
    

Ejercicio: 2 (Capítulo: SQL)

4.2.3. Ejercicio 3: Creación de una vista materializada para ruteo de los peatones

Vista de carreteras para vehículos

Problema

  • Crear una vista materializada con una cantidad mínima de información para procesar peatones.

  • El costo de ruteo en cost y reverse_cost será en segundos para los cálculos de ruteo.

    • La velocidad es de 2 mts/sec.

  • Excluir segmentos de autopista y primarios.

  • Datos necesarios en la vista para su posterior procesamiento.

    • length_m La longitud en metros.

    • the_geom La geometría|.

  • Comprobar que se ha reducido el número de aristas.

Solución

  • Creación de la vista:

     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');
    
  • Verificación:

    • Cuente las filas de la vista walk_net (line 1)

    1SELECT count(*) FROM walk_net;
    

Ejercicio: 3 (Capítulo: SQL)

4.2.4. Ejercicio 4: Probar las vistas para el ruteo

Desde la sede hasta el hotel utilizando el `osm_id`.

Problema

  • Pruebe las vistas creadas

En particular:

  • Desde «Palazzo dei Congressi» hacia «Mercato Centrale» usando el identificador OSM

  • las vistas a probar son:

    • vehicle_net

    • taxi_net

    • walk_net

  • Mostrar únicamente los siguientes resultados, ya que las demás columnas deben omitirse en la función.

    • seq

    • edge con el nombre id

    • cost con el nombre: seconds

Solución

  • En general

    • El punto de partida es «Palazzo dei Congressi» con el identificador OSM 5020458299.

    • El destino es «Mercato Centrale» con el identificador OSM 266939565.

  • Para vehicle_net:

    • Se utiliza vehicle_net.

    • La selección de las columnas con los nombres correspondientes está en línea 1.

    • La vista se prepara con los nombres de columna que pgRouting utilizan.

      • No es necesario cambiar el nombre de las columnas. (línea 3)

    • Se utilizan los identificadores OSM del punto de partida y del destino. (línea 4)

    1SELECT seq, edge AS id, cost AS seconds
    2FROM pgr_dijkstra(
    3    'SELECT * FROM vehicle_net',
    4    5020458299, 266939565);
    
  • Para taxi_net:

    • Similar al anterior pero con taxi_net (línea 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);
    
  • Para walk_net:

    • Similar al anterior pero con walk_net. (línea 3)

    • Los resultados dan una ruta diferente a la de los vehículos.

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

Nota

De estas consultas, se puede deducir que lo que diseñamos para una vista funcionará para las otras vistas. En los siguientes ejercicios solo se usará vehicle_net, pero puede probar las consultas con las otras vistas.


Ejercicio: 4 (Capítulo: SQL)

4.2.5. Ejercicio 5: Obtener información adicional

Ruta que muestra nombres

Problema

  • Desde Palazzo dei Congressi hasta Mercato Centrale, utilizando identificadores OSM.

  • además de Ejercicio 4: Probar las vistas para el ruteo resultados también obtienen información que se encuentra en el subconjunto de bordes:

    • name

    • length_m

Solución

  • La consulta de Ejercicio 4: Probar las vistas para el ruteo se utiliza como una subconsulta denominada results (no líneas resaltadas 5 a 9)

  • La cláusula SELECT contiene

    • Todas las columnas de results. (línea 2)

    • Los valores name y length_m. (línea 3)

  • Se necesita un LEFT JOIN con vehicle_net para obtener la información adicional. (línea 10)

    • Tiene que ser LEFT porque hay una fila con id = -1 que no existe en 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;

Ejercicio: 5 (Capítulo: SQL)

4.3. Manejo de geometría

4.3.1. Ejercicio 6: Geometría de la ruta (legible para humanos)

De la sede a la cervecería

Problema

  • Desde «Palazzo dei Congressi» hasta «Mercato Centrale», además, se obtiene la geometría en forma legible por humanos.

Truco

WITH proporciona una manera de escribir instrucciones auxiliares en consultas más grandes. Se puede considerar como la definición de tablas temporales que existen solo para una consulta.

Solución

  • La consulta de Ejercicio 4: Probar las vistas para el ruteo se utiliza como una subconsulta denominada results esta vez en una cláusula WITH. (líneas no resaltadas 2 a 6)

  • La cláusula SELECT contiene:

    • Todas las columnas de results. (línea 8)

    • El the_geom procesado con ST_AsText para obtener la forma legible por humanos. (línea 9)

      • Cambia el nombre del resultado a route_readable

  • Como antes LEFT JOIN con vehicle_net. (línea 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;

Ejercicio: 6 (Capítulo: SQL)

4.3.2. Ejercicio 7: Geometría de ruta (formato binario)

Desde |place_3| al |place_1| mostrando flechas.

Problema

  • Desde «Palazzo dei Congressi» hasta «Mercato Centrale», la geometría en formato binario.

Solución

 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;

Ejercicio: 7 (Capítulo: SQL)

4.3.3. Ejercicio 8: Direccionalidad de la geometría de la ruta

Desde |place_3| al |place_1|

Inspeccionando la imagen detallada de Ejercicio 7: Geometría de ruta (formato binario) hay flechas que no coinciden con la direccionalidad de la ruta.

detalle

Inspeccionar el detalle de los resultados de Ejercicio 6: Geometría de la ruta (legible para humanos)

  • Para tener una direccionalidad correcta, el punto final de una geometría debe coincidir con el punto inicial de la geometría siguiente

  • Las líneas 2 y 3 no coinciden con ese criterio

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)

Problema

  • Desde «Palazzo dei Congressi» hasta «Mercato Centrale»,

    • Además de Ejercicio 4: Probar las vistas para el ruteo resultados también obtener información encontrada en el subconjunto de bordes de:

      • the_geom en forma legible por humanos nombrado como route_readable

      • the_geom en formato binario con el nombre route_geom

      • Ambas columnas deben tener la geometría corregida para la direccionalidad.

Solución

  • Para obtener la dirección correcta, algunas geometrías deben invertirse:

    • Invertir una geometría dependerá de la columna node de la consulta a dijkstra (línea 3)

      • Ese nodo no es necesario en la salida de la consulta, por lo que se nombra explícitamente las columnas necesarias en la línea 9.

    • Una instrucción condicional CASE que devuelve la geometría en forma legible por humanos:

      • De la geometría cuando node es la columna source. (línea 11)

      • De la geometría invertida cuando node no es la columna source. (línea 12)

    • Una instrucción condicional CASE que devuelve:

      • La geometría invertida cuando node no es la columna source. (línea 16)

      • La geometría cuando node es la columna source. (línea 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;

Ejercicio: 8 (Capítulo: SQL)

4.3.4. Ejercicio 9: Uso de la geometría

Desde |place_3| al |place_1| mostrar el azimut

Hay muchas funciones de geometría en PostGIS, el taller ya cubrió algunas de ellas como ST_AsText, ST_Reverse, ST_EndPoint, etc. Este ejercicio hará uso de una función adicional ST_Azimuth.

Problema

  • Modifique la consulta de Ejercicio 8: Direccionalidad de la geometría de la ruta.

  • Además, obtenga el azimut de la geometría correcta.

  • mantenga la salida pequeña:

    • Incluso que otras columnas se calculan solo como salida:

      • seq, id, seconds y el azimuth

  • Dado que vehicle_net es un subgrafo de ways, realizar el JOIN con ways.

Solución

  • Mover la consulta que obtiene la información adicional en la instrucción WITH.

    • Asígnele el nombre adicional. (línea 9)

  • Las instrucciones SELECT finales obtienen:

    • La información solicitada. (línea 25)

    • Calcula el azimut de route_geom. (línea 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;

Ejercicio: 9 (Capítulo: SQL)

4.4. Crear la función

La siguiente función simplifica (y establece los valores por defecto) cuando se llama a la función del camino más corto de Dijkstra.

Advertencia

pgRouting utiliza una gran sobrecarga de funciones:

  • Evite crear funciones con un nombre de una función de enrutamiento pgRouting

  • Evite el nombre de una función para comenzar con pgr_, _pgr o ST_

4.4.1. Ejercicio 10: Función para una aplicación

Problema

Poner todo junto en una función SQL

  • nombre de función wrk_dijkstra

  • Debe funcionar para cualquier vista dada.

    • Permitir una vista como parámetro

      • Se puede utilizar una tabla si las columnas tienen los nombres correctos.

  • source y target son en términos de osm_id.

  • El resultado debe cumplir los requisitos indicados al principio del capítulo

Solución

  • La firma de la función:

    • Los parámetros de entrada son de la línea 4 a 6.

    • Las columnas de salida van de la línea 7 a 14 (sin resltar).

    • La función devuelve un conjunto. (línea 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
  • El cuerpo de la función:

    • Anexar el nombre de la vista en la línea 7 en la consulta SELECT a pgr_dijkstra.

    • Usar los datos para obtener la ruta de source a target. (línea 8)

    • El JOIN con ways es necesario, ya que las vistas son un subconjunto de ways (línea 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';

Ejercicio: 10 (Capítulo: SQL)

4.4.2. Ejercicio 11: Uso de la función

Problema

  • Probar la función con las tres vistas

  • Desde «Palazzo dei Congressi» hacia Mercato Centrale utilizando el identificador OSM

Solución

  • Usar la función en la instrucción SELECT

  • El primer parámetro cambia en función de la vista que se va a probar

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);

Ejercicio: 11 (Capítulo: SQL)

Utilizar la función

  • Pruebe la función con una combinación de los lugares interesantes:

    • 266939565 Mercato Centrale

    • 2531656518 Stazione di Santa Maria Novella

    • 5020458299 Palazzo dei Congressi

    • 6483155124 Hotel Albani

    • 1798374718 Cattedrale di Santa Maria del Fiore