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 mayor version 4.0.0
Migration of pgr_aStar¶
Starting from v3.6.0
Signatures to be migrated:
pgr_aStar(One to One)pgr_aStar(One to Many)pgr_aStar(Many to One)
- Before Migration:
Output columns were
(seq, path_seq, [start_vid], [end_vid], node, edge, cost, agg_cost)Depending on the overload used, the columns
start_vidandend_vidmight be missing:pgr_aStar(One to One) does not havestart_vidandend_vid.pgr_aStar(One to Many) does not havestart_vid.pgr_aStar(Many to One) does not haveend_vid.
- Migration:
Be aware of the existence of the additional columns.
In
pgr_aStar(One to One)start_vidcontains the start vid parameter value.end_vidcontains the end vid parameter value.
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)
In
pgr_aStar(One to Many)start_vidcontains the start vid parameter value.
SELECT * FROM pgr_aStar(
$$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
6, ARRAY[3, 10]);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 6 | 3 | 6 | 4 | 1 | 0
2 | 2 | 6 | 3 | 7 | 7 | 1 | 1
3 | 3 | 6 | 3 | 3 | -1 | 0 | 2
4 | 1 | 6 | 10 | 6 | 4 | 1 | 0
5 | 2 | 6 | 10 | 7 | 8 | 1 | 1
6 | 3 | 6 | 10 | 11 | 9 | 1 | 2
7 | 4 | 6 | 10 | 16 | 16 | 1 | 3
8 | 5 | 6 | 10 | 15 | 3 | 1 | 4
9 | 6 | 6 | 10 | 10 | -1 | 0 | 5
(9 rows)
In
pgr_aStar(Many to One)end_vidcontains the end vid parameter value.
SELECT * FROM pgr_aStar(
$$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
ARRAY[3, 6], 10);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 3 | 10 | 3 | 7 | 1 | 0
2 | 2 | 3 | 10 | 7 | 8 | 1 | 1
3 | 3 | 3 | 10 | 11 | 9 | 1 | 2
4 | 4 | 3 | 10 | 16 | 16 | 1 | 3
5 | 5 | 3 | 10 | 15 | 3 | 1 | 4
6 | 6 | 3 | 10 | 10 | -1 | 0 | 5
7 | 1 | 6 | 10 | 6 | 4 | 1 | 0
8 | 2 | 6 | 10 | 7 | 8 | 1 | 1
9 | 3 | 6 | 10 | 11 | 9 | 1 | 2
10 | 4 | 6 | 10 | 16 | 16 | 1 | 3
11 | 5 | 6 | 10 | 15 | 3 | 1 | 4
12 | 6 | 6 | 10 | 10 | -1 | 0 | 5
(12 rows)
If needed filter out the added columns, for example:
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)
If needed add the new columns, similar to the following example where
pgr_dijkstrais used, and the function had to be modified to be able to return the new columns:
Migration of pgr_bdAstar¶
Starting from v3.6.0
Signatures to be migrated:
pgr_bdAstar(One to One)pgr_bdAstar(One to Many)pgr_bdAstar(Many to One)
- Before Migration:
Output columns were
(seq, path_seq, [start_vid], [end_vid], node, edge, cost, agg_cost)Depending on the overload used, the columns
start_vidandend_vidmight be missing:pgr_bdAstar(One to One) does not havestart_vidandend_vid.pgr_bdAstar(One to Many) does not havestart_vid.pgr_bdAstar(Many to One) does not haveend_vid.
- Migration:
Be aware of the existence of the additional columns.
In
pgr_bdAstar(One to One)start_vidcontains the start vid parameter value.end_vidcontains the end vid parameter value.
SELECT * FROM pgr_bdAstar(
$$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)
In
pgr_bdAstar(One to Many)start_vidcontains the start vid parameter value.
SELECT * FROM pgr_bdAstar(
$$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
6, ARRAY[3, 10]);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 6 | 3 | 6 | 4 | 1 | 0
2 | 2 | 6 | 3 | 7 | 7 | 1 | 1
3 | 3 | 6 | 3 | 3 | -1 | 0 | 2
4 | 1 | 6 | 10 | 6 | 4 | 1 | 0
5 | 2 | 6 | 10 | 7 | 8 | 1 | 1
6 | 3 | 6 | 10 | 11 | 9 | 1 | 2
7 | 4 | 6 | 10 | 16 | 16 | 1 | 3
8 | 5 | 6 | 10 | 15 | 3 | 1 | 4
9 | 6 | 6 | 10 | 10 | -1 | 0 | 5
(9 rows)
In
pgr_bdAstar(Many to One)end_vidcontains the end vid parameter value.
SELECT * FROM pgr_bdAstar(
$$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
ARRAY[3, 6], 10);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 3 | 10 | 3 | 7 | 1 | 0
2 | 2 | 3 | 10 | 7 | 8 | 1 | 1
3 | 3 | 3 | 10 | 11 | 9 | 1 | 2
4 | 4 | 3 | 10 | 16 | 16 | 1 | 3
5 | 5 | 3 | 10 | 15 | 3 | 1 | 4
6 | 6 | 3 | 10 | 10 | -1 | 0 | 5
7 | 1 | 6 | 10 | 6 | 4 | 1 | 0
8 | 2 | 6 | 10 | 7 | 8 | 1 | 1
9 | 3 | 6 | 10 | 11 | 9 | 1 | 2
10 | 4 | 6 | 10 | 16 | 16 | 1 | 3
11 | 5 | 6 | 10 | 15 | 3 | 1 | 4
12 | 6 | 6 | 10 | 10 | -1 | 0 | 5
(12 rows)
If needed filter out the added columns, for example:
SELECT seq, path_seq, node, edge, cost, agg_cost FROM pgr_bdAstar(
$$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)
If needed add the new columns, similar to the following example where
pgr_dijkstrais used, and the function had to be modified to be able to return the new columns:
Migration of pgr_dijkstra¶
Starting from v3.5.0
Signatures to be migrated:
pgr_dijkstra(One to One)pgr_dijkstra(One to Many)pgr_dijkstra(Many to One)
- Before Migration:
Output columns were
(seq, path_seq, [start_vid], [end_vid], node, edge, cost, agg_cost)Depending on the overload used, the columns
start_vidandend_vidmight be missing:pgr_dijkstra(One to One) does not havestart_vidandend_vid.pgr_dijkstra(One to Many) does not havestart_vid.pgr_dijkstra(Many to One) does not haveend_vid.
- Migration:
Be aware of the existence of the additional columns.
In
pgr_dijkstra(One to One)start_vidcontains the start vid parameter value.end_vidcontains the end vid parameter value.
SELECT * FROM pgr_dijkstra(
$$SELECT id, source, target, cost, reverse_cost 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)
In
pgr_dijkstra(One to Many)start_vidcontains the start vid parameter value.
SELECT * FROM pgr_dijkstra(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
6, ARRAY[3, 10]);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 6 | 3 | 6 | 4 | 1 | 0
2 | 2 | 6 | 3 | 7 | 7 | 1 | 1
3 | 3 | 6 | 3 | 3 | -1 | 0 | 2
4 | 1 | 6 | 10 | 6 | 4 | 1 | 0
5 | 2 | 6 | 10 | 7 | 8 | 1 | 1
6 | 3 | 6 | 10 | 11 | 9 | 1 | 2
7 | 4 | 6 | 10 | 16 | 16 | 1 | 3
8 | 5 | 6 | 10 | 15 | 3 | 1 | 4
9 | 6 | 6 | 10 | 10 | -1 | 0 | 5
(9 rows)
In
pgr_dijkstra(Many to One)end_vidcontains the end vid parameter value.
SELECT * FROM pgr_dijkstra(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
ARRAY[3, 6], 10);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 3 | 10 | 3 | 7 | 1 | 0
2 | 2 | 3 | 10 | 7 | 8 | 1 | 1
3 | 3 | 3 | 10 | 11 | 9 | 1 | 2
4 | 4 | 3 | 10 | 16 | 16 | 1 | 3
5 | 5 | 3 | 10 | 15 | 3 | 1 | 4
6 | 6 | 3 | 10 | 10 | -1 | 0 | 5
7 | 1 | 6 | 10 | 6 | 4 | 1 | 0
8 | 2 | 6 | 10 | 7 | 8 | 1 | 1
9 | 3 | 6 | 10 | 11 | 9 | 1 | 2
10 | 4 | 6 | 10 | 16 | 16 | 1 | 3
11 | 5 | 6 | 10 | 15 | 3 | 1 | 4
12 | 6 | 6 | 10 | 10 | -1 | 0 | 5
(12 rows)
If needed filter out the added columns, for example:
SELECT seq, path_seq, node, edge, cost, agg_cost FROM pgr_dijkstra(
$$SELECT id, source, target, cost, reverse_cost 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)
Migration of pgr_drivingdistance¶
Starting from v3.6.0 pgr_drivingDistance result columns are being standardized.
- from:
(seq, [from_v,] node, edge, cost, agg_cost)- to:
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)
Signatures to be migrated:
pgr_drivingdistance(Single vertex)pgr_drivingdistance(Multiple vertices)
- Before Migration:
Output columns were (seq, [from_v,] node, edge, cost, agg_cost)
pgr_drivingdistance(Single vertex)Does not have
start_vidanddepthresult columns.
pgr_drivingdistance(Multiple vertices)Has
from_vinstead ofstart_vidresult column.does not have
depthresult column.
- Migration:
Be aware of the existence and name change of the result columns.
pgr_drivingdistance (Single vertex)¶
Using this example.
start_vidcontains the start vid parameter value.depthcontains the depth of thenode.predcontains the predecessor of thenode.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)
If needed filter out the added columns, for example, to return the original 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)
pgr_drivingdistance (Multiple vertices)¶
Using this example.
The
from_vresult column name changes tostart_vid.depthcontains the depth of thenode.predcontains the predecessor of thenode.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)
If needed filter out and rename columns, for example, to return the original columns:
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 pgr_kruskalDD / pgr_kruskalBFS / pgr_kruskalDFS¶
Starting from v3.7.0 pgr_kruskalDD, pgr_kruskalBFS and pgr_kruskalDFS result columns are being standardized.
- from:
(seq, depth, start_vid, node, edge, cost, agg_cost)- to:
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)
pgr_kruskalDDSingle vertex
Multiple vertices
pgr_kruskalDFSSingle vertex
Multiple vertices
pgr_kruskalBFSSingle vertex
Multiple vertices
- Before Migration:
Output columns were (seq, depth, start_vid, node, edge, cost, agg_cost)
Single vertex and Multiple vertices
Do not have
predresult column.
- Migration:
Be aware of the existence of pred result columns.
If needed filter out the added columns
Kruskal single vertex¶
Using pgr_KruskalDD as example.
Migration is similar to al the affected functions.
Comparing with this example.
Now column pred exists and contains the predecessor of the node.
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)
If needed filter out the added columns, for example, to return the original columns
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)
Kruskal multiple vertices¶
Using pgr_KruskalDD as example.
Migration is similar to al the affected functions.
Comparing with this example.
Now column pred exists and contains the predecessor of the node.
SELECT * FROM pgr_kruskalDD(
'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 | 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)
If needed filter out the added columns, for example, to return the original columns
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',
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 | 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)
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)- from:
(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.
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_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:
SELECT * FROM pgr_maxCardinalityMatch(
$$SELECT id, source, target, cost AS going, reverse_cost AS coming FROM edges$$,
directed => true
);
WARNING: pgr_maxCardinalityMatch(text,boolean) deprecated signature on v3.4.0
seq | edge | source | target
-----+------+--------+--------
1 | 1 | 5 | 6
2 | 5 | 10 | 11
3 | 6 | 1 | 3
4 | 13 | 12 | 17
5 | 14 | 8 | 9
6 | 16 | 15 | 16
7 | 17 | 2 | 4
8 | 18 | 13 | 14
(8 rows)
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 pgr_primDD / pgr_primBFS / pgr_primDFS¶
Starting from v3.7.0 pgr_primDD, pgr_primBFS and pgr_primDFS result columns are being standardized.
- from:
(seq, depth, start_vid, node, edge, cost, agg_cost)- to:
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)
pgr_primDDSingle vertex
Multiple vertices
pgr_primDFSSingle vertex
Multiple vertices
pgr_primBFSSingle vertex
Multiple vertices
- Before Migration:
Output columns were (seq, depth, start_vid, node, edge, cost, agg_cost)
Single vertex and Multiple vertices
Do not have
predresult column.
- Migration:
Be aware of the existence of pred result columns.
If needed filter out the added columns
Prim single vertex¶
Using pgr_primDD as example.
Migration is similar to al the affected functions.
Comparing with this example.
Now column pred exists and contains the predecessor of the node.
SELECT * FROM pgr_primDD(
'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 | 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
(12 rows)
If needed filter out the added columns, for example, to return the original columns
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',
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
(12 rows)
Prim multiple vertices¶
Using pgr_primDD as example.
Migration is similar to al the affected functions.
Comparing with this example.
Now column pred exists and contains the predecessor of the node.
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)
If needed filter out the added columns, for example, to return the original columns
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)
Migration of pgr_withPointsDD¶
Starting from v3.6.0 pgr_withPointsDD - Proposed result columns are being standardized.
- from:
(seq, [start_vid], node, edge, cost, agg_cost)- to:
(seq, depth, start_vid, pred, 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_withPointsDD(Single vertex)pgr_withPointsDD(Multiple vertices)
- Before Migration:
pgr_withPointsDD(Single vertex)Output columns were
(seq, node, edge, cost, agg_cost)Does not have
start_vid,predanddepthresult columns.driving_sideparameter was named optional now it is compulsory unnamed.
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.
Driving side was optional
The default values on this query are:
- directed:
true
- driving_side:
‘b’
- details:
false
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);
WARNING: pgr_withpointsdd(text,text,bigint,double precision,boolean,character,boolean) deprecated signature on 3.6.0
seq | node | edge | cost | agg_cost
-----+------+------+------+----------
1 | -1 | -1 | 0 | 0
2 | 5 | 1 | 0.4 | 0.4
3 | 6 | 1 | 0.6 | 0.6
4 | 7 | 4 | 1 | 1.6
5 | 3 | 7 | 1 | 2.6
6 | 8 | 10 | 1 | 2.6
7 | 11 | 8 | 1 | 2.6
8 | -3 | 12 | 0.6 | 3.2
9 | -4 | 6 | 0.7 | 3.3
(9 rows)
Driving side was named optional
The default values on this query are:
- directed:
true
- details:
false
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, driving_side => 'r');
WARNING: pgr_withpointsdd(text,text,bigint,double precision,boolean,character,boolean) deprecated signature on 3.6.0
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 | 7 | 4 | 1 | 2.4
(4 rows)
On directed graph b could be used as driving side
The default values on this query are:
- details:
false
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, directed => true, driving_side => 'b');
WARNING: pgr_withpointsdd(text,text,bigint,double precision,boolean,character,boolean) deprecated signature on 3.6.0
seq | node | edge | cost | agg_cost
-----+------+------+------+----------
1 | -1 | -1 | 0 | 0
2 | 5 | 1 | 0.4 | 0.4
3 | 6 | 1 | 0.6 | 0.6
4 | 7 | 4 | 1 | 1.6
5 | 3 | 7 | 1 | 2.6
6 | 8 | 10 | 1 | 2.6
7 | 11 | 8 | 1 | 2.6
8 | -3 | 12 | 0.6 | 3.2
9 | -4 | 6 | 0.7 | 3.3
(9 rows)
On undirected graph r could be used as driving side
Also l could be used as driving side
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', directed => 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 | 7 | 4 | 1 | 2.4
(4 rows)
- After Migration:
Be aware of the existence of the additional result Columns.
New 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.
pgr_withPointsDD (Single vertex)¶
Using this example.
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)start_vidcontains the start vid parameter value.depthcontains the depth from thestart_vidvertex to thenode.predcontains the predecessor of thenode.
To migrate, use an unnamed valid value for driving side after the distance parameter:
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', directed => 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 | 7 | 4 | 1 | 2.4
(4 rows)
To get results from previous versions:
filter out the additional columns, for example;
When
details => falseto remove the points useWHERE node >= 0 OR cost = 0
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)
pgr_withPointsDD (Multiple vertices)¶
Using this example.
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)depthcontains the depth from thestart_vidvertex to thenode.predcontains the predecessor of thenode.
SELECT * FROM pgr_withPointsDD(
$$SELECT * FROM edges ORDER BY id$$,
$$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
ARRAY[-1, 16], 3.3, 'l', equicost => true);
seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
1 | 0 | -1 | -1 | -1 | -1 | 0 | 0
2 | 1 | -1 | -1 | 6 | 1 | 0.6 | 0.6
3 | 2 | -1 | 6 | 7 | 4 | 1 | 1.6
4 | 2 | -1 | 6 | 5 | 1 | 1 | 1.6
5 | 3 | -1 | 7 | 3 | 7 | 1 | 2.6
6 | 3 | -1 | 7 | 8 | 10 | 1 | 2.6
7 | 4 | -1 | 8 | -3 | 12 | 0.6 | 3.2
8 | 4 | -1 | 3 | -4 | 6 | 0.7 | 3.3
9 | 0 | 16 | 16 | 16 | -1 | 0 | 0
10 | 1 | 16 | 16 | 11 | 9 | 1 | 1
11 | 1 | 16 | 16 | 15 | 16 | 1 | 1
12 | 1 | 16 | 16 | 17 | 15 | 1 | 1
13 | 2 | 16 | 15 | 10 | 3 | 1 | 2
14 | 2 | 16 | 11 | 12 | 11 | 1 | 2
(14 rows)
To get results from previous versions:
Filter out the additional columns
When
details => falseto remove the points useWHERE node >= 0 OR cost = 0
SELECT seq, start_vid, 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$$,
ARRAY[-1, 16], 3.3, 'l', equicost => true) WHERE node >= 0 OR cost = 0;
seq | start_vid | node | edge | cost | agg_cost
-----+-----------+------+------+------+----------
1 | -1 | -1 | -1 | 0 | 0
2 | -1 | 6 | 1 | 0.6 | 0.6
3 | -1 | 7 | 4 | 1 | 1.6
4 | -1 | 5 | 1 | 1 | 1.6
5 | -1 | 3 | 7 | 1 | 2.6
6 | -1 | 8 | 10 | 1 | 2.6
9 | 16 | 16 | -1 | 0 | 0
10 | 16 | 11 | 9 | 1 | 1
11 | 16 | 15 | 16 | 1 | 1
12 | 16 | 17 | 15 | 1 | 1
13 | 16 | 10 | 3 | 1 | 2
14 | 16 | 12 | 11 | 1 | 2
(12 rows)
Migration of pgr_withPointsKSP¶
Starting from v3.6.0 pgr_withPointsKSP - Proposed result columns are being standardized.
- from:
(seq, path_id, path_seq, node, edge, cost, agg_cost)- from:
(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)
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 - Proposed (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
Use pgr_withPoints when there are no restrictions.¶
Use pgr_withPoints - Proposed (Ont 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,
details => false);
seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
1 | 1 | -4 | 6 | 0.7 | 0
2 | 2 | 3 | 7 | 1 | 0.7
3 | 3 | 7 | 10 | 1 | 1.7
4 | 4 | 8 | 12 | 0.6 | 2.7
5 | 5 | -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,
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 - Proposed 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,
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,
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_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 - Proposed 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 - Proposed 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],
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],
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 - Proposed 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],
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],
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
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 \(3 \rightarrow 5
\rightarrow9\)
\(3\rightarrow5\)
is on column
via_pathin reverse orderis of type
TEXT
\(9\)
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 \(3 \rightarrow5
\rightarrow9\).
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