Migration guide - pgRouting Manual (3.7)
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 functions
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_kruskalDD-
Single vertex
-
Multiple vertices
-
-
pgr_kruskalDFS-
Single vertex
-
Multiple vertices
-
-
pgr_kruskalBFS-
Single 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 query -
Results 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_primDD-
Single vertex
-
Multiple vertices
-
-
pgr_primDFS-
Single vertex
-
Multiple vertices
-
-
pgr_primBFS-
Single 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 turn restrictions
Migration of restrictions
Starting from v3.4.0
The structure of the restrictions have changed:
Old restrictions structure
On the deprecated signatures:
-
Column
ridis ignored -
via_path-
Must be in reverse order.
-
Is of type
TEXT. -
When more than one via edge must be separated with
,.
-
-
target_id-
Is the last edge of the forbidden path.
-
Is of type
INTEGER.
-
-
to_cost-
Is 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 order -
is of type
TEXT
-
-
\(9\)
-
is on column
target_id -
is of type
INTEGER
-
New restrictions structure
-
Column
idis ignored -
Column
path-
Is of type
ARRAY[ANY-INTEGER]. -
Contains all the edges involved on the restriction.
-
The array has the ordered edges of the restriction.
-
-
Column
cost-
Is 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)
Migration of
pgr_trsp
(Vertices)
pgr_trsp - Proposed signatures have changed and many issues have been fixed in the new signatures. This section will show how to migrate from the old signatures to the new replacement functions. This also affects the restrictions.
Starting from v3.4.0
Signature to be migrated:
pgr_trsp(Edges SQL, source, target,
directed boolean, has_rcost boolean
[,restrict_sql text]);
RETURNS SETOF (seq, id1, id2, cost)
-
The integral type of the
Edges SQLcan only beINTEGER. -
The floating point type of the
Edges SQLcan only beFLOAT. -
directedflag is compulsory.-
Does not have a default value.
-
-
Does not autodetect if
reverse_costcolumn exist.-
User must be careful to match the existence of the column with the value of
has_rcostparameter.
-
-
The restrictions inner query is optional.
-
The output column names are meaningless
Migrate by using:
-
pgr_dijkstra when there are no restrictions,
-
pgr_trsp - Proposed (One to One) when there are restrictions.
Migrating
pgr_trsp
(Vertices) using
pgr_dijkstra
The following query does not have restrictions.
SELECT * FROM pgr_trsp(
$$SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost
FROM edges WHERE id != 16$$,
15, 16,
true, true);
WARNING: pgr_trsp(text,integer,integer,boolean,boolean) deprecated signature on v3.4.0
seq id1 id2 cost
-----+-----+-----+------
0 15 3 1
1 10 5 1
2 11 9 1
3 16 -1 0
(4 rows)
-
A message about deprecation is shown
-
Deprecated functions will be removed on the next mayor version 4.0.0
-
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)
-
The types casting has been removed.
-
-
Autodetects if
reverse_costcolumn is in the edges SQL. -
Accepts
ANY-INTEGERon integral types -
Accepts
ANY-NUMERICALon floating point types -
directedflag has a default value oftrue.-
Use the same value that on the original query.
-
In this example it is
truewhich is the default value.-
The flag has been omitted and the default is been used.
-
-
-
When the need of using strictly the same (meaningless) names and types of the function been migrated then:
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 node -
id2is the edge
Migrating
pgr_trsp
(Vertices) using
pgr_trsp
The following query has restrictions.
SELECT * FROM pgr_trsp(
$$SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost
FROM edges WHERE id != 16$$,
15, 16,
true, true,
$$SELECT to_cost, target_id::INTEGER, via_path
FROM old_restrictions$$);
WARNING: pgr_trsp(text,integer,integer,boolean,boolean) deprecated signature on v3.4.0
seq id1 id2 cost
-----+-----+-----+------
0 15 3 1
1 10 5 1
2 11 11 1
3 12 13 1
4 17 15 1
5 16 -1 0
(6 rows)
-
A message about deprecation is shown
-
Deprecated functions will be removed on the next mayor version 4.0.0
-
-
The restrictions are the last parameter of the function
-
Using the old structure of 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)
-
The new structure of restrictions is been used.
-
It is the second parameter.
-
-
The types casting has been removed.
-
-
Autodetects if
reverse_costcolumn is in the edges SQL. -
Accepts
ANY-INTEGERon integral types -
Accepts
ANY-NUMERICALon floating point types -
directedflag has a default value oftrue.-
Use the same value that on the original query.
-
In this example it is
truewhich is the default value.-
The flag has been omitted and the default is been used.
-
-
-
When the need of using strictly the same (meaningless) names and types of the function been migrated then:
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 node -
id2is the edge
Migration of
pgr_trsp
(Edges)
Signature to be migrated:
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)
-
The integral types of the
sqlcan only beINTEGER. -
The floating point type of the
sqlcan only beFLOAT. -
directedflag is compulsory.-
Does not have a default value.
-
-
Does not autodetect if
reverse_costcolumn exist.-
User must be careful to match the existence of the column with the value of
has_rcostparameter.
-
-
The restrictions inner query is optional.
For these migration guide the following points will be used:
SELECT pid, edge_id, fraction, side FROM pointsOfInterest
WHERE pid IN (3, 4);
pid edge_id fraction side
-----+---------+----------+------
3 12 0.6 l
4 6 0.3 r
(2 rows)
Migrate by using:
-
pgr_withPoints - Proposed when there are no restrictions,
-
pgr_trsp_withPoints - Proposed (One to One) when there are restrictions.
Migrating
pgr_trsp
(Edges) using
pgr_withPoints
The following query does not have restrictions.
SELECT * FROM pgr_trsp(
$$SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost
FROM edges$$,
6, 0.3, 12, 0.6,
true, true);
WARNING: pgr_trsp(text,integer,float,integer,float,boolean,boolean) deprecated signature on v3.4.0
seq id1 id2 cost
-----+-----+-----+------
0 -1 6 0.7
1 3 7 1
2 7 10 1
3 8 12 0.6
4 -2 -1 0
(5 rows)
-
A message about deprecation is shown
-
Deprecated functions will be removed on the next mayor version 4.0.0
-
Use pgr_withPoints - Proposed 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)
-
The types casting has been removed.
-
Do not show details, as the deprecated function does not show details.
-
-
Autodetects if
reverse_costcolumn is in the edges SQL. -
Accepts
ANY-INTEGERon integral types -
Accepts
ANY-NUMERICALon floating point types -
directedflag has a default value oftrue.-
Use the same value that on the original query.
-
In this example it is
truewhich is the default value.-
The flag has been omitted and the default is been used.
-
-
-
On the points query do not include the
sidecolumn.
-
When the need of using strictly the same (meaningless) names and types, and node values of the function been migrated then:
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 node -
id2is the edge
Migrating
pgr_trsp
(Edges) using
pgr_trsp_withPoints
The following query has restrictions.
SELECT * FROM pgr_trsp(
$$SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost FROM edges$$,
6, 0.3, 12, 0.6, true, true,
$$SELECT to_cost, target_id::INTEGER, via_path FROM old_restrictions$$);
WARNING: pgr_trsp(text,integer,float,integer,float,boolean,boolean) deprecated signature on v3.4.0
seq id1 id2 cost
-----+-----+-----+------
0 -1 6 0.7
1 3 7 1
2 7 8 1
3 11 9 1
4 16 16 1
5 15 3 1
6 10 2 1
7 6 4 1
8 7 10 1
9 8 12 0.6
(10 rows)
-
A message about deprecation is shown
-
Deprecated functions will be removed on the next mayor version 4.0.0
-
-
The restrictions are the last parameter of the function
-
Using the old structure of 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)
-
The new structure of restrictions is been used.
-
It is the second parameter.
-
-
The types casting has been removed.
-
Do not show details, as the deprecated function does not show details.
-
pgr_trsp_withPoints - Proposed :
-
Autodetects if
reverse_costcolumn is in the edges SQL. -
Accepts
ANY-INTEGERon integral types -
Accepts
ANY-NUMERICALon floating point types -
directedflag has a default value oftrue.-
Use the same value that on the original query.
-
In this example it is
truewhich is the default value.-
The flag has been omitted and the default is been used.
-
-
-
On the points query do not include the
sidecolumn.
-
When the need of using strictly the same (meaningless) names and types, and node values of the function been migrated then:
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 node -
id2is the edge
Migration of
pgr_trspViaVertices
Signature to be migrated:
pgr_trspViaVertices(sql text, vids integer[],
directed boolean, has_rcost boolean
[, turn_restrict_sql text]);
RETURNS SETOF (seq, id1, id2, id3, cost)
-
The integral types of the
Edges SQLcan only beINTEGER. -
The floating point type of the
Edges SQLcan only beFLOAT. -
directedflag is compulsory.-
Does not have a default value.
-
-
Does not autodetect if
reverse_costcolumn exist.-
User must be careful to match the existence of the column with the value of
has_rcostparameter.
-
-
The restrictions inner query is optional.
Migrate by using:
-
pgr_dijkstraVia - Proposed when there are no restrictions,
-
pgr_trspVia - Proposed when there are restrictions.
Migrating
pgr_trspViaVertices
using
pgr_dijkstraVia
The following query does not have restrictions.
SELECT * FROM pgr_trspViaVertices(
$$SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost FROM edges$$,
ARRAY[6, 3, 6],
true, true);
WARNING: pgr_trspViaVertices(text,anyarray,boolean,boolean,text) deprecated function on v3.4.0
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)
-
A message about deprecation is shown
-
Deprecated functions will be removed on the next mayor version 4.0.0
-
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)
-
The types casting has been removed.
-
-
Autodetects if
reverse_costcolumn is in the edges SQL. -
Accepts
ANY-INTEGERon integral types -
Accepts
ANY-NUMERICALon floating point types -
directedflag has a default value oftrue.-
Use the same value that on the original query.
-
In this example it is
truewhich is the default value.-
The flag has been omitted and the default is been used.
-
-
-
On the points query do not include the
sidecolumn.
-
When the need of using strictly the same (meaningless) names and types of the function been migrated then:
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 identifier -
id2is the node -
id3is the edge
Migrating
pgr_trspViaVertices
using
pgr_trspVia
The following query has restrictions.
SELECT * FROM pgr_trspViaVertices(
$$SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost FROM edges$$,
ARRAY[6, 3, 6],
true, true,
$$SELECT to_cost, target_id::INTEGER, via_path FROM old_restrictions$$);
WARNING: pgr_trspViaVertices(text,anyarray,boolean,boolean,text) deprecated function on v3.4.0
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)
-
A message about deprecation is shown
-
Deprecated functions will be removed on the next mayor version 4.0.0
-
-
The restrictions are the last parameter of the function
-
Using the old structure of 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)
-
The new structure of restrictions is been used.
-
It is the second parameter.
-
-
The types casting has been removed.
-
-
Autodetects if
reverse_costcolumn is in the edges SQL. -
Accepts
ANY-INTEGERon integral types -
Accepts
ANY-NUMERICALon floating point types -
directedflag has a default value oftrue.-
Use the same value that on the original query.
-
In this example it is
truewhich is the default value.-
The flag has been omitted and the default is been used.
-
-
-
On the points query do not include the
sidecolumn.
-
When the need of using strictly the same (meaningless) names and types of the function been migrated then:
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 identifier -
id2is the node -
id3is the edge
Migration of
pgr_trspViaEdges
Signature to be migrated:
pgr_trspViaEdges(sql text, eids integer[], pcts float8[],
directed boolean, has_rcost boolean
[, turn_restrict_sql text]);
RETURNS SETOF (seq, id1, id2, id3, cost)
-
The integral types of the
Edges SQLcan only beINTEGER. -
The floating point type of the
Edges SQLcan only beFLOAT. -
directedflag is compulsory.-
Does not have a default value.
-
-
Does not autodetect if
reverse_costcolumn exist.-
User must be careful to match the existence of the column with the value of
has_rcostparameter.
-
-
The restrictions inner query is optional.
For these migration guide the following points will be used:
SELECT pid, edge_id, fraction, side FROM pointsOfInterest
WHERE pid IN (3, 4, 6);
pid edge_id fraction side
-----+---------+----------+------
3 12 0.6 l
4 6 0.3 r
6 4 0.7 b
(3 rows)
And will travel thru the following Via points \(4\rightarrow3\rightarrow6\)
Migrate by using:
-
pgr_withPointsVia - Proposed when there are no restrictions,
-
pgr_trspVia_withPoints - Proposed when there are restrictions.
Migrating
pgr_trspViaEdges
using
pgr_withPointsVia
The following query does not have restrictions.
SELECT * FROM pgr_trspViaEdges(
$$SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost FROM edges$$,
ARRAY[6, 12, 4], ARRAY[0.3, 0.6, 0.7],
true, true);
WARNING: pgr_trspViaEdges(text,integer[],float[],boolean,boolean,text) deprecated function on v3.4.0
seq id1 id2 id3 cost
-----+-----+-----+-----+------
1 1 -1 6 0.7
2 1 3 7 1
3 1 7 10 1
4 1 8 12 0.6
5 1 -2 -1 0
6 2 -2 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.7
12 2 -3 -2 0
(12 rows)
-
A message about deprecation is shown
-
Deprecated functions will be removed on the next mayor version 4.0.0
-
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)
-
The types casting has been removed.
-
Do not show details, as the deprecated function does not show details.
-
pgr_withPointsVia - Proposed :
-
Autodetects if
reverse_costcolumn is in the edges SQL. -
Accepts
ANY-INTEGERon integral types -
Accepts
ANY-NUMERICALon floating point types -
directedflag has a default value oftrue.-
Use the same value that on the original query.
-
In this example it is
truewhich is the default value.-
The flag has been omitted and the default is been used.
-
-
-
On the points query do not include the
sidecolumn.
-
When the need of using strictly the same (meaningless) names and types, and node values of the function been migrated then:
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 * 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 10 1
4 1 8 12 0.6
5 1 -2 -1 0
6 2 -2 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 -3 -1 0
(12 rows)
-
id1is the path identifier -
id2is the node -
id3is the edge
Migrating
pgr_trspViaEdges
using
pgr_trspVia_withPoints
The following query has restrictions.
SELECT * FROM pgr_trspViaEdges(
$$SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost FROM edges$$,
ARRAY[6, 12, 4], ARRAY[0.3, 0.6, 0.7],
true, true,
$$SELECT to_cost, target_id::INTEGER, via_path FROM old_restrictions$$);
WARNING: pgr_trspViaEdges(text,integer[],float[],boolean,boolean,text) deprecated function on v3.4.0
WARNING: pgr_trsp(text,integer,float,integer,float,boolean,boolean) deprecated signature on v3.4.0
WARNING: pgr_trsp(text,integer,float,integer,float,boolean,boolean) deprecated signature on v3.4.0
seq id1 id2 id3 cost
-----+-----+-----+-----+------
1 1 -1 6 0.7
2 1 3 7 1
3 1 7 8 1
4 1 11 9 1
5 1 16 16 1
6 1 15 3 1
7 1 10 2 1
8 1 6 4 1
9 1 7 10 1
10 1 8 12 1
11 2 12 13 1
12 2 17 15 1
13 2 16 9 1
14 2 11 8 1
15 2 7 4 0.3
(15 rows)
-
A message about deprecation is shown
-
Deprecated functions will be removed on the next mayor version 4.0.0
-
-
The restrictions are the last parameter of the function
-
Using the old structure of 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)
-
The new structure of restrictions is been used.
-
It is the second parameter.
-
-
The types casting has been removed.
-
Do not show details, as the deprecated function does not show details.
-
pgr_trspVia_withPoints - Proposed :
-
Autodetects if
reverse_costcolumn is in the edges SQL. -
Accepts
ANY-INTEGERon integral types -
Accepts
ANY-NUMERICALon floating point types -
directedflag has a default value oftrue.-
Use the same value that on the original query.
-
In this example it is
truewhich is the default value.-
The flag has been omitted and the default is been used.
-
-
-
On the points query do not include the
sidecolumn.
-
When the need of using strictly the same (meaningless) names and types, and node values of the function been migrated then:
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 identifier -
id2is the node -
id3is the edge
See Also
Indices and tables