Supported versions:
pgr_withPoints
- Propuesto¶
pgr_withPoints
- Devuelve la ruta más corta de un grafo con vértices temporales adicionales.
Advertencia
Funciones propuestas para la próxima versión mayor.
No están oficialmente en la versión actual.
Es probable que oficialmente formen parte del próximo lanzamiento:
Las funciones hacen uso de ENTEROS y FLOTANTES
Probablemente el nombre no cambie. (Pero todavía puede)
Es posible que la firma no cambie. (Pero todavía puede)
Probablemente la funcionalidad no cambie. (Pero todavía puede)
Se han hecho pruebas con pgTap. Pero tal vez se necesiten más.
Es posible que la documentación necesite un refinamiento.
Disponibilidad
Versión 3.2.0
Nueva función propuesta:
pgr_withPoints(Combinaciones)
Version 2.2.0
Nueva función propuesta
Descripción¶
Modificar el grafo para incluir puntos definidos por points_sql. Usando el algoritmo Dijkstra, busar la o las rutas más cortas
Las principales características son:
El proceso se realiza sólo en aristas con costos positivos.
Los vértices del grafo son:
positivo cuando pertenece a edges_sql
negativo cuando pertenece a points_sql
Los valores se devuelven cuando hay una ruta.
Cuando el vértice inicial y el vértice final son los mismos, no hay ninguna ruta. - El agg_cost los valores no incluidos (v, v) es 0
Cuando el vértice inicial y el vértice final son diferentes y no hay ninguna ruta: - En ell agg_cost los valores no incluidos (u, v) es ∞
Para fines de optimización, se omite cualquier valor duplicado en start_vids o end_vids.
Los valores devueltos se ordenan: - start_vid ascendente - end_vid ascendente
Tiempo de ejecución: \(O(|start\_vids|\times(V \log V + E))\)
Firmas¶
Resumen
[directed, driving_side, details])
(seq, path_seq, [start_pid], [end_pid], node, edge, cost, agg_cost)
Uno a Uno¶
(seq, path_seq, node, edge, cost, agg_cost)
- Ejemplo:
Del punto \(1\) al vértice \(10\) con detalles
SELECT * FROM pgr_withPoints(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
-1, 10,
details => true);
seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
1 | 1 | -1 | 1 | 0.6 | 0
2 | 2 | 6 | 4 | 0.7 | 0.6
3 | 3 | -6 | 4 | 0.3 | 1.3
4 | 4 | 7 | 8 | 1 | 1.6
5 | 5 | 11 | 9 | 1 | 2.6
6 | 6 | 16 | 16 | 1 | 3.6
7 | 7 | 15 | 3 | 1 | 4.6
8 | 8 | 10 | -1 | 0 | 5.6
(8 rows)
Uno a Muchos¶
(seq, path_seq, end_pid, node, edge, cost, agg_cost)
- Ejemplo:
Desde el punto \(1\) al punto \(3\) y al vértice \(7\) en un grafo no dirigido
SELECT * FROM pgr_withPoints(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
-1, ARRAY[-3, 7],
directed => false);
seq | path_seq | end_pid | node | edge | cost | agg_cost
-----+----------+---------+------+------+------+----------
1 | 1 | -3 | -1 | 1 | 0.6 | 0
2 | 2 | -3 | 6 | 4 | 1 | 0.6
3 | 3 | -3 | 7 | 10 | 1 | 1.6
4 | 4 | -3 | 8 | 12 | 0.6 | 2.6
5 | 5 | -3 | -3 | -1 | 0 | 3.2
6 | 1 | 7 | -1 | 1 | 0.6 | 0
7 | 2 | 7 | 6 | 4 | 1 | 0.6
8 | 3 | 7 | 7 | -1 | 0 | 1.6
(8 rows)
Muchos a Uno¶
(seq, path_seq, start_pid, node, edge, cost, agg_cost)
- Ejemplo:
Desde el punto \(1\) y el vértice \(6\) al punto \(3\)
SELECT * FROM pgr_withPoints(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
ARRAY[-1, 6], -3);
seq | path_seq | start_pid | node | edge | cost | agg_cost
-----+----------+-----------+------+------+------+----------
1 | 1 | -1 | -1 | 1 | 0.6 | 0
2 | 2 | -1 | 6 | 4 | 1 | 0.6
3 | 3 | -1 | 7 | 10 | 1 | 1.6
4 | 4 | -1 | 8 | 12 | 0.6 | 2.6
5 | 5 | -1 | -3 | -1 | 0 | 3.2
6 | 1 | 6 | 6 | 4 | 1 | 0
7 | 2 | 6 | 7 | 10 | 1 | 1
8 | 3 | 6 | 8 | 12 | 0.6 | 2
9 | 4 | 6 | -3 | -1 | 0 | 2.6
(9 rows)
Muchos a Muchos¶
(seq, path_seq, start_pid, end_pid, node, edge, cost, agg_cost)
- Ejemplo:
Desde el punto \(1\) y el vértice \(6\) al punto \(3\) y al vértice \(1\)
SELECT * FROM pgr_withPoints(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
ARRAY[-1, 6], ARRAY[-3, 1]);
seq | path_seq | start_pid | end_pid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | -1 | -3 | -1 | 1 | 0.6 | 0
2 | 2 | -1 | -3 | 6 | 4 | 1 | 0.6
3 | 3 | -1 | -3 | 7 | 10 | 1 | 1.6
4 | 4 | -1 | -3 | 8 | 12 | 0.6 | 2.6
5 | 5 | -1 | -3 | -3 | -1 | 0 | 3.2
6 | 1 | -1 | 1 | -1 | 1 | 0.6 | 0
7 | 2 | -1 | 1 | 6 | 4 | 1 | 0.6
8 | 3 | -1 | 1 | 7 | 7 | 1 | 1.6
9 | 4 | -1 | 1 | 3 | 6 | 1 | 2.6
10 | 5 | -1 | 1 | 1 | -1 | 0 | 3.6
11 | 1 | 6 | -3 | 6 | 4 | 1 | 0
12 | 2 | 6 | -3 | 7 | 10 | 1 | 1
13 | 3 | 6 | -3 | 8 | 12 | 0.6 | 2
14 | 4 | 6 | -3 | -3 | -1 | 0 | 2.6
15 | 1 | 6 | 1 | 6 | 4 | 1 | 0
16 | 2 | 6 | 1 | 7 | 7 | 1 | 1
17 | 3 | 6 | 1 | 3 | 6 | 1 | 2
18 | 4 | 6 | 1 | 1 | -1 | 0 | 3
(18 rows)
Combinaciones¶
(seq, path_seq, start_pid, end_pid, node, edge, cost, agg_cost)
- Ejemplo:
Dos combinaciones
Desde el punto \(1\) hasta el vértice \(10\), y desde el vértice \(6\) hasta el punto \(3\) con lado de manejo derecho.
SELECT * FROM pgr_withPoints(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
'SELECT * FROM (VALUES (-1, 10), (6, -3)) AS combinations(source, target)',
driving_side => 'r', details => true);
seq | path_seq | start_pid | end_pid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | -1 | 10 | -1 | 1 | 0.4 | 0
2 | 2 | -1 | 10 | 5 | 1 | 1 | 0.4
3 | 3 | -1 | 10 | 6 | 4 | 0.7 | 1.4
4 | 4 | -1 | 10 | -6 | 4 | 0.3 | 2.1
5 | 5 | -1 | 10 | 7 | 8 | 1 | 2.4
6 | 6 | -1 | 10 | 11 | 9 | 1 | 3.4
7 | 7 | -1 | 10 | 16 | 16 | 1 | 4.4
8 | 8 | -1 | 10 | 15 | 3 | 1 | 5.4
9 | 9 | -1 | 10 | 10 | -1 | 0 | 6.4
10 | 1 | 6 | -3 | 6 | 4 | 0.7 | 0
11 | 2 | 6 | -3 | -6 | 4 | 0.3 | 0.7
12 | 3 | 6 | -3 | 7 | 10 | 1 | 1
13 | 4 | 6 | -3 | 8 | 12 | 0.6 | 2
14 | 5 | 6 | -3 | -3 | -1 | 0 | 2.6
(14 rows)
Parámetros¶
Columna |
Tipo |
Descripción |
---|---|---|
|
SQL de aristas como se describe a continuación |
|
|
SQL de puntos como se describe abajo |
|
|
SQL de combinaciones como se describe a abajo |
|
salida |
|
Identificador del vértice inicial de la ruta. Valor negativo es para identificador de punto. |
salidas |
|
Arreglo de identificadores de vértices iniciales. Valores negativos son para identificadores de puntos. |
destino |
|
Identificador del vértice final de la ruta. Valor negativo es para identificador de punto. |
destinos |
|
Arreglo de identificadores de vértices finales. Valores negativos son para identificadores de puntos. |
Parámetros opcionales¶
Columna |
Tipo |
x Defecto |
Descripción |
---|---|---|---|
|
|
|
|
Parámetros opcionales para Con puntos¶
Parámetro |
Tipo |
x Defecto |
Descripción |
---|---|---|---|
|
|
|
Valor en [
|
|
|
|
|
Consultas Internas¶
SQL aristas¶
Columna |
Tipo |
x Defecto |
Descripción |
---|---|---|---|
|
ENTEROS |
Identificador de la arista. |
|
|
ENTEROS |
Identificador del primer vértice de la arista. |
|
|
ENTEROS |
Identificador del segundo vértice de la arista. |
|
|
FLOTANTES |
Peso de la arista ( |
|
|
FLOTANTES |
-1 |
Peso de la arista (
|
Donde:
- ENTEROS:
SMALLINT
,INTEGER
,BIGINT
- FLOTANTES:
SMALLINT
,INTEGER
,BIGINT
,REAL
,FLOAT
SQL de puntos¶
Parámetro |
Tipo |
x Defecto |
Descripción |
---|---|---|---|
|
ENTEROS |
valor |
Identificador del punto.
|
|
ENTEROS |
Identificador de la arista «más cercana» al punto. |
|
|
FLOTANTES |
El valor en <0,1> que indica la posición relativa desde el primer punto de la arista. |
|
|
|
|
Valor en [
|
Donde:
- ENTEROS:
SMALLINT
,INTEGER
,BIGINT
- FLOTANTES:
SMALLINT
,INTEGER
,BIGINT
,REAL
,FLOAT
SQL Combinaciones¶
Parámetro |
Tipo |
Descripción |
---|---|---|
|
ENTEROS |
Identificador del vértice de salida. |
|
ENTEROS |
Identificador del vértice de llegada. |
Donde:
- ENTEROS:
SMALLINT
,INTEGER
,BIGINT
Columnas de Resultados¶
Devuelve el conjunto de (seq, path_seq [, start_pid] [, end_pid], node, edge, cost, agg_cost)
Columna |
Tipo |
Descripción |
---|---|---|
|
|
Valor secuencial a partir de 1. |
|
|
Posición relativa en la ruta.
|
|
|
Identificador del vértice/punto inicial de la ruta.
|
|
|
Identificador d vértice/punto final del camino.
|
|
|
Identificador del nodo en la ruta de
|
|
|
Identificador de la arsita utilizada para ir del
|
|
|
Costo para atravesar desde
|
|
|
Costo agregado desde
|
Ejemplos Adicionales¶
Variaciones de uso¶
Todos los ejemplos son acerca de viajar desde el punto \(1\) y vértice \(5\) al los puntos \(\{2, 3, 6\}\) y vértices \(\{10, 11\}\)
SELECT *
FROM pgr_withPoints(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
ARRAY[5, -1], ARRAY[-2, -3, -6, 10, 11],
driving_side => 'r', details => true);
seq | path_seq | start_pid | end_pid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | -1 | -6 | -1 | 1 | 0.4 | 0
2 | 2 | -1 | -6 | 5 | 1 | 1 | 0.4
3 | 3 | -1 | -6 | 6 | 4 | 0.7 | 1.4
4 | 4 | -1 | -6 | -6 | -1 | 0 | 2.1
5 | 1 | -1 | -3 | -1 | 1 | 0.4 | 0
6 | 2 | -1 | -3 | 5 | 1 | 1 | 0.4
7 | 3 | -1 | -3 | 6 | 4 | 0.7 | 1.4
8 | 4 | -1 | -3 | -6 | 4 | 0.3 | 2.1
9 | 5 | -1 | -3 | 7 | 10 | 1 | 2.4
10 | 6 | -1 | -3 | 8 | 12 | 0.6 | 3.4
11 | 7 | -1 | -3 | -3 | -1 | 0 | 4
12 | 1 | -1 | -2 | -1 | 1 | 0.4 | 0
13 | 2 | -1 | -2 | 5 | 1 | 1 | 0.4
14 | 3 | -1 | -2 | 6 | 4 | 0.7 | 1.4
15 | 4 | -1 | -2 | -6 | 4 | 0.3 | 2.1
16 | 5 | -1 | -2 | 7 | 8 | 1 | 2.4
17 | 6 | -1 | -2 | 11 | 9 | 1 | 3.4
18 | 7 | -1 | -2 | 16 | 15 | 0.4 | 4.4
19 | 8 | -1 | -2 | -2 | -1 | 0 | 4.8
20 | 1 | -1 | 10 | -1 | 1 | 0.4 | 0
21 | 2 | -1 | 10 | 5 | 1 | 1 | 0.4
22 | 3 | -1 | 10 | 6 | 4 | 0.7 | 1.4
23 | 4 | -1 | 10 | -6 | 4 | 0.3 | 2.1
24 | 5 | -1 | 10 | 7 | 8 | 1 | 2.4
25 | 6 | -1 | 10 | 11 | 9 | 1 | 3.4
26 | 7 | -1 | 10 | 16 | 16 | 1 | 4.4
27 | 8 | -1 | 10 | 15 | 3 | 1 | 5.4
28 | 9 | -1 | 10 | 10 | -1 | 0 | 6.4
29 | 1 | -1 | 11 | -1 | 1 | 0.4 | 0
30 | 2 | -1 | 11 | 5 | 1 | 1 | 0.4
31 | 3 | -1 | 11 | 6 | 4 | 0.7 | 1.4
32 | 4 | -1 | 11 | -6 | 4 | 0.3 | 2.1
33 | 5 | -1 | 11 | 7 | 8 | 1 | 2.4
34 | 6 | -1 | 11 | 11 | -1 | 0 | 3.4
35 | 1 | 5 | -6 | 5 | 1 | 1 | 0
36 | 2 | 5 | -6 | 6 | 4 | 0.7 | 1
37 | 3 | 5 | -6 | -6 | -1 | 0 | 1.7
38 | 1 | 5 | -3 | 5 | 1 | 1 | 0
39 | 2 | 5 | -3 | 6 | 4 | 0.7 | 1
40 | 3 | 5 | -3 | -6 | 4 | 0.3 | 1.7
41 | 4 | 5 | -3 | 7 | 10 | 1 | 2
42 | 5 | 5 | -3 | 8 | 12 | 0.6 | 3
43 | 6 | 5 | -3 | -3 | -1 | 0 | 3.6
44 | 1 | 5 | -2 | 5 | 1 | 1 | 0
45 | 2 | 5 | -2 | 6 | 4 | 0.7 | 1
46 | 3 | 5 | -2 | -6 | 4 | 0.3 | 1.7
47 | 4 | 5 | -2 | 7 | 8 | 1 | 2
48 | 5 | 5 | -2 | 11 | 9 | 1 | 3
49 | 6 | 5 | -2 | 16 | 15 | 0.4 | 4
50 | 7 | 5 | -2 | -2 | -1 | 0 | 4.4
51 | 1 | 5 | 10 | 5 | 1 | 1 | 0
52 | 2 | 5 | 10 | 6 | 4 | 0.7 | 1
53 | 3 | 5 | 10 | -6 | 4 | 0.3 | 1.7
54 | 4 | 5 | 10 | 7 | 8 | 1 | 2
55 | 5 | 5 | 10 | 11 | 9 | 1 | 3
56 | 6 | 5 | 10 | 16 | 16 | 1 | 4
57 | 7 | 5 | 10 | 15 | 3 | 1 | 5
58 | 8 | 5 | 10 | 10 | -1 | 0 | 6
59 | 1 | 5 | 11 | 5 | 1 | 1 | 0
60 | 2 | 5 | 11 | 6 | 4 | 0.7 | 1
61 | 3 | 5 | 11 | -6 | 4 | 0.3 | 1.7
62 | 4 | 5 | 11 | 7 | 8 | 1 | 2
63 | 5 | 5 | 11 | 11 | -1 | 0 | 3
(63 rows)
Pasa enfrente o visita con lado derecho de manejo.¶
Del punto \(6\) al vértice \(11\).
SELECT (start_pid || ' -> ' || end_pid ||' at ' || path_seq || 'th step')::TEXT AS path_at,
CASE WHEN edge = -1 THEN ' visits'
ELSE ' passes in front of'
END as status,
CASE WHEN node < 0 THEN 'Point'
ELSE 'Vertex'
END as is_a,
abs(node) as id
FROM pgr_withPoints(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
ARRAY[5, -1], ARRAY[-2, -3, -6, 10, 11],
driving_side => 'r', details => true)
WHERE node IN (-6, 11);
path_at | status | is_a | id
----------------------+---------------------+--------+----
-1 -> -6 at 4th step | visits | Point | 6
-1 -> -3 at 4th step | passes in front of | Point | 6
-1 -> -2 at 4th step | passes in front of | Point | 6
-1 -> -2 at 6th step | passes in front of | Vertex | 11
-1 -> 10 at 4th step | passes in front of | Point | 6
-1 -> 10 at 6th step | passes in front of | Vertex | 11
-1 -> 11 at 4th step | passes in front of | Point | 6
-1 -> 11 at 6th step | visits | Vertex | 11
5 -> -6 at 3th step | visits | Point | 6
5 -> -3 at 3th step | passes in front of | Point | 6
5 -> -2 at 3th step | passes in front of | Point | 6
5 -> -2 at 5th step | passes in front of | Vertex | 11
5 -> 10 at 3th step | passes in front of | Point | 6
5 -> 10 at 5th step | passes in front of | Vertex | 11
5 -> 11 at 3th step | passes in front of | Point | 6
5 -> 11 at 5th step | visits | Vertex | 11
(16 rows)
Pasa enfrente o visita con lado izquierdo de manejo.¶
Del punto \(6\) al vértice \(11\).
SELECT (start_pid || ' => ' || end_pid ||' at ' || path_seq || 'th step')::TEXT AS path_at,
CASE WHEN edge = -1 THEN ' visits'
ELSE ' passes in front of'
END as status,
CASE WHEN node < 0 THEN 'Point'
ELSE 'Vertex'
END as is_a,
abs(node) as id
FROM pgr_withPoints(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
ARRAY[5, -1], ARRAY[-2, -3, -6, 10, 11],
driving_side => 'l', details => true)
WHERE node IN (-6, 11);
path_at | status | is_a | id
----------------------+---------------------+--------+----
-1 => -6 at 3th step | visits | Point | 6
-1 => -3 at 3th step | passes in front of | Point | 6
-1 => -2 at 3th step | passes in front of | Point | 6
-1 => -2 at 5th step | passes in front of | Vertex | 11
-1 => 10 at 3th step | passes in front of | Point | 6
-1 => 10 at 5th step | passes in front of | Vertex | 11
-1 => 11 at 3th step | passes in front of | Point | 6
-1 => 11 at 5th step | visits | Vertex | 11
5 => -6 at 4th step | visits | Point | 6
5 => -3 at 4th step | passes in front of | Point | 6
5 => -2 at 4th step | passes in front of | Point | 6
5 => -2 at 6th step | passes in front of | Vertex | 11
5 => 10 at 4th step | passes in front of | Point | 6
5 => 10 at 6th step | passes in front of | Vertex | 11
5 => 11 at 4th step | passes in front of | Point | 6
5 => 11 at 6th step | visits | Vertex | 11
(16 rows)
Ver también¶
Índices y tablas