5. Función pl/pgsql

../_images/route.png

Otro tipo de funciones son pl/pgsql. A medida que los requisitos de las aplicaciones se vuelven más complejos, el uso de contenedores de funciones previamente definidas se hace necesario para mayor claridad.

5.1. Requisitos para el ruteo de A a B

Problema del capítulo:

  • Cree una función wrk_fromAtoB que permita el ruteo desde 2 geometrías.

  • La función toma los puntos de latitud/longitud como parámetros de entrada.

  • Devuelve una ruta que incluye una geometría para que si se puede mostrar, por ejemplo, en QGIS.

  • También devolverá algunos otros atributos.

La descripción detallada:

Parámetros de entrada

Columna

tipo

Descripción

edges_subset

REGCLASS

Identificador de nombre de tabla perimetral.

lat1

NUMERIC

La latitud del punto de partida.

lon1

NUMERIC

La longitud del punto de partida

lat2

NUMERIC

La latitud del punto de destino.

lon2

NUMERIC

La longitud del punto de destino.

do_debug

BOOLEAN

Marcar para crear una ADVERTENCIA con la consulta que se ha ejecutado

Columnas de resultados

Columna

Descripción

seq

Para fines ordenamiento

gid

Identificador de bordes que se puede utilizar para unir los resultados a la tabla ways.

name

El nombre de la calle.

azimuth

Entre el nodo inicial y final de una arista.

length

En metros.

minutes

Minutos tomados para atravesar el segmento.

route_geom

La geometría de la carretera con direccionalidad corregida.

Para este capítulo, se utilizarán los siguientes puntos para las pruebas.

  • (lat,lon) = (43.784, 11.2384)

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

Guardar esta información en una tabla:

  • El valor X de una geometría es la longitud.

  • El valor Y de una geometría es la latitud.

  • El lenguaje natural para formar el punto es (latitud, longitud).

  • Para que el procesamiento de geometría forme el punto es (longitud, latitud).

  • líneas 4 y 6 muestran el orden inverso de los pares (lat,lon).

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. La Tabla de Vértices

Los grafos tienen un conjunto de aristas y un conjunto de vértices asociados a él.

osm2pgrouting proporciona la tabla ways_vertices_pgr, asociada a la tabla formas.

Cuando se utiliza un subconjunto de edges como en vehicle_net or in taxi_net se debe utilizar el conjunto de vértices asociados a cada una para, por ejemplo, localizar el vértice más cercano a una ubicación lat/lon.

5.2.1. Ejercicio 1: Número de vértices

Problema

  • Calcular el número de vértices en un grafo.

Dependiendo del grafo calcular el número de vértices de:

  • ways

  • vehicle_net

  • taxi_net

  • walk_net

