5. Función pl/pgsql ¶

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	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	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) = (42.2151, 20.729354)
(lat,lon) = (42.2147, 20.7312)

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

SELECT *
INTO points
FROM (
  SELECT 1 AS gid, ST_SetSRID(ST_Point(20.729354, 42.2151), 4326) AS geom
  UNION
  SELECT 2, ST_SetSRID(ST_Point(20.7312, 42.2147), 4326)
  ) 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)

SELECT count(*)
FROM (
  SELECT source
  FROM taxi_net

  UNION

  SELECT target
  FROM taxi_net
) AS id_list;

Para walk_net:

Solución similar a la de la consulta anterior pero en walk_net. (líneas 4 y 9)

SELECT count(*)
FROM (
  SELECT source
  FROM walk_net

  UNION

  SELECT target
  FROM walk_net
) 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 osm_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)

WITH id_list AS (
  SELECT source AS id
  FROM taxi_net

  UNION

  SELECT target
  FROM taxi_net)

SELECT id_list.id, the_geom
INTO taxi_net_vertices_pgr
FROM id_list
JOIN ways_vertices_pgr ON (id_list.id = osm_id);

Para walk_net:

Solución similar a la de la consulta anterior pero en taxi_net. (líneas 3, 8 y 11)

WITH id_list AS (
  SELECT source AS id
  FROM walk_net

  UNION

  SELECT target
  FROM walk_net)

SELECT id_list.id, the_geom
INTO walk_net_vertices_pgr
FROM id_list
JOIN ways_vertices_pgr ON (id_list.id = osm_id);

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: (42.2151, 20.729354).

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)

SELECT osm_id
FROM ways_vertices_pgr
ORDER BY the_geom <-> ST_SetSRID(ST_Point(20.729354, 42.2151), 4326)
LIMIT 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)

SELECT id
FROM vehicle_net_vertices_pgr
ORDER BY the_geom <-> ST_SetSRID(ST_Point(20.729354, 42.2151), 4326)
LIMIT 1;

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

SELECT id
FROM taxi_net_vertices_pgr
ORDER BY the_geom <-> ST_SetSRID(ST_Point(20.729354, 42.2151), 4326)
LIMIT 1;

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

SELECT id
FROM walk_net_vertices_pgr
ORDER BY the_geom <-> ST_SetSRID(ST_Point(20.729354, 42.2151), 4326)
LIMIT 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)
Usando format para construir la consulta. (línea 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)

CREATE OR REPLACE FUNCTION wrk_NearestOSM(
  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 the_geom <-> ST_SetSRID(
                              ST_Point(%3$s, %2$s),
                              4326)
      LIMIT 1
    $$,
    vertex_table, lat, lon)
  INTO result;
  RETURN result;

END
$BODY$
LANGUAGE '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: (42.2151, 20.729354).

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  42.2151, 20.729354);
```
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  42.2151, 20.729354);
```
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  42.2151, 20.729354);
```
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  42.2151, 20.729354);
```

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

Crear la función wrk_fromAtoB.
Siga la descripción dada en Requisitos para el ruteo de A a B.
Utilice funciones especializadas ya creadas wrk_dijkstra y wrk_NearestOSM.
- wrk_NearestOSM creado en Ejercicio 4: Función de vértice más cercano.
  - Recibe el punto en formato de lenguaje natural.
  - Obtiene el identificador OSM necesario por wrk_dijkstra.
- wrk_dijkstra creado en Ejercicio 10: Función para una aplicación.

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)

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

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)

$BODY$
DECLARE
final_query TEXT;
BEGIN
  final_query := format(
    $$
      SELECT *
      FROM wrk_dijkstra(
        '%1$I',
        (SELECT wrk_NearestOSM(
            '%1$I_vertices_pgr',
            %2$s, %3$s)),
        (SELECT wrk_NearestOSM('%1$I_vertices_pgr', %4$s, %5$s))
      )
    $$,
    edges_subset,
    lat1,lon1,
    lat2,lon2);

    IF do_debug THEN
      RAISE WARNING '%', final_query;
    END IF;
    RETURN QUERY EXECUTE final_query;
END;
$BODY$
LANGUAGE '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

El punto de partida es: (lat,lon) = (42.2151, 20.729354)
El punto de destino es: (lat,lon) = (42.2147, 20.7312)
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)
- 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  42.2151, 20.729354,
4  42.2147, 20.7312);
```
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  42.2151, 20.729354,
4  42.2147, 20.7312,
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  42.2151, 20.729354,
6  42.2147, 20.7312);
7
8SELECT *
9FROM example;
```

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