Migration guide¶
Several functions are having changes on the signatures, and/or have been replaced by new functions.
Results can be different because of the changes.
Warning
All deprecated functions will be removed on next major version 4.0.0
Migration to standardized columns¶
There has been an effort to standardize function output columns names and types.
Result columns for cost functions
(start_vid, end_vid, agg_cost)
Result columns for single path functions
(seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
Result columns for spanning tree functions
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)
Function |
Migration guide |
|---|---|
Changed in version 3.5.0: pgr_dijkstra [1] |
|
Changed in version 3.6.0: pgr_bdAstar [1] |
|
Changed in version 3.6.0: pgr_drivingDistance [1] |
|
Changed in version 3.6.0: pgr_withPointsDD [2] |
|
Changed in version 3.7.0: pgr_kruskalBFS [1] |
|
Changed in version 3.7.0: pgr_kruskalDD [1] |
|
Changed in version 3.7.0: pgr_kruskalDFS [1] |
|
Changed in version 3.7.0: pgr_primBFS [1] |
|
Changed in version 3.7.0: pgr_primDD [1] |
|
Changed in version 3.7.0: pgr_primDFS [1] |
|
Changed in version 4.0.0: pgr_bdDijkstra [1] |
|
Changed in version 4.0.0: pgr_bellmanFord - Experimental [3] |
|
Changed in version 4.0.0: pgr_binaryBreadthFirstSearch - Experimental [3] |
|
Changed in version 4.0.0: pgr_dagShortestPath - Experimental [3] |
|
Changed in version 4.0.0: pgr_edwardMoore - Experimental [3] |
|
Changed in version 4.0.0: pgr_withPoints [2] |
|
Changed in version 4.0.0: pgr_withPointsCost [2] |
|
Changed in version 4.0.0: pgr_withPointsCostMatrix [2] |
Migration of cost functions¶
The standardized Result columns for cost functions 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)
Signatures to be migrated:
One to One
One to Many
Many to One
Many to Many
Combinations
Warning
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.
- from:
(start_pid, end_pid, agg_cost)- to:
(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)
Warning
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.
- from:
(start_pid, end_pid, agg_cost)- to:
(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)
Warning
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.
- from:
(start_pid, end_pid, agg_cost)- to:
(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)
Warning
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.
- from:
(start_pid, end_pid, agg_cost)- to:
(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
Warning
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.
- from:
(start_pid, end_pid, agg_cost)- to:
(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)
Warning
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 |
|
- to:
(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)
Signatures to be migrated:
One to One
One to Many
Many to One
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)
Signatures to be migrated:
One to One
One to Many
Many to One
Many to Many
Combinations
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
Warning
Breaking change
If using pgr_withPoints: column names must be changed after updating
pgRouting
Migration of (seq, path_seq, node, edge, cost, agg_cost)
Signatures to be migrated:
One to One
One to Many
Many to One
Many to Many
Combinations
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)
Note
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)
Warning
Breaking change
If using pgr_withPoints: column names must be changed after updating
pgRouting
- from:
(seq, path_seq, end_pid, node, edge, cost, agg_cost)- to:
(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)
Warning
Breaking change
If using pgr_withPoints: column names must be changed after updating
pgRouting
- from:
(seq, path_seq, start_pid, end_pid, node, edge, cost, agg_cost)- to:
(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)
Warning
Breaking change
If using pgr_withPoints: column names must be changed after updating
pgRouting
- from:
(seq, path_seq, start_pid, end_pid, node, edge, cost, agg_cost)- to:
(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 Result columns for spanning tree functions 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)
Signatures to be migrated:
Single vertex
Multiple vertices
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)
Signatures to be migrated:
Single vertex
Multiple vertices
Migration depends on the signature.
For single vertex:
Before updating pgRouting, enumerate
(seq, path_seq, node, edge, cost, agg_cost)columns
For multiple vertices:
Warning
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)
Signatures to be migrated:
Single vertex
Multiple vertices
Warning
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)
Output columns were
(seq, start_vid, node, edge, cost, agg_cost)Does not have
depthandpredresult columns.driving_sideparameter was named optional now it is compulsory unnamed.
Validity of driving_side:
On directed graph
bcould be used as driving sideOn undirected graph
r,lcould be used as driving side
After Migration
Be aware of the existence of the additional result Columns.
Output columns are
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)
driving side parameter is unnamed compulsory, and valid values differ for directed and undirected graphs.
Does not have a default value.
In directed graph: valid values are [
r,R,l,L]In undirected graph: valid values are [
b,B]Using an invalid value throws an
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.
Warning
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)
Warning
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
Migration of pgr_alphaShape¶
Before Deprecation: The following was calculated:
An alphaShape was calculated
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
Migration of pgr_createTopology¶
Before Deprecation: The following was calculated:
A table with <edges>_vertices_pgr was created.
After Deprecation: The user is responsible to create the complete topology.
Build a routing topology¶
The basic information to use the majority of the pgRouting functions id,
source, target, cost, [reverse_cost] is what in pgRouting is called the
routing topology.
reverse_cost is optional but strongly recommended to have in order to reduce
the size of the database due to the size of the geometry columns.
Having said that, in this documentation reverse_cost is used in this
documentation.
When the data comes with geometries and there is no routing topology, then this step is needed.
All the start and end vertices of the geometries need an identifier that is to
be stored in a source and target columns of the table of the data.
Likewise, cost and reverse_cost need to have the value of traversing the
edge in both directions.
If the columns do not exist they need to be added to the table in question. (see ALTER TABLE)
The function pgr_extractVertices is used to create a vertices table based on the edge identifier and the geometry of the edge of the graph.
SELECT * INTO vertices
FROM pgr_extractVertices('SELECT id, geom FROM edges ORDER BY id');
SELECT 18
Finally using the data stored on the vertices tables the source and
target are filled up.
/* -- 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
Migration of pgr_createVerticesTable¶
Before Deprecation: The following was calculated:
A table with <edges>_vertices_pgr was created.
After Deprecation: The user is responsible to create the vertices table, indexes, etc. They may use pgr_extractVertices for that purpose.
SELECT * INTO vertices
FROM pgr_extractVertices('SELECT id, geom FROM edges ORDER BY id');
SELECT 17
Migration of pgr_analyzeOneWay¶
Before Deprecation: The following was calculated:
Number of potential problems in directionality
WHERE
Directionality problems were calculated based on codes.
Dead ends.
A routing problem can arise when from a vertex there is only a way on or a way out but not both:
Either saving or using directly pgr_extractVertices get the dead ends information and determine if the adjacent edge is one way or not.
In this example pgr_extractVertices has already been applied.
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)
Bridges.
Another routing problem can arise when there is an edge of an undirected graph whose deletion increases its number of connected components, and the bridge is only one way.
To determine if the bridges are or not one way.
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)
Migration of pgr_analyzeGraph¶
Before Deprecation: The following was calculated:
Number of isolated segments.
Number of dead ends.
Number of potential gaps found near dead ends.
Number of intersections. (between 2 edges)
WHERE
- Graph component:
A connected subgraph that is not part of any larger connected subgraph.
- Isolated segment:
A graph component with only one segment.
- Dead ends:
A vertex that participates in only one edge.
- gaps:
Space between two geometries.
- Intersection:
Is a topological relationship between two geometries.
Migration.
Components.
Instead of counting only isolated segments, determine all the components of the graph.
Depending of the final application requirements use:
For example:
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)
Dead ends.
Instead of counting the dead ends, determine all the dead ends of the graph using pgr_degree.
For example:
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.
For example:
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)
Topological relationships.
Instead of counting intersections, determine topological relationships between geometries.
Several PostGIS functions can be used: ST_Intersects, ST_Crosses, ST_Overlaps, etc.
For example:
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)
Migration of pgr_trsp (Vertices)¶
Signature:
pgr_trsp(Edges SQL, source, target, directed boolean, has_rcost boolean
[,restrict_sql text]);
RETURNS SETOF (seq, id1, id2, cost)
See Also
Use pgr_dijkstra when there are no restrictions.¶
Use pgr_dijkstra instead.
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)
To get the original column names:
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)
id1is the nodeid2is the edge
Use pgr_trsp when there are restrictions.¶
Use pgr_trsp (One to One) instead.
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)
To get the original column names:
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)
id1is the nodeid2is the edge
Migration of pgr_trsp (Edges)¶
Signature:
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)
See Also
Migration of pgr_trspViaVertices¶
Signature:
pgr_trspViaVertices(sql text, vids integer[],
directed boolean, has_rcost boolean
[, turn_restrict_sql text]);
RETURNS SETOF (seq, id1, id2, id3, cost)
See Also
Use pgr_dijkstraVia when there are no restrictions¶
Use pgr_dijkstraVia - Proposed instead.
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)
To get the original column names:
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)
id1is the path identifierid2is the nodeid3is the edge
Use pgr_trspVia when there are restrictions¶
Use pgr_trspVia instead.
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)
To get the original column names:
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)
id1is the path identifierid2is the nodeid3is the edge
Migration of pgr_trspViaEdges¶
Signature:
pgr_trspViaEdges(sql text, eids integer[], pcts float8[],
directed boolean, has_rcost boolean
[, turn_restrict_sql text]);
RETURNS SETOF (seq, id1, id2, id3, cost)
See Also
Use pgr_withPointsVia when there are no restrictions¶
Use pgr_withPointsVia instead.
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)
To get the original column names:
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)
id1is the path identifierid2is the nodeid3is the edge
Use pgr_trspVia_withPoints when there are restrictions¶
Use pgr_trspVia_withPoints instead.
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)
To get the original column names:
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)
id1is the path identifierid2is the nodeid3is the edge
Not yet classified migrations¶
Migration of pgr_KSP¶
Starting from v3.6.0 pgr_KSP result columns are being standardized.
- from:
(seq, path_id, path_seq, node, edge, cost, agg_cost)- to:
(seq, path_id, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
Signatures to be migrated:
pgr_KSP(One to One)
- Before Migration:
Output columns were
(seq, path_id, path_seq, node, edge, cost, agg_cost)the columns
start_vidandend_viddo not exist.pgr_KSP(One to One) does not havestart_vidandend_vid.
- Migration:
Be aware of the existence of the additional columns.
If needed filter out the added columns, for example, to return the original columns.
pgr_KSP (One to One)¶
Using this example.
start_vidcontains the start vid parameter value.end_vidcontains the end vid parameter value.
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)
If needed filter out the added columns, for example, to return the original columns:
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)
Migration of pgr_withPointsKSP¶
Starting from v3.6.0 pgr_withPointsKSP result columns are being standardized.
- from:
(seq, path_id, path_seq, node, edge, cost, agg_cost)- to:
(seq, path_id, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
And driving side parameter changed from named optional to unnamed compulsory
driving side and its validity differ for directed and undirected graphs.
Signatures to be migrated:
pgr_withPointsKSP(One to One)
- Before Migration:
Output columns were
(seq, path_seq, [start_pid], [end_pid], node, edge, cost, agg_cost)the columns
start_vidandend_viddo not exist.
- Migration:
Be aware of the existence of the additional result Columns.
New output columns are
(seq, path_id, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)driving side parameter is unnamed compulsory, and valid values differ for directed and undirected graphs.
Does not have a default value.
In directed graph: valid values are [
r,R,l,L]In undirected graph: valid values are [
b,B]Using an invalid value throws an
ERROR.
pgr_withPointsKSP (One to One)¶
Using this example.
start_vidcontains the start vid parameter value.end_vidcontains the end vid parameter value.
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)
If needed filter out the additional columns, for example, to return the original columns:
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)
Use pgr_withPoints when there are no restrictions.¶
Use pgr_withPoints (One to One) instead.
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)
To get the original column names:
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)
id1is the nodeid2is the edge
Use pgr_trsp_withPoints when there are restrictions.¶
Use pgr_trsp_withPoints instead.
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)
To get the original column names:
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)
id1is the nodeid2is the edge
Migration of pgr_maxCardinalityMatch¶
pgr_maxCardinalityMatch works only for undirected graphs, therefore the
directed flag has been removed.
Starting from v3.4.0
Signature to be migrated:
pgr_maxCardinalityMatch(Edges SQL, [directed])
RETURNS SETOF (seq, edge, source, target)
Migration is needed, because:
Use
costandreverse_coston the inner queryResults are ordered
Works for undirected graphs.
New signature
pgr_maxCardinalityMatch(text)returns onlyedgecolumn.The optional flag
directedis removed.
- Before migration:
Columns used are
goingandcomingto represent the existence of an edge.Flag
directedwas used to indicate if it was for a directed or undirected graph.The flag
directedis ignored.Regardless of it’s value it gives the result considering the graph as undirected.
- Migration:
Use the columns
costandreverse_costto represent the existence of an edge.Do not use the flag
directed.In the query returns only
edgecolumn.
SELECT * FROM pgr_maxCardinalityMatch(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$
);
edge
------
1
5
6
13
14
16
17
18
(8 rows)
Migration of restrictions¶
Starting from v3.4.0
The structure of the restrictions have changed:
Old restrictions structure¶
On the deprecated signatures:
Column
ridis ignoredvia_pathMust be in reverse order.
Is of type
TEXT.When more than one via edge must be separated with
,.
target_idIs the last edge of the forbidden path.
Is of type
INTEGER.
to_costIs of type
FLOAT.
Creation of the old restrictions table
CREATE TABLE old_restrictions (
rid BIGINT NOT NULL,
to_cost FLOAT,
target_id BIGINT,
via_path TEXT
);
CREATE TABLE
Old restrictions fill up
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
Old restrictions contents¶
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)
The restriction with rid = 2 is representing
is on column
via_pathin reverse orderis of type
TEXT
is on column
target_idis of type
INTEGER
New restrictions structure¶
Column
idis ignoredColumn
pathIs of type
ARRAY[ANY-INTEGER].Contains all the edges involved on the restriction.
The array has the ordered edges of the restriction.
Column
costIs of type
ANY-NUMERICAL
The creation of the restrictions table
CREATE TABLE restrictions (
id SERIAL PRIMARY KEY,
path BIGINT[],
cost FLOAT
);
CREATE TABLE
Adding the restrictions
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
Restrictions data
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)
The restriction with rid = 2 represents the path
By inspection the path is clear.
Migration¶
To transform the old restrictions table to the new restrictions structure,
Create a new table with the new restrictions structure.
In this migration guide
new_restrictionsis been used.
For this migration pgRouting supplies an auxiliary function for reversal of an array
_pgr_array_reverseneeded for the migration._pgr_array_reverse:Was created temporally for this migration
Is not documented.
Will be removed on the next mayor version 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
The migrated table contents:
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)
See Also¶
Indices and tables