Solución

  • Para ways

    • osm2pgrouting creó automáticamente la tabla ways_vertices_pgr que contiene todos los vértices en la tabla ways.

    • Uso de la función agregada count. (line 1)

    • Cuente todas las filas de la tabla ways_vertices_pgr. (line 2)

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

    • Extraiga los identificadores de vértices de la columna fuente. (línea 3)

    • Extraiga los identificadores de vértices de la columna target. (línea 8)

    • UNIÓN de ambos resultados (línea 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;
    
  • Para taxi_net:

    • Solución similar a la de la consulta anterior pero en taxi_net. (líneas 4 y 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;
    
  • Para walk_net:

    • Solución similar a la de la consulta anterior pero en walk_net. (líneas 4 y 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;
    

Ejercicio: 1 (Capítulo: pl/pgsql)

5.2.2. Ejercicio 2: Vértices en una tabla

Problema

  • Crear una tabla de vértices.

  • Siga el sufijo que nombra _vertices_pgr.

Dependiendo del grafo cree una tabla de vértices de:

  • ways

  • vehicle_net

  • taxi_net

  • walk_net

La tabla de vértices debe contener:

Columna

Descripción

osm_id

Identificador de OSM del vértice.

the_geom

La geometría del vértice.

Solución

  • Para ways

    • osm2pgrouting creó automáticamente la tabla ways_vertices_pgr que contiene todos los vértices en la tabla ways.

    • Los vértices ya se encuentran en una tabla.

    • El sufijo de tabla que sigue es el solicitado.

    • No es necesario crear una tabla.

    • Las columnas de origen y destino son en términos de columna id de ways_vertices_pgr

  • Para vehicle_net:

    • Uso de la consulta id_list de Ejercicio 1: Número de vértices. (líneas no resaltadas 2 a 8)

    • JOIN con ways_vertices_pgr que tiene el identificador OSM y la información de geometría. (línea 13)

    • Extraer el osm_id y the_geom. (línea 10)

    • Guardar en la tabla vehicle_net_vertices_pgr. (línea 11)

    • Los valores de las columnas de origen y destino tienen los valores sm_id, por lo tanto, la columna id de vehicle_net_vertices_pgr también debe tener los valores osm_id.

     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);
    
  • Para taxi_net:

    • Solución similar a la de la consulta anterior pero en taxi_net. (líneas 3, 8 y 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
    
  • Para walk_net:

    • Solución similar a la de la consulta anterior pero en taxi_net. (líneas 3, 8 y 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
    

Ejercicio: 2 (Capítulo: pl/pgsql)

5.2.3. Ejercicio 3: Vértice más cercano

Problema

  • Calcule el identificador OSM del vértice más cercano a un punto.

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

  • calcular el identificador OSM más cercano del vértice a:

    • vehicle_net_vertices_pgr

    • taxi_net_vertices_pgr

    • walk_net_vertices_pgr

Nota

Las tablas ways y ways_vertices_pgr no se utilizan en las aplicaciones finales

Las vistas net y las tablas vértices se han preparado de tal manera que la instrucción AS ya no se necesita en una función pgRouting.

Solución

  • Para ways_vertices_pgr:

    • Obtener la osm_id. (línea 1)

    • Usando el operador de distancia <-> para ordenar por distancia (línea 3)

    • Obtenga solo la primera fila para obtener el identificador de OSM más cercano del vértice. (línea 4)

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

    • Solución similar a la de la consulta anterior, pero:

      • Extracción de las columnas id. (línea 1)

      • En vehicle_net_vertices_pgr. (línea 2)

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

    • Solución similar a la de la consulta anterior pero en taxi_net_vertices_pgr. (línea 2)

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

    • Solución similar a la de la consulta anterior pero en walk_net_vertices_pgr. (línea 2)

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

Ejercicio: 3 (Capítulo: pl/pgsql)

5.2.4. Ejercicio 4: Función de vértice más cercano

Problema

Cuando las operaciones se ven similares para diferentes tablas, se puede crear una función.

  • Cree una función que calcule el identificador OSM del vértice más cercano a un punto.

  • Nombre de la función: wrk_NearestOSM.

  • Solo tiene que funcionar para las vistas y la tabla aplicación final.

Los parámetros de entrada:

Columna

tipo

Descripción

vertex_table

REGCLASS

Identificador de nombre de tabla.

lat

NUMERIC

La latitud de un punto.

lon

NUMERIC

La longitud de un punto

La salida:

tipo

Descripción

BIGINT

el identificador OSM más cercano a (lat,lon).

Solución

  • La función devuelve solo un valor. (línea 5)

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

    • La estructura de la consulta es similar a las soluciones Ejercicio 3: Vértice más cercano. (líneas 12 a 16)

    • %1$I para el identificador de nombre de tabla. (línea 13)

    • %2$s y %3$s para la latitud y longitud.

      • El punto se forma con (lon/lat) (%3$s, %2$s). (línea 15)

    • Los parámetros adicionales de la función format, son los parámetros de la función que estamos creando. (línea 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';

Ejercicio: 4 (Capítulo: pl/pgsql)

5.2.5. Ejercicio 5: Prueba de la función vértice más cercano

Problema

  • Pruebe la función wrk_NearestOSM.

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

  • El punto es el mismo que en Ejercicio 3: Vértice más cercano.

    • Compruebe que los resultados son los mismos.

  • calcular el identificador OSM más cercano del vértice a:

    • ways_vertices_pgr

    • vehicle_net_vertices_pgr

    • taxi_net_vertices_pgr

    • walk_net_vertices_pgr

Solución

  • Para ways_vertices_pgr:

    • Utilizar la función con ways_vertices_pgr como parámetro vertex_table. (línea 2)

    • Pasar los valores (lat,lon) como segundo y tercer parámetro. (línea 3)

    • El uso de la función en los datos originales no devuelve el identificador de OSM.

      El valor almacenado en la columna id no es el identificador de OSM.

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

    • Solución similar a la de la consulta anterior pero en vehicles_net_vertices_pgr. (líneas 2)

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

    • Solución similar a la de la consulta anterior pero en taxi_net_vertices_pgr. (líneas 2)

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

    • Solución similar a la de la consulta anterior pero en walk_net_vertices_pgr. (líneas 2)

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

Ejercicio: 5 (Capítulo: pl/pgsql)

5.3. función wrk_fromAtoB

En esta sección, se abordará la creación y prueba de la función resonada.

5.3.1. Ejercicio 6: Creación de la función principal

Problema

Solución

Firma de la función:

  • Los parámetros de entrada se resaltan en las líneas 2 a 5.

  • Las columnas de salida no se resaltan en las líneas 7 a 13.

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

Cuerpo de la función:

  • Llamada a la función wrk_dijkstra (línea 8)

    • wrk_dijkstra obtiene muchos de los valores de resultados

    • Los parámetros se pasan en las líneas 9 a 13.

    • Los edges_subset:

      • Los primeros parámetros de la función format es el nombre de la tabla. (línea 16)

      • Se pasa como %1$I. (línea 9)

    • Para el punto de salida:

      • wrk_NearestOSM se utiliza para encontrar el identificador OSM. (línea 10)

        • El nombre de la tabla de vértices se forma con %1$I_vertices_pgr. (línea 11)

        • Los parámetros segundo y tercero de la función formato son %2$s, %3$s. (línea 17)

        • La latitud y la longitud se dan en forma de lenguaje natural. (línea 12)

    • Para el punto de destino:

      • Se construye una consulta similar pero con la información de destino. (línea 13)

      • Cuarto y quinto parámetros de la función formato. (línea 18)

  • Para obtener la consulta construida en forma de advertencia:

    • La ADVERTENCIA se emitirá sólo cuando do_debug sea cierto. (líneas 20 a 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';

Ejercicio: 6 (Capítulo: pl/pgsql)

5.3.2. Ejercicio 7: Uso de la función principal

Vista de las calles para taxis junto con el origen y el destino

Problema

Usar wrk_fromAtoB

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

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

  • Para vehicle_net:

    • Utilizar con el valor predeterminado de do_debug.

  • Para taxi_net:

    • Utilizar con do_debug establecido en true.

  • Para walk_net:

    • Utilizar con el valor predeterminado de do_debug.

    • Almacene los resultados en una tabla.

    • Mostrar el contenido de la tabla.

Nota

La función no está destinada a ser utilizada con formas

Solución

  • Para vehicle_net:

    • El primer parámetro es el nombre de la tabla. (línea 2)

    • Los dos siguientes dos parámetros son la latitud y longitud del punto de partida. (línea 3)

    • Los dos siguientes dos parámetros son la latitud y longitud del punto de destino. (línea 4)

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

    • Similar a la solución anterior, pero con taxi_net (línea 2)

    • Agregar true para obtener la consulta que se ejecuta. (línea 5)

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

    • Similar a una solución anterior, pero con ways (línea 4)

    • Almacene los resultados en una tabla. (línea 2)

    • Mostrar el contenido de la tabla mediante una cláusula SELECT (líneas 8 y 9).

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

Ejercicio: 7 (Capítulo: pl/pgsql)