4. Función SQL

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

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.

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

• Verificación:

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

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

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

Ejercicio: 1 (Capítulo: SQL)

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

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)

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

• Verificación:

  • Cuente las filas en el taxi_net original.

  SELECT 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

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:

  • Similar a Ejercicio 1: Creación de una vista para el ruteo:

    • cost y reverse_cost se expresan en términos de segundos con velocidad de 2 mts/sec. (línea 7)

    • Excluir autopista, primaria. (línea 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');
  

• Verificación:

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

  SELECT count(*) FROM walk_net;
  

Ejercicio: 3 (Capítulo: SQL)

4.2.4. Ejercicio 4: Probar las vistas para el ruteo

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)

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

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

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

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

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;

Ejercicio: 5 (Capítulo: SQL)

4.3. Manejo de geometría

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

Problema

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

  • 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

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)

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;

Ejercicio: 6 (Capítulo: SQL)

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

Problema

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

  • 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 formato binario con el nombre route_geom

Solución

• La consulta de Ejercicio 6: Geometría de la ruta (legible para humanos) utilizada;

• La cláusula SELECT contiene:

  • the_geom, incluido el cambio de nombre (línea 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;

Ejercicio: 7 (Capítulo: SQL)

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

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

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

   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)

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)

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;

Ejercicio: 8 (Capítulo: SQL)

4.3.4. Ejercicio 9: Uso de la geometría

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)

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;

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)

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

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

$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';

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

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

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

SELECT *
FROM 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