Guía de migración¶
Varias funciones están teniendo cambios en las firmas, y/o han sido remplazadas por nuevas funciones.
Resultados pueden ser diferentes dado los cambios.
Advertencia
Todas las funciones obsoletas serán removidas en las siguiente versión mayor 4.0.0
Migration to standardized columns¶
There has been an effort to standardize function output columns names and types.
Columnas de resultados de las funciones de costes
(start_vid, end_vid, agg_cost)
Result columns for single path functions
(seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
Columnas de resultados para funciones de árbol de expansión
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)
Function |
Guía de migración |
|---|---|
Distinto en la versión 3.5.0: pgr_dijkstra [1] |
|
Distinto en la versión 3.6.0: pgr_bdAstar [1] |
|
Distinto en la versión 3.6.0: pgr_drivingDistance [1] |
|
Distinto en la versión 3.6.0: pgr_withPointsDD [2] |
|
Distinto en la versión 3.7.0: pgr_kruskalBFS [1] |
|
Distinto en la versión 3.7.0: pgr_kruskalDD [1] |
|
Distinto en la versión 3.7.0: pgr_kruskalDFS [1] |
|
Distinto en la versión 3.7.0: pgr_primBFS [1] |
|
Distinto en la versión 3.7.0: pgr_primDD [1] |
|
Distinto en la versión 3.7.0: pgr_primDFS [1] |
|
Distinto en la versión 4.0.0: pgr_bdDijkstra [1] |
|
Distinto en la versión 4.0.0: pgr_bellmanFord - Experimental [3] |
|
Distinto en la versión 4.0.0: pgr_binaryBreadthFirstSearch - Experimental [3] |
|
Distinto en la versión 4.0.0: pgr_dagShortestPath - Experimental [3] |
|
Distinto en la versión 4.0.0: pgr_edwardMoore - Experimental [3] |
|
Distinto en la versión 4.0.0: pgr_withPoints [2] |
|
Distinto en la versión 4.0.0: pgr_withPointsCost [2] |
|
Distinto en la versión 4.0.0: pgr_withPointsCostMatrix [2] |
Migration of cost functions¶
The standardized Columnas de resultados de las funciones de costes are
(start_vid, end_vid, agg_cost)
The following functions need to be migrated when they are being used in an application.
Function |
Version |
From |
|---|---|---|
|
v < 4.0 |
|
|
v < 4.0 |
|
to (start_vid, end_vid, agg_cost)
Migration of (start_pid, end_pid, agg_cost)
Firmas que serán migradas:
Uno a Uno
Uno a Muchos
Muchos a Uno
Muchos a Muchos
Combinaciones
Advertencia
Breaking change
If using pgr_withPointsCost: column names must be changed after updating
pgRouting
New output columns are (start_vid, end_vid, agg_cost)
To get the old version column names: rename start_vid to start_pid and
end_vid to end_pid.
Examples for One to One for cost functions¶
Using pgr_withPointsCost
Migrating this v3.8 example.
- de:
(start_pid, end_pid, agg_cost)- a:
(start_vid, end_vid, agg_cost)
SELECT * FROM pgr_withPointsCost(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
-1, 10, 'r');
start_vid | end_vid | agg_cost
-----------+---------+----------
-1 | 10 | 6.4
(1 row)
To get the old version column names: rename start_vid to start_pid and
end_vid to end_pid.
SELECT start_vid AS start_pid, end_vid AS end_pid, agg_cost
FROM pgr_withPointsCost(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
-1, 10, 'r');
start_pid | end_pid | agg_cost
-----------+---------+----------
-1 | 10 | 6.4
(1 row)
Advertencia
Breaking change
If using pgr_withPointsCost: column names must be changed after updating
pgRouting
Examples for One to Many for cost functions¶
Using pgr_withPointsCost
Migrating this v3.8 example.
- de:
(start_pid, end_pid, agg_cost)- a:
(start_vid, end_vid, agg_cost)
SELECT * FROM pgr_withPointsCost(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
-1, ARRAY[-3, 7],
'b', directed => false);
start_vid | end_vid | agg_cost
-----------+---------+----------
-1 | -3 | 3.2
-1 | 7 | 1.6
(2 rows)
To get the old version column names: rename start_vid to start_pid and
end_vid to end_pid.
SELECT start_vid AS start_pid, end_vid AS end_pid, agg_cost
FROM pgr_withPointsCost(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
-1, ARRAY[-3, 7],
'b', directed => false);
start_pid | end_pid | agg_cost
-----------+---------+----------
-1 | -3 | 3.2
-1 | 7 | 1.6
(2 rows)
Advertencia
Breaking change
If using pgr_withPointsCost: column names must be changed after updating
pgRouting
Examples for Many to One for cost functions¶
Using pgr_withPointsCost
Migrating this v3.8 example.
- de:
(start_pid, end_pid, agg_cost)- a:
(start_vid, end_vid, agg_cost)
SELECT * FROM pgr_withPointsCost(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
ARRAY[-1, 6], -3, 'r');
start_vid | end_vid | agg_cost
-----------+---------+----------
-1 | -3 | 4
6 | -3 | 2.6
(2 rows)
To get the old version column names: rename start_vid to start_pid and
end_vid to end_pid.
SELECT start_vid AS start_pid, end_vid AS end_pid, agg_cost
FROM pgr_withPointsCost(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
ARRAY[-1, 6], -3, 'r');
start_pid | end_pid | agg_cost
-----------+---------+----------
-1 | -3 | 4
6 | -3 | 2.6
(2 rows)
Advertencia
Breaking change
If using pgr_withPointsCost: column names must be changed after updating
pgRouting
Examples for Many to Many for cost functions¶
Using pgr_withPointsCost
Migrating this v3.8 example.
- de:
(start_pid, end_pid, agg_cost)- a:
(start_vid, end_vid, agg_cost)
SELECT * FROM pgr_withPointsCost(
'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], 'r');
start_vid | end_vid | agg_cost
-----------+---------+----------
-1 | -3 | 4
-1 | 1 | 4.4
6 | -3 | 2.6
6 | 1 | 3
(4 rows)
To get the old version column names: rename start_vid to start_pid and
end_vid to end_pid.
SELECT start_vid AS start_pid, end_vid AS end_pid, agg_cost
FROM pgr_withPointsCost(
'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], 'r');
start_pid | end_pid | agg_cost
-----------+---------+----------
-1 | -3 | 4
-1 | 1 | 4.4
6 | -3 | 2.6
6 | 1 | 3
(4 rows)
Using pgr_withPointsCost
Advertencia
Breaking change
If using pgr_withPointsCost: column names must be changed after updating
pgRouting
Examples for Combinations for cost functions¶
Using pgr_withPointsCost
Migrating this v3.8 example.
- de:
(start_pid, end_pid, agg_cost)- a:
(start_vid, end_vid, agg_cost)
SELECT * FROM pgr_withPointsCost(
'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)',
'r');
start_vid | end_vid | agg_cost
-----------+---------+----------
-1 | 10 | 6.4
6 | -3 | 2.6
(2 rows)
To get the old version column names: rename start_vid to start_pid and
end_vid to end_pid.
SELECT start_vid AS start_pid, end_vid AS end_pid, agg_cost
FROM pgr_withPointsCost(
'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)',
'r');
start_pid | end_pid | agg_cost
-----------+---------+----------
-1 | 10 | 6.4
6 | -3 | 2.6
(2 rows)
Advertencia
Breaking change
If using pgr_withPointsCost: column names must be changed after updating
pgRouting
Migration of single path functions¶
THe standardized Result columns for single path functions are (seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
The following functions need to be migrated when they are being used in an application.
Function |
Version |
From |
|---|---|---|
|
v < 3.5 |
|
|
v < 3.6 |
|
|
v < 4.0 |
|
|
v < 4.0 |
|
|
v < 4.0 |
|
|
v < 4.0 |
|
|
v < 4.0 |
|
- a:
(seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
Migration of (seq, path_seq, [start_vid], [end_vid], node, edge, cost, agg_cost)
Firmas que serán migradas:
Uno a Uno
Uno a Muchos
Muchos a Uno
Before updating pgRouting, enumerate the corresponding columns of the signature
Skip when applicable,
start_vidSkip when applicable,
end_vid
Migration of (seq, path_seq, [start_pid], [end_pid], node, edge, cost, agg_cost)
Firmas que serán migradas:
Uno a Uno
Uno a Muchos
Muchos a Uno
Muchos a Muchos
Combinaciones
To get the old version column names, depending on the signature:
Filter out the columns:
start_vidand/orend_vidRename the columns:
start_vidtostart_pidend_vidtoend_pid
Advertencia
Breaking change
If using pgr_withPoints: column names must be changed after updating
pgRouting
Migration of (seq, path_seq, node, edge, cost, agg_cost)
Firmas que serán migradas:
Uno a Uno
Uno a Muchos
Muchos a Uno
Muchos a Muchos
Combinaciones
To get the old version column names:
Filter out the columns:
start_vidandend_vid
Examples with One to One with one route result¶
Using pgr_aStar
Migrating this v3.5 example.
SELECT * FROM pgr_aStar(
$$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
6, 10);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 6 | 10 | 6 | 4 | 1 | 0
2 | 2 | 6 | 10 | 7 | 8 | 1 | 1
3 | 3 | 6 | 10 | 11 | 9 | 1 | 2
4 | 4 | 6 | 10 | 16 | 16 | 1 | 3
5 | 5 | 6 | 10 | 15 | 3 | 1 | 4
6 | 6 | 6 | 10 | 10 | -1 | 0 | 5
(6 rows)
Before updating pgRouting enumerate the columns: (seq, path_seq, node, edge, cost, agg_cost)
SELECT seq, path_seq, node, edge, cost, agg_cost FROM pgr_aStar(
$$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
6, 10);
seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
1 | 1 | 6 | 4 | 1 | 0
2 | 2 | 7 | 8 | 1 | 1
3 | 3 | 11 | 9 | 1 | 2
4 | 4 | 16 | 16 | 1 | 3
5 | 5 | 15 | 3 | 1 | 4
6 | 6 | 10 | -1 | 0 | 5
(6 rows)
Using pgr_bdDijkstra
Migrating this v3.8 example.
SELECT * FROM pgr_bdDijkstra(
'select id, source, target, cost, reverse_cost from edges',
6, 10, true);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 6 | 10 | 6 | 4 | 1 | 0
2 | 2 | 6 | 10 | 7 | 8 | 1 | 1
3 | 3 | 6 | 10 | 11 | 9 | 1 | 2
4 | 4 | 6 | 10 | 16 | 16 | 1 | 3
5 | 5 | 6 | 10 | 15 | 3 | 1 | 4
6 | 6 | 6 | 10 | 10 | -1 | 0 | 5
(6 rows)
Before updating pgRouting enumerate the columns: (seq, path_seq, node, edge, cost, agg_cost)
SELECT seq, path_seq, node, edge, cost, agg_cost
FROM pgr_bdDijkstra(
'select id, source, target, cost, reverse_cost from edges',
6, 10, true);
seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
1 | 1 | 6 | 4 | 1 | 0
2 | 2 | 7 | 8 | 1 | 1
3 | 3 | 11 | 9 | 1 | 2
4 | 4 | 16 | 16 | 1 | 3
5 | 5 | 15 | 3 | 1 | 4
6 | 6 | 10 | -1 | 0 | 5
(6 rows)
Using pgr_DAGshortestPath
Migrating this v3.8 example.
SELECT * FROM pgr_DAGshortestPath(
'select id, source, target, cost from edges',
5, 11);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 5 | 11 | 5 | 1 | 1 | 0
2 | 2 | 5 | 11 | 6 | 4 | 1 | 1
3 | 3 | 5 | 11 | 7 | 8 | 1 | 2
4 | 4 | 5 | 11 | 11 | -1 | 0 | 3
(4 rows)
Before updating pgRouting enumerate the columns: (seq, path_seq, node, edge, cost, agg_cost)
SELECT seq, path_seq, node, edge, cost, agg_cost
FROM pgr_DAGshortestPath(
'select id, source, target, cost from edges',
5, 11);
seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
1 | 1 | 5 | 1 | 1 | 0
2 | 2 | 6 | 4 | 1 | 1
3 | 3 | 7 | 8 | 1 | 2
4 | 4 | 11 | -1 | 0 | 3
(4 rows)
Nota
This applies to all signatures of pgr_DAGshortestPath
Examples for One to Many with one route result¶
Using pgr_bdAstar
Migrating this v3.5 example.
SELECT * FROM pgr_bdAstar(
$$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
6, ARRAY[10, 12],
heuristic => 3, factor := 3.5);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 6 | 10 | 6 | 4 | 1 | 0
2 | 2 | 6 | 10 | 7 | 8 | 1 | 1
3 | 3 | 6 | 10 | 11 | 9 | 1 | 2
4 | 4 | 6 | 10 | 16 | 16 | 1 | 3
5 | 5 | 6 | 10 | 15 | 3 | 1 | 4
6 | 6 | 6 | 10 | 10 | -1 | 0 | 5
7 | 1 | 6 | 12 | 6 | 4 | 1 | 0
8 | 2 | 6 | 12 | 7 | 8 | 1 | 1
9 | 3 | 6 | 12 | 11 | 11 | 1 | 2
10 | 4 | 6 | 12 | 12 | -1 | 0 | 3
(10 rows)
Before updating pgRouting enumerate the columns: (seq, path_seq, end_vid, node, edge, cost, agg_cost)
SELECT * FROM pgr_bdAstar(
$$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
6, ARRAY[10, 12],
heuristic => 3, factor := 3.5);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 6 | 10 | 6 | 4 | 1 | 0
2 | 2 | 6 | 10 | 7 | 8 | 1 | 1
3 | 3 | 6 | 10 | 11 | 9 | 1 | 2
4 | 4 | 6 | 10 | 16 | 16 | 1 | 3
5 | 5 | 6 | 10 | 15 | 3 | 1 | 4
6 | 6 | 6 | 10 | 10 | -1 | 0 | 5
7 | 1 | 6 | 12 | 6 | 4 | 1 | 0
8 | 2 | 6 | 12 | 7 | 8 | 1 | 1
9 | 3 | 6 | 12 | 11 | 11 | 1 | 2
10 | 4 | 6 | 12 | 12 | -1 | 0 | 3
(10 rows)
Using pgr_withPoints
Migrating this v3.8 example.
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],
'b', directed => false);
seq | path_seq | start_vid | end_vid | 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 | 7 | -1 | 1 | 0.6 | 0
7 | 2 | -1 | 7 | 6 | 4 | 1 | 0.6
8 | 3 | -1 | 7 | 7 | -1 | 0 | 1.6
(8 rows)
Advertencia
Breaking change
If using pgr_withPoints: column names must be changed after updating
pgRouting
- de:
(seq, path_seq, end_pid, node, edge, cost, agg_cost)- a:
(seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
To get the old signature column names: filter out the
column start_vid and rename end_vid to end_pid.
SELECT seq, path_seq, end_vid AS end_pid, node, edge, cost, agg_cost
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],
'b', 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)
Examples for Many to One with one route result¶
Using pgr_bdDijkstra
Migrating this v3.8 example.
SELECT * FROM pgr_bdDijkstra(
'select id, source, target, cost, reverse_cost from edges',
ARRAY[6, 1], 17);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 1 | 17 | 1 | 6 | 1 | 0
2 | 2 | 1 | 17 | 3 | 7 | 1 | 1
3 | 3 | 1 | 17 | 7 | 8 | 1 | 2
4 | 4 | 1 | 17 | 11 | 11 | 1 | 3
5 | 5 | 1 | 17 | 12 | 13 | 1 | 4
6 | 6 | 1 | 17 | 17 | -1 | 0 | 5
7 | 1 | 6 | 17 | 6 | 4 | 1 | 0
8 | 2 | 6 | 17 | 7 | 8 | 1 | 1
9 | 3 | 6 | 17 | 11 | 11 | 1 | 2
10 | 4 | 6 | 17 | 12 | 13 | 1 | 3
11 | 5 | 6 | 17 | 17 | -1 | 0 | 4
(11 rows)
Before updating pgRouting enumerate the columns: (seq, path_seq, start_vid, node, edge, cost, agg_cost)
SELECT seq, path_seq, start_vid AS start_pid, node, edge, cost, agg_cost
FROM pgr_bdDijkstra(
'select id, source, target, cost, reverse_cost from edges',
ARRAY[6, 1], 17);
seq | path_seq | start_pid | node | edge | cost | agg_cost
-----+----------+-----------+------+------+------+----------
1 | 1 | 1 | 1 | 6 | 1 | 0
2 | 2 | 1 | 3 | 7 | 1 | 1
3 | 3 | 1 | 7 | 8 | 1 | 2
4 | 4 | 1 | 11 | 11 | 1 | 3
5 | 5 | 1 | 12 | 13 | 1 | 4
6 | 6 | 1 | 17 | -1 | 0 | 5
7 | 1 | 6 | 6 | 4 | 1 | 0
8 | 2 | 6 | 7 | 8 | 1 | 1
9 | 3 | 6 | 11 | 11 | 1 | 2
10 | 4 | 6 | 12 | 13 | 1 | 3
11 | 5 | 6 | 17 | -1 | 0 | 4
(11 rows)
Using pgr_dijkstra
Migrating this v3.4 example.
SELECT * FROM pgr_dijkstra(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
ARRAY[6, 1], 17);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 1 | 17 | 1 | 6 | 1 | 0
2 | 2 | 1 | 17 | 3 | 7 | 1 | 1
3 | 3 | 1 | 17 | 7 | 8 | 1 | 2
4 | 4 | 1 | 17 | 11 | 11 | 1 | 3
5 | 5 | 1 | 17 | 12 | 13 | 1 | 4
6 | 6 | 1 | 17 | 17 | -1 | 0 | 5
7 | 1 | 6 | 17 | 6 | 4 | 1 | 0
8 | 2 | 6 | 17 | 7 | 8 | 1 | 1
9 | 3 | 6 | 17 | 11 | 11 | 1 | 2
10 | 4 | 6 | 17 | 12 | 13 | 1 | 3
11 | 5 | 6 | 17 | 17 | -1 | 0 | 4
(11 rows)
Before updating pgRouting enumerate the columns: (seq, path_seq, start_vid, node, edge, cost, agg_cost)
SELECT seq, path_seq, start_vid, node, edge, cost, agg_cost FROM pgr_dijkstra(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
ARRAY[6, 1], 17);
seq | path_seq | start_vid | node | edge | cost | agg_cost
-----+----------+-----------+------+------+------+----------
1 | 1 | 1 | 1 | 6 | 1 | 0
2 | 2 | 1 | 3 | 7 | 1 | 1
3 | 3 | 1 | 7 | 8 | 1 | 2
4 | 4 | 1 | 11 | 11 | 1 | 3
5 | 5 | 1 | 12 | 13 | 1 | 4
6 | 6 | 1 | 17 | -1 | 0 | 5
7 | 1 | 6 | 6 | 4 | 1 | 0
8 | 2 | 6 | 7 | 8 | 1 | 1
9 | 3 | 6 | 11 | 11 | 1 | 2
10 | 4 | 6 | 12 | 13 | 1 | 3
11 | 5 | 6 | 17 | -1 | 0 | 4
(11 rows)
Examples for Many to Many with one route result¶
Using pgr_withPoints
Migrating this v3.8 example.
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], 'r');
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | -1 | -3 | -1 | 1 | 1.4 | 0
2 | 2 | -1 | -3 | 6 | 4 | 1 | 1.4
3 | 3 | -1 | -3 | 7 | 10 | 1 | 2.4
4 | 4 | -1 | -3 | 8 | 12 | 0.6 | 3.4
5 | 5 | -1 | -3 | -3 | -1 | 0 | 4
6 | 1 | -1 | 1 | -1 | 1 | 1.4 | 0
7 | 2 | -1 | 1 | 6 | 4 | 1 | 1.4
8 | 3 | -1 | 1 | 7 | 7 | 1 | 2.4
9 | 4 | -1 | 1 | 3 | 6 | 1 | 3.4
10 | 5 | -1 | 1 | 1 | -1 | 0 | 4.4
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)
Advertencia
Breaking change
If using pgr_withPoints: column names must be changed after updating
pgRouting
- de:
(seq, path_seq, start_pid, end_pid, node, edge, cost, agg_cost)- a:
(seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
To get the old version column names: rename start_vid to start_pid and
end_vid to end_pid.
SELECT seq, path_seq, start_vid AS start_pid, end_vid AS end_pid,node, edge, cost, agg_cost
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], 'r');
seq | path_seq | start_pid | end_pid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | -1 | -3 | -1 | 1 | 1.4 | 0
2 | 2 | -1 | -3 | 6 | 4 | 1 | 1.4
3 | 3 | -1 | -3 | 7 | 10 | 1 | 2.4
4 | 4 | -1 | -3 | 8 | 12 | 0.6 | 3.4
5 | 5 | -1 | -3 | -3 | -1 | 0 | 4
6 | 1 | -1 | 1 | -1 | 1 | 1.4 | 0
7 | 2 | -1 | 1 | 6 | 4 | 1 | 1.4
8 | 3 | -1 | 1 | 7 | 7 | 1 | 2.4
9 | 4 | -1 | 1 | 3 | 6 | 1 | 3.4
10 | 5 | -1 | 1 | 1 | -1 | 0 | 4.4
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)
Examples for combinations with one route result¶
Using pgr_withPoints
Migrating this v3.8 `this example.
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)',
'r', details => true);
seq | path_seq | start_vid | end_vid | 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)
Advertencia
Breaking change
If using pgr_withPoints: column names must be changed after updating
pgRouting
- de:
(seq, path_seq, start_pid, end_pid, node, edge, cost, agg_cost)- a:
(seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
To get the old version column names: rename start_vid to start_pid and
end_vid to end_pid.
SELECT seq, path_seq, start_vid AS start_pid, end_vid AS end_pid,node, edge, cost, agg_cost
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)',
'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)
Migration of spanning tree functions¶
The standardized Columnas de resultados para funciones de árbol de expansión are (seq, depth, start_vid, pred, node, edge, cost, agg_cost)
Function |
Version |
From |
|---|---|---|
|
v < 3.7 |
|
|
v < 3.7 |
|
|
v < 3.7 |
|
|
v < 3.7 |
|
|
v < 3.7 |
|
|
v < 3.7 |
|
|
v < 3.6 |
|
|
v < 3.6 |
|
to (seq, depth, start_vid, pred, node, edge, cost, agg_cost)
Migration of (seq, depth, start_vid, node, edge, cost, agg_cost)
Firmas que serán migradas:
Vértice único
Múltiples vértices
Before updating pgRouting enumerate the columns: (seq, depth, start_vid, node, edge, cost, agg_cost)
Migration of (seq, [from_v,] node, edge, cost, agg_cost)
Firmas que serán migradas:
Vértice único
Múltiples vértices
Migration depends on the signature.
For single vertex:
Before updating pgRouting, enumerate
(seq, path_seq, node, edge, cost, agg_cost)columns
For multiple vertices:
Advertencia
Breaking change
If using pgr_drivingDistance with multiple vertices: column names must be
changed after updating pgRouting.
To get the old version column names (seq, from_v, node, edge, cost, agg_cost)
Enumerate
(seq, from_v, node, edge, cost, agg_cost)Rename
start_vidtofrom_v.
Migration of (seq, [start_vid], node, edge, cost, agg_cost)
Firmas que serán migradas:
Vértice único
Múltiples vértices
Advertencia
Breaking change
If using pgr_withPointsDD: function call must be changed after updating
pgRouting.
pgr_withPointsDD’s parameter driving_side changed from named optional
to unnamed positional parameter (position 5 in the function call) and its
validity differ for directed and undirected graphs.
Migration depends on the signature.
For pgr_withPointsDD Single vertex
To get the * Output columns were (seq, node, edge, cost, agg_cost)
* Does not have start_vid, pred and depth result columns.
* driving_side parameter was named optional now it is compulsory unnamed.
For pgr_withPointsDD (Multiple vertices)
Las columnas de salida eran
(seq, start_vid, node, edge, cost, agg_cost)No tiene columnas de resultados
depthypred.El parámetro
driving_sideera opcional ahora es obligatorio sin nombre.
Validity of driving_side:
En un grafo dirigido
bpodía usarse como lado de manejoOn undirected graph
r,lcould be used as driving side
Después de la migración
Tener en cuenta la existencia de las columnas adicionales de resultados.
Output columns are
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)
El parámetro lado de manjeo es obligatorio sin nombre, y los valores válidos difieren para grafos dirigidos y no dirigidos.
No tiene un valor de facto.
En grafos dirigidos: Los valores válidos son [
r,R,l,L]En grafos no dirigidos: Los valores válidos son [
b,B]El uso de un valor inválido lanza un
ERROR.
Examples for single vertex¶
Using pgr_kruskalDD¶
Migrating this v3.6 example.
SELECT * FROM pgr_kruskalDD(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
6, 3.5);
seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
1 | 0 | 6 | 6 | 6 | -1 | 0 | 0
2 | 1 | 6 | 6 | 5 | 1 | 1 | 1
3 | 1 | 6 | 6 | 10 | 2 | 1 | 1
4 | 2 | 6 | 10 | 15 | 3 | 1 | 2
5 | 3 | 6 | 15 | 16 | 16 | 1 | 3
(5 rows)
Before updating pgRouting enumerate the columns: (seq, depth, start_vid, node, edge, cost, agg_cost).
SELECT seq, depth, start_vid, node, edge, cost, agg_cost
FROM pgr_kruskalDD(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
6, 3.5);
seq | depth | start_vid | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+----------
1 | 0 | 6 | 6 | -1 | 0 | 0
2 | 1 | 6 | 5 | 1 | 1 | 1
3 | 1 | 6 | 10 | 2 | 1 | 1
4 | 2 | 6 | 15 | 3 | 1 | 2
5 | 3 | 6 | 16 | 16 | 1 | 3
(5 rows)
Using pgr_primBFS¶
Migrating this v3.6 example.
SELECT * FROM pgr_primBFS(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
6);
seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
1 | 0 | 6 | 6 | 6 | -1 | 0 | 0
2 | 1 | 6 | 6 | 5 | 1 | 1 | 1
3 | 1 | 6 | 6 | 10 | 2 | 1 | 1
4 | 1 | 6 | 6 | 7 | 4 | 1 | 1
5 | 2 | 6 | 10 | 15 | 3 | 1 | 2
6 | 2 | 6 | 10 | 11 | 5 | 1 | 2
7 | 2 | 6 | 7 | 3 | 7 | 1 | 2
8 | 2 | 6 | 7 | 8 | 10 | 1 | 2
9 | 3 | 6 | 11 | 16 | 9 | 1 | 3
10 | 3 | 6 | 11 | 12 | 11 | 1 | 3
11 | 3 | 6 | 3 | 1 | 6 | 1 | 3
12 | 3 | 6 | 8 | 9 | 14 | 1 | 3
13 | 4 | 6 | 12 | 17 | 13 | 1 | 4
(13 rows)
Before updating pgRouting enumerate the columns: (seq, depth, start_vid, node, edge, cost, agg_cost).
SELECT seq, depth, start_vid, node, edge, cost, agg_cost
FROM pgr_primBFS(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
6);
seq | depth | start_vid | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+----------
1 | 0 | 6 | 6 | -1 | 0 | 0
2 | 1 | 6 | 5 | 1 | 1 | 1
3 | 1 | 6 | 10 | 2 | 1 | 1
4 | 1 | 6 | 7 | 4 | 1 | 1
5 | 2 | 6 | 15 | 3 | 1 | 2
6 | 2 | 6 | 11 | 5 | 1 | 2
7 | 2 | 6 | 3 | 7 | 1 | 2
8 | 2 | 6 | 8 | 10 | 1 | 2
9 | 3 | 6 | 16 | 9 | 1 | 3
10 | 3 | 6 | 12 | 11 | 1 | 3
11 | 3 | 6 | 1 | 6 | 1 | 3
12 | 3 | 6 | 9 | 14 | 1 | 3
13 | 4 | 6 | 17 | 13 | 1 | 4
(13 rows)
Using pgr_drivingDistance¶
Migrating this v3.5 example.
SELECT * FROM pgr_drivingDistance(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
11, 3.0);
seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
1 | 0 | 11 | 11 | 11 | -1 | 0 | 0
2 | 1 | 11 | 11 | 7 | 8 | 1 | 1
3 | 1 | 11 | 11 | 12 | 11 | 1 | 1
4 | 1 | 11 | 11 | 16 | 9 | 1 | 1
5 | 2 | 11 | 7 | 3 | 7 | 1 | 2
6 | 2 | 11 | 7 | 6 | 4 | 1 | 2
7 | 2 | 11 | 7 | 8 | 10 | 1 | 2
8 | 2 | 11 | 16 | 15 | 16 | 1 | 2
9 | 2 | 11 | 16 | 17 | 15 | 1 | 2
10 | 3 | 11 | 3 | 1 | 6 | 1 | 3
11 | 3 | 11 | 6 | 5 | 1 | 1 | 3
12 | 3 | 11 | 8 | 9 | 14 | 1 | 3
13 | 3 | 11 | 15 | 10 | 3 | 1 | 3
(13 rows)
Before updating pgRouting, enumerate (seq, node, edge, cost, agg_cost) columns
SELECT seq, node, edge, cost, agg_cost
FROM pgr_drivingDistance(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
11, 3.0);
seq | node | edge | cost | agg_cost
-----+------+------+------+----------
1 | 11 | -1 | 0 | 0
2 | 7 | 8 | 1 | 1
3 | 12 | 11 | 1 | 1
4 | 16 | 9 | 1 | 1
5 | 3 | 7 | 1 | 2
6 | 6 | 4 | 1 | 2
7 | 8 | 10 | 1 | 2
8 | 15 | 16 | 1 | 2
9 | 17 | 15 | 1 | 2
10 | 1 | 6 | 1 | 3
11 | 5 | 1 | 1 | 3
12 | 9 | 14 | 1 | 3
13 | 10 | 3 | 1 | 3
(13 rows)
Using pgr_withPointsDD¶
Migrating this v3.5 example.
Advertencia
Breaking change
If using pgr_withPointsDD: function call must be changed after updating
pgRouting.
pgr_withPointsDD’s parameter driving_side changed from named optional
to unnamed positional parameter (position 5 in the function call) and its
validity differ for directed and undirected graphs.
SELECT * FROM pgr_withPointsDD(
$$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
$$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
-1, 3.3, 'r', details =>true);
seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
1 | 0 | -1 | -1 | -1 | -1 | 0 | 0
2 | 1 | -1 | -1 | 5 | 1 | 0.4 | 0.4
3 | 2 | -1 | 5 | 6 | 1 | 1 | 1.4
4 | 3 | -1 | 6 | -6 | 4 | 0.7 | 2.1
5 | 4 | -1 | -6 | 7 | 4 | 0.3 | 2.4
(5 rows)
Before updating pgRouting, enumerate
(seq, node, edge, cost, agg_cost)columnsAfter updating pgROuting use an unnamed valid value for driving side after the distance parameter.
SELECT seq, node, edge, cost, agg_cost FROM pgr_withPointsDD(
$$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
$$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
-1, 3.3, 'r', details => true);
seq | node | edge | cost | agg_cost
-----+------+------+------+----------
1 | -1 | -1 | 0 | 0
2 | 5 | 1 | 0.4 | 0.4
3 | 6 | 1 | 1 | 1.4
4 | -6 | 4 | 0.7 | 2.1
5 | 7 | 4 | 0.3 | 2.4
(5 rows)
Examples for multiple vertices¶
Using pgr_kruskalDFS
Migrating this v3.6 example.
SELECT * FROM pgr_kruskalDFS(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
ARRAY[9, 6], max_depth => 3);
seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
1 | 0 | 6 | 6 | 6 | -1 | 0 | 0
2 | 1 | 6 | 6 | 5 | 1 | 1 | 1
3 | 1 | 6 | 6 | 10 | 2 | 1 | 1
4 | 2 | 6 | 10 | 15 | 3 | 1 | 2
5 | 3 | 6 | 15 | 16 | 16 | 1 | 3
6 | 0 | 9 | 9 | 9 | -1 | 0 | 0
7 | 1 | 9 | 9 | 8 | 14 | 1 | 1
8 | 2 | 9 | 8 | 7 | 10 | 1 | 2
9 | 3 | 9 | 7 | 3 | 7 | 1 | 3
10 | 2 | 9 | 8 | 12 | 12 | 1 | 2
11 | 3 | 9 | 12 | 11 | 11 | 1 | 3
12 | 3 | 9 | 12 | 17 | 13 | 1 | 3
(12 rows)
Before updating pgRouting enumerate the columns: (seq, depth, start_vid, node, edge, cost, agg_cost).
SELECT seq, depth, start_vid, node, edge, cost, agg_cost
FROM pgr_kruskalDFS(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
ARRAY[9, 6], max_depth => 3);
seq | depth | start_vid | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+----------
1 | 0 | 6 | 6 | -1 | 0 | 0
2 | 1 | 6 | 5 | 1 | 1 | 1
3 | 1 | 6 | 10 | 2 | 1 | 1
4 | 2 | 6 | 15 | 3 | 1 | 2
5 | 3 | 6 | 16 | 16 | 1 | 3
6 | 0 | 9 | 9 | -1 | 0 | 0
7 | 1 | 9 | 8 | 14 | 1 | 1
8 | 2 | 9 | 7 | 10 | 1 | 2
9 | 3 | 9 | 3 | 7 | 1 | 3
10 | 2 | 9 | 12 | 12 | 1 | 2
11 | 3 | 9 | 11 | 11 | 1 | 3
12 | 3 | 9 | 17 | 13 | 1 | 3
(12 rows)
Using pgr_primDD
Migrating this v3.6 example.
SELECT * FROM pgr_primDD(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
ARRAY[9, 6], 3.5);
seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
1 | 0 | 6 | 6 | 6 | -1 | 0 | 0
2 | 1 | 6 | 6 | 5 | 1 | 1 | 1
3 | 1 | 6 | 6 | 10 | 2 | 1 | 1
4 | 2 | 6 | 10 | 15 | 3 | 1 | 2
5 | 2 | 6 | 10 | 11 | 5 | 1 | 2
6 | 3 | 6 | 11 | 16 | 9 | 1 | 3
7 | 3 | 6 | 11 | 12 | 11 | 1 | 3
8 | 1 | 6 | 6 | 7 | 4 | 1 | 1
9 | 2 | 6 | 7 | 3 | 7 | 1 | 2
10 | 3 | 6 | 3 | 1 | 6 | 1 | 3
11 | 2 | 6 | 7 | 8 | 10 | 1 | 2
12 | 3 | 6 | 8 | 9 | 14 | 1 | 3
13 | 0 | 9 | 9 | 9 | -1 | 0 | 0
14 | 1 | 9 | 9 | 8 | 14 | 1 | 1
15 | 2 | 9 | 8 | 7 | 10 | 1 | 2
16 | 3 | 9 | 7 | 6 | 4 | 1 | 3
17 | 3 | 9 | 7 | 3 | 7 | 1 | 3
(17 rows)
Before updating pgRouting enumerate the columns: (seq, depth, start_vid, node, edge, cost, agg_cost).
SELECT seq, depth, start_vid, node, edge, cost, agg_cost
FROM pgr_primDD(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
ARRAY[9, 6], 3.5);
seq | depth | start_vid | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+----------
1 | 0 | 6 | 6 | -1 | 0 | 0
2 | 1 | 6 | 5 | 1 | 1 | 1
3 | 1 | 6 | 10 | 2 | 1 | 1
4 | 2 | 6 | 15 | 3 | 1 | 2
5 | 2 | 6 | 11 | 5 | 1 | 2
6 | 3 | 6 | 16 | 9 | 1 | 3
7 | 3 | 6 | 12 | 11 | 1 | 3
8 | 1 | 6 | 7 | 4 | 1 | 1
9 | 2 | 6 | 3 | 7 | 1 | 2
10 | 3 | 6 | 1 | 6 | 1 | 3
11 | 2 | 6 | 8 | 10 | 1 | 2
12 | 3 | 6 | 9 | 14 | 1 | 3
13 | 0 | 9 | 9 | -1 | 0 | 0
14 | 1 | 9 | 8 | 14 | 1 | 1
15 | 2 | 9 | 7 | 10 | 1 | 2
16 | 3 | 9 | 6 | 4 | 1 | 3
17 | 3 | 9 | 3 | 7 | 1 | 3
(17 rows)
Using pgr_drivingDistance
Migrating this v3.5 example.
SELECT *
FROM pgr_drivingDistance(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
ARRAY[11, 16], 3.0, equicost => true);
seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
1 | 0 | 11 | 11 | 11 | -1 | 0 | 0
2 | 1 | 11 | 11 | 7 | 8 | 1 | 1
3 | 1 | 11 | 11 | 12 | 11 | 1 | 1
4 | 2 | 11 | 7 | 3 | 7 | 1 | 2
5 | 2 | 11 | 7 | 6 | 4 | 1 | 2
6 | 2 | 11 | 7 | 8 | 10 | 1 | 2
7 | 3 | 11 | 3 | 1 | 6 | 1 | 3
8 | 3 | 11 | 6 | 5 | 1 | 1 | 3
9 | 3 | 11 | 8 | 9 | 14 | 1 | 3
10 | 0 | 16 | 16 | 16 | -1 | 0 | 0
11 | 1 | 16 | 16 | 15 | 16 | 1 | 1
12 | 1 | 16 | 16 | 17 | 15 | 1 | 1
13 | 2 | 16 | 15 | 10 | 3 | 1 | 2
(13 rows)
Advertencia
Breaking change
If using pgr_drivingDistance with multiple vertices: column names must be
changed after updating pgRouting
To get the old version column names (seq, from_v, node, edge, cost, agg_cost): filter out the column
pred and depth and rename start_vid to from_v.
SELECT seq, start_vid AS from_v, node, edge, cost, agg_cost
FROM pgr_drivingDistance(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
ARRAY[11, 16], 3.0, equicost => true);
seq | from_v | node | edge | cost | agg_cost
-----+--------+------+------+------+----------
1 | 11 | 11 | -1 | 0 | 0
2 | 11 | 7 | 8 | 1 | 1
3 | 11 | 12 | 11 | 1 | 1
4 | 11 | 3 | 7 | 1 | 2
5 | 11 | 6 | 4 | 1 | 2
6 | 11 | 8 | 10 | 1 | 2
7 | 11 | 1 | 6 | 1 | 3
8 | 11 | 5 | 1 | 1 | 3
9 | 11 | 9 | 14 | 1 | 3
10 | 16 | 16 | -1 | 0 | 0
11 | 16 | 15 | 16 | 1 | 1
12 | 16 | 17 | 15 | 1 | 1
13 | 16 | 10 | 3 | 1 | 2
(13 rows)
Migration of deleted functions¶
Functions no longer on pgRouting
Migración de pgr_alphaShape¶
Antes de la Depreciación: Se calculó lo siguiente:
Se ha calculado un alphaShape
After Deprecation:
PostGIS has two ways of generating alphaShape.
If you have SFCGAL, which you can install using
CREATE EXTENSION postgis_sfcgal
Since PostGIS 3.5+ use CG_AlphaShape
For PostGIS 3.5+ use the old name
ST_AlphaShape
Other PostGIS options are
Migration of pgr_nodeNetwork¶
Before Deprecation: A table with <edges>_nodded was created. with split edges.
Migration
Use pgr_separateTouching and/or use pgr_separateCrossing
Migración de pgr_createTopology¶
Antes de la Depreciación: Se calculó lo siguiente:
Se ha creado una tabla con <edges>_vertices_pgr.
Después de la depreciación El usuario es responsable de crear la topología completa.
Construir una topología de ruteo¶
La información básica para usar la mayoría de las funciones de pgRouting id, source, target, cost, [reverse_cost] es lo que en pgRouting se llama topología de ruteo.
reverse_cost es opcional pero se recomienda encarecidamente tenerlo para reducir el tamaño de la base de datos debido al tamaño de las columnas de geometría. Dicho esto, en esta documentación se utiliza reverse_cost.
Cuando los datos vienen con geometrías y no hay topología de ruteo, entonces este paso es necesario.
Todos los vértices iniciales y finales de las geometrías necesitan un identificador que se almacenará en las columnas source y target de la tabla de los datos. Del mismo modo, cost y reverse_cost necesitan tener el valor de atravesar la arista en ambas direcciones.
Si las columnas no existen, hay que añadirlas a la tabla en cuestión. (véase ALTER TABLE)
La función pgr_extractVertices se utiliza para crear una tabla de vértices basada en el identificador de arista y la geometría de la arista del grafo.
SELECT * INTO vertices
FROM pgr_extractVertices('SELECT id, geom FROM edges ORDER BY id');
SELECT 18
Finalmente, utilizando los datos almacenados en las tablas de vértices, se rellenan los campos source y target.
/* -- set the source information */
UPDATE edges AS e
SET source = v.id, x1 = x, y1 = y
FROM vertices AS v
WHERE ST_StartPoint(e.geom) = v.geom;
UPDATE 24
/* -- set the target information */
UPDATE edges AS e
SET target = v.id, x2 = x, y2 = y
FROM vertices AS v
WHERE ST_EndPoint(e.geom) = v.geom;
UPDATE 24
Migracion de pgr_createVerticesTable¶
Antes de la Depreciación: Se calculó lo siguiente:
Se ha creado una tabla con <edges>_vertices_pgr.
Después de la depreciación: El usuario es responsable de crear la tabla de vértices, índices, etc. Para ello puede utilizar pgr_extractVertices.
SELECT * INTO vertices
FROM pgr_extractVertices('SELECT id, geom FROM edges ORDER BY id');
SELECT 17
Migración de pgr_analyzeOneWay¶
Antes de la Depreciación: Se calculó lo siguiente:
Número de problemas potenciales en la dirección
Donde
Los problemas de dirección se calcularon a partir de códigos.
Callejones sin salida.
Un problema de ruteo puede surgir cuando desde un vértice sólo hay una salida o una entrada, pero no ambas:
Guardando o usando directamente pgr_extractVertices se obtiene la información de los callejones sin salida y se determina si la arista adyacente es unidireccional o no.
En este ejemplo pgr_extractVertices ya se ha aplicado.
WITH
deadends AS (
SELECT (in_edges || out_edges)[1] as id
FROM vertices where array_length(in_edges || out_edges, 1) = 1)
SELECT * FROM edges JOIN deadends USING (id)
WHERE cost < 0 OR reverse_cost < 0;
id | source | target | cost | reverse_cost | capacity | reverse_capacity | x1 | y1 | x2 | y2 | geom
----+--------+--------+------+--------------+----------+------------------+----+----+----+----+------
(0 rows)
Puentes.
Otro problema para ruteo puede surgir cuando en un grafo no dirigido hay una arista cuya eliminación aumenta el numero de componentes conectados, y el puente es solo de un sentido.
Para determinar si los puentes son o no unidireccionales.
SELECT id, cost < 0 OR reverse_cost<0 AS is_OneWway
FROM pgr_bridges('SELECT id, source, target, cost, reverse_cost FROM edges')
JOIN edges ON (edge = id);
id | is_onewway
----+------------
1 | f
6 | f
7 | f
14 | f
17 | f
18 | f
(6 rows)
Migración de pgr_analyzeGraph¶
Antes de la Depreciación: Se calculó lo siguiente:
Número de segmentos aislados.
Cantidad de callejones sin salida.
Número de huecos potenciales encontrados cerca de callejones sin salida.
Número de intersecciones. (entre 2 aristas)
Donde
- Componente de grafo:
Un sub-grafo conectando no es parte de un grafo conectado mas grande.
- Segmento aislado:
Un componente de grafo con un solo segmento.
- Callejones sin salida:
Un vértice que participa en una sola arista.
- Espacios:
Espacio entre dos geometrías.
- Intersección:
Es una relación topológica entre dos geometrías.
Migración.
Componentes.
En lugar de contar sólo segmentos aislados, determinar todos los componentes del grafo.
Dependiendo de los requisitos de la aplicación final utilizar:
Por ejemplo:
SELECT *
FROM pgr_connectedComponents(
'SELECT id, source, target, cost, reverse_cost FROM edges'
);
seq | component | node
-----+-----------+------
1 | 1 | 1
2 | 1 | 3
3 | 1 | 5
4 | 1 | 6
5 | 1 | 7
6 | 1 | 8
7 | 1 | 9
8 | 1 | 10
9 | 1 | 11
10 | 1 | 12
11 | 1 | 15
12 | 1 | 16
13 | 1 | 17
14 | 2 | 2
15 | 2 | 4
16 | 13 | 13
17 | 13 | 14
(17 rows)
Callejones sin salida.
En lugar de contar los callejones sin salida, determine todos los callejones sin salida del grafo utilizando pgr_degree.
Por ejemplo:
SELECT *
FROM pgr_degree($$SELECT id, source, target FROM edges$$)
WHERE degree = 1;
node | degree
------+--------
9 | 1
5 | 1
4 | 1
14 | 1
13 | 1
2 | 1
1 | 1
(7 rows)
Potential gaps near dead ends.
Instead of counting potential gaps between geometries, determine the geometric gaps in the graph using pgr_findCloseEdges.
Por ejemplo:
WITH
deadends AS (
SELECT id,geom, (in_edges || out_edges)[1] as inhere
FROM vertices where array_length(in_edges || out_edges, 1) = 1),
results AS (
SELECT (pgr_findCloseEdges('SELECT id, geom FROM edges WHERE id != ' || inhere , geom, 0.001)).*
FROM deadends)
SELECT d.id, edge_id, distance, st_AsText(geom) AS point, st_asText(edge) edge
FROM results JOIN deadends d USING (geom);
id | edge_id | distance | point | edge
----+---------+-------------------+---------------------------+--------------------------------------
4 | 14 | 1.00008890058e-12 | POINT(1.999999999999 3.5) | LINESTRING(1.999999999999 3.5,2 3.5)
(1 row)
Relaciones topológicas.
Instead of counting intersections, determine topological relationships between geometries.
Se pueden utilizar varias funciones de PostGIS: ST_Intersects, ST_Crosses, ST_Overlaps, etc.
Por ejemplo:
SELECT e1.id AS id1, e2.id AS id2
FROM edges e1, edges e2 WHERE e1 < e2 AND st_crosses(e1.geom, e2.geom);
id1 | id2
-----+-----
13 | 18
(1 row)
Migración de pgr_trsp` (Vértices)¶
Firma:
pgr_trsp(Edges SQL, source, target, directed boolean, has_rcost boolean
[,restrict_sql text]);
RETURNS SETOF (seq, id1, id2, cost)
Ver también
Usar pgr_dijkstra cuando no hay restricciones.¶
Usa en vez pgr_dijkstra.
SELECT * FROM pgr_dijkstra(
$$SELECT id, source, target, cost, reverse_cost
FROM edges WHERE id != 16$$,
15, 16);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 15 | 16 | 15 | 3 | 1 | 0
2 | 2 | 15 | 16 | 10 | 5 | 1 | 1
3 | 3 | 15 | 16 | 11 | 9 | 1 | 2
4 | 4 | 15 | 16 | 16 | -1 | 0 | 3
(4 rows)
Para obtener los nombres originales de las columnas:
SELECT seq, node::INTEGER AS id1, edge::INTEGER AS id2, cost
FROM pgr_dijkstra(
$$SELECT id, source, target, cost, reverse_cost
FROM edges WHERE id != 16$$,
15, 16);
seq | id1 | id2 | cost
-----+-----+-----+------
1 | 15 | 3 | 1
2 | 10 | 5 | 1
3 | 11 | 9 | 1
4 | 16 | -1 | 0
(4 rows)
id1es el nodoid2es la arista
Usar pgr_trspVia cuando hay restricciones.¶
Utilice en su lugar pgr_trsp (Uno a Uno).
SELECT * FROM pgr_trsp(
$$SELECT id, source, target, cost, reverse_cost
FROM edges WHERE id != 16$$,
$$SELECT * FROM new_restrictions$$,
15, 16);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 15 | 16 | 15 | 3 | 1 | 0
2 | 2 | 15 | 16 | 10 | 5 | 1 | 1
3 | 3 | 15 | 16 | 11 | 11 | 1 | 2
4 | 4 | 15 | 16 | 12 | 13 | 1 | 3
5 | 5 | 15 | 16 | 17 | 15 | 1 | 4
6 | 6 | 15 | 16 | 16 | -1 | 0 | 5
(6 rows)
Para obtener los nombres originales de las columnas:
SELECT seq, node::INTEGER AS id1, edge::INTEGER AS id2, cost
FROM pgr_trsp(
$$SELECT id, source, target, cost, reverse_cost
FROM edges WHERE id != 16$$,
$$SELECT * FROM new_restrictions$$,
15, 16);
seq | id1 | id2 | cost
-----+-----+-----+------
1 | 15 | 3 | 1
2 | 10 | 5 | 1
3 | 11 | 11 | 1
4 | 12 | 13 | 1
5 | 17 | 15 | 1
6 | 16 | -1 | 0
(6 rows)
id1es el nodoid2es la arista
Migración de pgr_trsp (Aristas)¶
Firma:
pgr_trsp(sql text, source_edge integer, source_pos float8,
target_edge integer, target_pos float8,
directed boolean, has_rcost boolean
[,restrict_sql text]);
RETURNS SETOF (seq, id1, id2, cost)
Ver también
Migraciones de pgr_trspViaVertices¶
Firma:
pgr_trspViaVertices(sql text, vids integer[],
directed boolean, has_rcost boolean
[, turn_restrict_sql text]);
RETURNS SETOF (seq, id1, id2, id3, cost)
Ver también
Usar pgr_dijkstraVia cuando no hay restricciones¶
Usar pgr_dijkstraVia - Propuesto en su lugar.
SELECT * FROM pgr_dijkstraVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
ARRAY[6, 3, 6]);
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost | route_agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------+----------------
1 | 1 | 1 | 6 | 3 | 6 | 4 | 1 | 0 | 0
2 | 1 | 2 | 6 | 3 | 7 | 7 | 1 | 1 | 1
3 | 1 | 3 | 6 | 3 | 3 | -1 | 0 | 2 | 2
4 | 2 | 1 | 3 | 6 | 3 | 7 | 1 | 0 | 2
5 | 2 | 2 | 3 | 6 | 7 | 4 | 1 | 1 | 3
6 | 2 | 3 | 3 | 6 | 6 | -2 | 0 | 2 | 4
(6 rows)
Para obtener los nombres originales de las columnas:
SELECT row_number() over(ORDER BY seq) AS seq,
path_id::INTEGER AS id1, node::INTEGER AS id2,
CASE WHEN edge >= 0 THEN edge::INTEGER ELSE -1 END AS id3, cost::FLOAT
FROM pgr_dijkstraVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
ARRAY[6, 3, 6])
WHERE edge != -1;
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 | 1 | 6 | 4 | 1
2 | 1 | 7 | 7 | 1
3 | 2 | 3 | 7 | 1
4 | 2 | 7 | 4 | 1
5 | 2 | 6 | -1 | 0
(5 rows)
id1es el identificador de caminoid2es el nodoid3es la arista
Usar pgr_trspVia cuando hay restricciones¶
Usar pgr_trspVia en su lugar.
SELECT * FROM pgr_trspVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM new_restrictions$$,
ARRAY[6, 3, 6]);
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost | route_agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------+----------------
1 | 1 | 1 | 6 | 3 | 6 | 4 | 1 | 0 | 0
2 | 1 | 2 | 6 | 3 | 7 | 8 | 1 | 1 | 1
3 | 1 | 3 | 6 | 3 | 11 | 9 | 1 | 2 | 2
4 | 1 | 4 | 6 | 3 | 16 | 16 | 1 | 3 | 3
5 | 1 | 5 | 6 | 3 | 15 | 3 | 1 | 4 | 4
6 | 1 | 6 | 6 | 3 | 10 | 5 | 1 | 5 | 5
7 | 1 | 7 | 6 | 3 | 11 | 8 | 1 | 6 | 6
8 | 1 | 8 | 6 | 3 | 7 | 7 | 1 | 7 | 7
9 | 1 | 9 | 6 | 3 | 3 | -1 | 0 | 8 | 8
10 | 2 | 1 | 3 | 6 | 3 | 7 | 1 | 0 | 8
11 | 2 | 2 | 3 | 6 | 7 | 4 | 1 | 1 | 9
12 | 2 | 3 | 3 | 6 | 6 | -2 | 0 | 2 | 10
(12 rows)
Para obtener los nombres originales de las columnas:
SELECT row_number() over(ORDER BY seq) AS seq,
path_id::INTEGER AS id1, node::INTEGER AS id2,
CASE WHEN edge >= 0 THEN edge::INTEGER ELSE -1 END AS id3, cost::FLOAT
FROM pgr_trspVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM new_restrictions$$,
ARRAY[6, 3, 6])
WHERE edge != -1;
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 | 1 | 6 | 4 | 1
2 | 1 | 7 | 8 | 1
3 | 1 | 11 | 9 | 1
4 | 1 | 16 | 16 | 1
5 | 1 | 15 | 3 | 1
6 | 1 | 10 | 5 | 1
7 | 1 | 11 | 8 | 1
8 | 1 | 7 | 7 | 1
9 | 2 | 3 | 7 | 1
10 | 2 | 7 | 4 | 1
11 | 2 | 6 | -1 | 0
(11 rows)
id1es el identificador de caminoid2es el nodoid3es la arista
Migración de pgr_trspViaEdges¶
Firma:
pgr_trspViaEdges(sql text, eids integer[], pcts float8[],
directed boolean, has_rcost boolean
[, turn_restrict_sql text]);
RETURNS SETOF (seq, id1, id2, id3, cost)
Ver también
Usar pgr_withPointsVia cuando no hay restricciones¶
Usar pgr_withPointsVia en su lugar.
SELECT * FROM pgr_withPointsVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (3, 4, 6)$$,
ARRAY[-4, -3, -6],
driving_side => 'r', details => false);
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost | route_agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------+----------------
1 | 1 | 1 | -4 | -3 | -4 | 6 | 0.7 | 0 | 0
2 | 1 | 2 | -4 | -3 | 3 | 7 | 1 | 0.7 | 0.7
3 | 1 | 3 | -4 | -3 | 7 | 10 | 1 | 1.7 | 1.7
4 | 1 | 4 | -4 | -3 | 8 | 12 | 0.6 | 2.7 | 2.7
5 | 1 | 5 | -4 | -3 | -3 | -1 | 0 | 3.3 | 3.3
6 | 2 | 1 | -3 | -6 | -3 | 12 | 0.4 | 0 | 3.3
7 | 2 | 2 | -3 | -6 | 12 | 13 | 1 | 0.4 | 3.7
8 | 2 | 3 | -3 | -6 | 17 | 15 | 1 | 1.4 | 4.7
9 | 2 | 4 | -3 | -6 | 16 | 9 | 1 | 2.4 | 5.7
10 | 2 | 5 | -3 | -6 | 11 | 8 | 1 | 3.4 | 6.7
11 | 2 | 6 | -3 | -6 | 7 | 4 | 0.3 | 4.4 | 7.7
12 | 2 | 7 | -3 | -6 | -6 | -2 | 0 | 4.7 | 8
(12 rows)
Para obtener los nombres originales de las columnas:
SELECT row_number() over(ORDER BY seq) AS seq,
path_id::INTEGER AS id1, node::INTEGER AS id2,
CASE WHEN edge >= 0 THEN edge::INTEGER ELSE -1 END AS id3, cost::FLOAT
FROM pgr_withPointsVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (3, 4, 6)$$,
ARRAY[-4, -3, -6],
driving_side => 'r', details => false);
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 | 1 | -4 | 6 | 0.7
2 | 1 | 3 | 7 | 1
3 | 1 | 7 | 10 | 1
4 | 1 | 8 | 12 | 0.6
5 | 1 | -3 | -1 | 0
6 | 2 | -3 | 12 | 0.4
7 | 2 | 12 | 13 | 1
8 | 2 | 17 | 15 | 1
9 | 2 | 16 | 9 | 1
10 | 2 | 11 | 8 | 1
11 | 2 | 7 | 4 | 0.3
12 | 2 | -6 | -1 | 0
(12 rows)
id1es el identificador de caminoid2es el nodoid3es la arista
Usar pgr_trspVia_withPoints cuando hay restricciones¶
Usar pgr_trspVia_withPoints en su lugar.
SELECT * FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM new_restrictions$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (3, 4, 6)$$,
ARRAY[-4, -3, -6],
driving_side => 'r', details => false);
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost | route_agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------+----------------
1 | 1 | 1 | -4 | -3 | -4 | 6 | 0.7 | 0 | 0
2 | 1 | 2 | -4 | -3 | 3 | 7 | 1 | 0.7 | 0.7
3 | 1 | 3 | -4 | -3 | 7 | 4 | 0.6 | 1.7 | 1.7
4 | 1 | 4 | -4 | -3 | 7 | 10 | 1 | 2.3 | 2.3
5 | 1 | 5 | -4 | -3 | 8 | 12 | 0.6 | 3.3 | 3.3
6 | 1 | 6 | -4 | -3 | -3 | -1 | 0 | 3.9 | 3.9
7 | 2 | 1 | -3 | -6 | -3 | 12 | 0.4 | 0 | 3.9
8 | 2 | 2 | -3 | -6 | 12 | 13 | 1 | 0.4 | 4.3
9 | 2 | 3 | -3 | -6 | 17 | 15 | 1 | 1.4 | 5.3
10 | 2 | 4 | -3 | -6 | 16 | 9 | 1 | 2.4 | 6.3
11 | 2 | 5 | -3 | -6 | 11 | 8 | 1 | 3.4 | 7.3
12 | 2 | 6 | -3 | -6 | 7 | 4 | 0.3 | 4.4 | 8.3
13 | 2 | 7 | -3 | -6 | -6 | -2 | 0 | 4.7 | 8.6
(13 rows)
Para obtener los nombres originales de las columnas:
SELECT row_number() over(ORDER BY seq) AS seq,
path_id::INTEGER AS id1, node::INTEGER AS id2,
CASE WHEN edge >= 0 THEN edge::INTEGER ELSE -1 END AS id3, cost::FLOAT
FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM new_restrictions$$,
$$SELECT * FROM (VALUES (1, 6, 0.3),(2, 12, 0.6),(3, 4, 0.7)) AS t(pid, edge_id, fraction)$$,
ARRAY[-1, -2, -3],
driving_side => 'r', details => false);
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 | 1 | -1 | 6 | 0.7
2 | 1 | 3 | 7 | 1
3 | 1 | 7 | 4 | 0.6
4 | 1 | 7 | 10 | 1
5 | 1 | 8 | 12 | 0.6
6 | 1 | -2 | -1 | 0
7 | 2 | -2 | 12 | 0.4
8 | 2 | 12 | 13 | 1
9 | 2 | 17 | 15 | 1
10 | 2 | 16 | 9 | 1
11 | 2 | 11 | 8 | 1
12 | 2 | 7 | 4 | 0.3
13 | 2 | -3 | -1 | 0
(13 rows)
id1es el identificador de caminoid2es el nodoid3es la arista
Not yet classified migrations¶
Migración de pgr_KSP¶
A partir de v3.6.0 En pgr_KSP las columnas de resultados están siendo estandarizadas.
- de:
(seq, path_id, path_seq, node, edge, cost, agg_cost)- a:
(seq, path_id, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
Firmas que serán migradas:
pgr_KSP(Uno a Uno)
- Antes de la migración:
Columnas de resultados eran
(seq, path_id, path_seq, node, edge, cost, agg_cost)las columnas
start_vidyend_vidno existen.pgr_KSP(Uno a Uno) no tienestart_vidniend_vid.
- Migración:
Tener en cuenta la existencia de las columnas adicionales.
If needed filter out the added columns, for example, to return the original columns.
pgr_KSP (Uno a Uno)¶
Usando éste ejemplo.
start_vidcontiene el valor del parámetro start vid.end_vidcontiene el valor del parámetro end vid.
SELECT * FROM pgr_KSP(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
6, 17, 2);
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------
1 | 1 | 1 | 6 | 17 | 6 | 4 | 1 | 0
2 | 1 | 2 | 6 | 17 | 7 | 10 | 1 | 1
3 | 1 | 3 | 6 | 17 | 8 | 12 | 1 | 2
4 | 1 | 4 | 6 | 17 | 12 | 13 | 1 | 3
5 | 1 | 5 | 6 | 17 | 17 | -1 | 0 | 4
6 | 2 | 1 | 6 | 17 | 6 | 4 | 1 | 0
7 | 2 | 2 | 6 | 17 | 7 | 8 | 1 | 1
8 | 2 | 3 | 6 | 17 | 11 | 9 | 1 | 2
9 | 2 | 4 | 6 | 17 | 16 | 15 | 1 | 3
10 | 2 | 5 | 6 | 17 | 17 | -1 | 0 | 4
(10 rows)
Si es necesario, filtrar las columnas añadidas, por ejemplo, para devolver las columnas originales:
SELECT seq, path_id, path_seq, node, edge, cost, agg_cost FROM pgr_KSP(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
6, 17, 2);
seq | path_id | path_seq | node | edge | cost | agg_cost
-----+---------+----------+------+------+------+----------
1 | 1 | 1 | 6 | 4 | 1 | 0
2 | 1 | 2 | 7 | 10 | 1 | 1
3 | 1 | 3 | 8 | 12 | 1 | 2
4 | 1 | 4 | 12 | 13 | 1 | 3
5 | 1 | 5 | 17 | -1 | 0 | 4
6 | 2 | 1 | 6 | 4 | 1 | 0
7 | 2 | 2 | 7 | 8 | 1 | 1
8 | 2 | 3 | 11 | 9 | 1 | 2
9 | 2 | 4 | 16 | 15 | 1 | 3
10 | 2 | 5 | 17 | -1 | 0 | 4
(10 rows)
Migración de pgr_withPointsKSP¶
A partir de v3.6.0 en pgr_withPointsKSP las columnas de resultados están siendo estandarizadas.
- de:
(seq, path_id, path_seq, node, edge, cost, agg_cost)- a:
(seq, path_id, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
Y el parámetro «driving side» ha cambiado de opcional con nombre a obligatorio sin nombre driving side y su validez difiere para grafos dirigidos y no dirigidos.
Firmas que serán migradas:
pgr_withPointsKSP(Uno a Uno)
- Antes de la migración:
Regresa conjunto de
(seq, path_seq, [start_vid], [end_vid], node, edge, cost, agg_cost)las columnas
start_vidyend_vidno existen.
- Migración:
Tener en cuenta la existencia de las columnas adicionales de resultados.
New output columns are
(seq, path_id, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)El parámetro lado de manjeo es obligatorio sin nombre, y los valores válidos difieren para grafos dirigidos y no dirigidos.
No tiene un valor de facto.
En grafos dirigidos: Los valores válidos son [
r,R,l,L]En grafos no dirigidos: Los valores válidos son [
b,B]El uso de un valor inválido lanza un
ERROR.
pgr_withPointsKSP (Uno a Uno)¶
Usando este ejemplo.
start_vidcontiene el valor del parámetro start vid.end_vidcontiene el valor del parámetro end vid.
SELECT * FROM pgr_withPointsKSP(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
-1, -2, 2, 'l');
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------
1 | 1 | 1 | -1 | -2 | -1 | 1 | 0.6 | 0
2 | 1 | 2 | -1 | -2 | 6 | 4 | 1 | 0.6
3 | 1 | 3 | -1 | -2 | 7 | 8 | 1 | 1.6
4 | 1 | 4 | -1 | -2 | 11 | 11 | 1 | 2.6
5 | 1 | 5 | -1 | -2 | 12 | 13 | 1 | 3.6
6 | 1 | 6 | -1 | -2 | 17 | 15 | 0.6 | 4.6
7 | 1 | 7 | -1 | -2 | -2 | -1 | 0 | 5.2
8 | 2 | 1 | -1 | -2 | -1 | 1 | 0.6 | 0
9 | 2 | 2 | -1 | -2 | 6 | 4 | 1 | 0.6
10 | 2 | 3 | -1 | -2 | 7 | 8 | 1 | 1.6
11 | 2 | 4 | -1 | -2 | 11 | 9 | 1 | 2.6
12 | 2 | 5 | -1 | -2 | 16 | 15 | 1.6 | 3.6
13 | 2 | 6 | -1 | -2 | -2 | -1 | 0 | 5.2
(13 rows)
Si es necesario, filtrar las columnas adicionales, por ejemplo, para devolver las columnas originales:
SELECT seq, path_id, path_seq, node, edge, cost, agg_cost FROM pgr_withPointsKSP(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
-1, -2, 2, 'l');
seq | path_id | path_seq | node | edge | cost | agg_cost
-----+---------+----------+------+------+------+----------
1 | 1 | 1 | -1 | 1 | 0.6 | 0
2 | 1 | 2 | 6 | 4 | 1 | 0.6
3 | 1 | 3 | 7 | 8 | 1 | 1.6
4 | 1 | 4 | 11 | 11 | 1 | 2.6
5 | 1 | 5 | 12 | 13 | 1 | 3.6
6 | 1 | 6 | 17 | 15 | 0.6 | 4.6
7 | 1 | 7 | -2 | -1 | 0 | 5.2
8 | 2 | 1 | -1 | 1 | 0.6 | 0
9 | 2 | 2 | 6 | 4 | 1 | 0.6
10 | 2 | 3 | 7 | 8 | 1 | 1.6
11 | 2 | 4 | 11 | 9 | 1 | 2.6
12 | 2 | 5 | 16 | 15 | 1.6 | 3.6
13 | 2 | 6 | -2 | -1 | 0 | 5.2
(13 rows)
Usar pgr_withPoints cuando no hay restricciones.¶
Utilizar en su lugar pgr_withPoints (Uno a Uno).
SELECT * FROM pgr_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (4, 3)$$,
-4, -3,
'r', details => false);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | -4 | -3 | -4 | 6 | 0.7 | 0
2 | 2 | -4 | -3 | 3 | 7 | 1 | 0.7
3 | 3 | -4 | -3 | 7 | 10 | 1 | 1.7
4 | 4 | -4 | -3 | 8 | 12 | 0.6 | 2.7
5 | 5 | -4 | -3 | -3 | -1 | 0 | 3.3
(5 rows)
Para obtener los nombres originales de las columnas:
SELECT seq, node::INTEGER AS id1, edge::INTEGER AS id2, cost
FROM pgr_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM (VALUES (1, 6, 0.3),(2, 12, 0.6)) AS t(pid, edge_id, fraction)$$,
-1, -2,
'r', details => false);
seq | id1 | id2 | cost
-----+-----+-----+------
1 | -1 | 6 | 0.7
2 | 3 | 7 | 1
3 | 7 | 10 | 1
4 | 8 | 12 | 0.6
5 | -2 | -1 | 0
(5 rows)
id1es el nodoid2es la arista
Usar pgr_trsp_withPoints cuando hay restricciones.¶
Utilizar en su lugar pgr_trsp_withPoints.
SELECT * FROM pgr_trsp_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM new_restrictions$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (4, 3)$$,
-4, -3,
driving_side => 'r', details => false);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | -4 | -3 | -4 | 6 | 0.7 | 0
2 | 2 | -4 | -3 | 3 | 7 | 1 | 0.7
3 | 3 | -4 | -3 | 7 | 8 | 1 | 1.7
4 | 4 | -4 | -3 | 11 | 9 | 1 | 2.7
5 | 5 | -4 | -3 | 16 | 16 | 1 | 3.7
6 | 6 | -4 | -3 | 15 | 3 | 1 | 4.7
7 | 7 | -4 | -3 | 10 | 2 | 1 | 5.7
8 | 8 | -4 | -3 | 6 | 4 | 1 | 6.7
9 | 9 | -4 | -3 | 7 | 10 | 1 | 7.7
10 | 10 | -4 | -3 | 8 | 12 | 0.6 | 8.7
11 | 11 | -4 | -3 | -3 | -1 | 0 | 9.3
(11 rows)
Para obtener los nombres originales de las columnas:
SELECT seq, node::INTEGER AS id1, edge::INTEGER AS id2, cost
FROM pgr_trsp_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM new_restrictions$$,
$$SELECT * FROM (VALUES (1, 6, 0.3),(2, 12, 0.6)) AS t(pid, edge_id, fraction)$$,
-1, -2,
driving_side => 'r', details => false)
WHERE edge != -1;
seq | id1 | id2 | cost
-----+-----+-----+------
1 | -1 | 6 | 0.7
2 | 3 | 7 | 1
3 | 7 | 8 | 1
4 | 11 | 9 | 1
5 | 16 | 16 | 1
6 | 15 | 3 | 1
7 | 10 | 2 | 1
8 | 6 | 4 | 1
9 | 7 | 10 | 1
10 | 8 | 12 | 0.6
(10 rows)
id1es el nodoid2es la arista
Migración de pgr_maxCardinalityMatch¶
pgr_maxCardinalityMatch funciona solamente para grafos no dirigidos, entonces la bandera directed ha sido removida.
A partir de v3.4.0 <https://docs.pgrouting.org/3.4/es/migration.html>`__
Firma que serán migrada:
pgr_maxCardinalityMatch(Edges SQL, [directed])
RETURNS SETOF (seq, edge, source, target)
Migración es necesaria, porque:
Usa
costyreverse_costen la consulta internaLos resultados son ordenados
Funciona para grafos no dirigidos.
Nueva firma
pgr_maxCardinalityMatch(text)regresa solamente la columnaedge.La bandera opcional
directedes removida.
- Antes de migración:
Las columnas usadas son
goingycomingpara representar la existencia de la arista.La flag
directedes usada para indicar si fue para un grafo dirigido or no dirigido.La bandera
directedes ignorada.Independiente de su valor, da el resultado considerando el grafo como no dirigido.
- Migración:
Usa las columnas
costyreverse_costpara representar la existenica de una arista.No use la bandera
directed.En la consulta solo devuelve la columna
edge.
SELECT * FROM pgr_maxCardinalityMatch(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$
);
edge
------
1
5
6
13
14
16
17
18
(8 rows)
Migración de restricciones¶
A partir de v3.4.0 <https://docs.pgrouting.org/3.4/es/migration.html>`__
La estructura de las restricciones ha cambiado:
Estructura de restricciones vieja¶
Sobre las firmas obsoletas:
La columna
rides ignoradavia_pathDebe de estar en orden reverso.
Es de tipo
TEXT.Cuando hay más de una arista debe de ser separada con
,.
target_idEs la última arista del camino prohibido.
Es de tipo de
INTEGER.
to_costEs de tipo
FLOAT.
Creación de la vieja table de restricciones
CREATE TABLE old_restrictions (
rid BIGINT NOT NULL,
to_cost FLOAT,
target_id BIGINT,
via_path TEXT
);
CREATE TABLE
Las viejas restricciones se llenan
INSERT INTO old_restrictions (rid, to_cost, target_id, via_path) VALUES
(1, 100, 7, '4'),
(1, 100, 11, '8'),
(1, 100, 10, '7'),
(2, 4, 9, '5, 3'),
(3, 100, 9, '16');
INSERT 0 5
Contenido de viejas restricciones¶
SELECT * FROM old_restrictions;
rid | to_cost | target_id | via_path
-----+---------+-----------+----------
1 | 100 | 7 | 4
1 | 100 | 11 | 8
1 | 100 | 10 | 7
2 | 4 | 9 | 5, 3
3 | 100 | 9 | 16
(5 rows)
La restricción con rid = 2 representa
esta en la columna
via_pathen orden reversoes de tipo
TEXT
está en la columna
target_ides de tipo
INTEGER
Estructura de nuevas restricciones¶
La columna
ides ignoradaColumna
pathEs de tipo
ARRAY[ANY-INTEGER].Contiene todas las aristas involucradas con la restricción
El conjunto tiene las aristas ordenadas de la restricción.
Columna
costEs de tipo``ANY-NUMERICAL``
La creación de la tabla de restricciones
CREATE TABLE restrictions (
id SERIAL PRIMARY KEY,
path BIGINT[],
cost FLOAT
);
CREATE TABLE
Agregando las restricciones
INSERT INTO restrictions (path, cost) VALUES
(ARRAY[4, 7], 100),
(ARRAY[8, 11], 100),
(ARRAY[7, 10], 100),
(ARRAY[3, 5, 9], 4),
(ARRAY[9, 16], 100);
INSERT 0 5
Datos de restricciones
SELECT * FROM restrictions;
id | path | cost
----+---------+------
1 | {4,7} | 100
2 | {8,11} | 100
3 | {7,10} | 100
4 | {3,5,9} | 4
5 | {9,16} | 100
(5 rows)
La restricción con``rid = 2`` representa el camino
El camino es claro por inspección.
Migración¶
Para transformar la antigua tabla de restricciones a la nueva estructura de restricciones,
Crea una nueva tabla con la nueva estructura de restricciones.
En esta guía de migración
new_restrictionsestá siendo usada.
Para esta migración pgRouting proporciona una función auxiliar para la inversión de un conjunto
_pgr_array_reversenecesaria para la migración._pgr_array_reverse:Se creo temporalmente para esta migración
No está documentado.
Se eliminara en la próxima versión mayor 4.0.0
SELECT rid AS id,
_pgr_array_reverse(
array_prepend(target_id, string_to_array(via_path::text, ',')::BIGINT[])) AS path,
to_cost AS cost
INTO new_restrictions
FROM old_restrictions;
SELECT 5
El contenido de la tabla de migración:
SELECT * FROM new_restrictions;
id | path | cost
----+---------+------
1 | {4,7} | 100
1 | {8,11} | 100
1 | {7,10} | 100
2 | {3,5,9} | 4
3 | {16,9} | 100
(5 rows)
Ver también¶
Índices y tablas