迁移指南¶
多个函数的签名发生了变化，和/或已被新函数取代。
结果可能会因变化而不同。
Warning
所有已弃用函数将在下一个主版本 4.0.0 中被移除
Migration to standardized columns¶
There has been an effort to standardize function output columns names and types.
-
(start_vid, end_vid, agg_cost)
Result columns for single path functions
(seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
-
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)
Function |
迁移指南 |
|---|---|
Changed in version 3.5.0: pgr_dijkstra` [1] |
|
Changed in version 3.6.0: pgr_bdAstar [1] |
|
Changed in version 3.6.0: pgr_drivingDistance [1] |
|
Changed in version 3.6.0: pgr_withPointsDD [2] |
|
Changed in version 3.7.0: pgr_kruskalBFS [1] |
|
Changed in version 3.7.0: pgr_kruskalDD [1] |
|
Changed in version 3.7.0: pgr_kruskalDFS [1] |
|
Changed in version 3.7.0: pgr_primBFS [1] |
|
Changed in version 3.7.0: pgr_primDD [1] |
|
Changed in version 3.7.0: pgr_primDFS [1] |
|
Changed in version 4.0.0: pgr_bdDijkstra [1] |
|
Changed in version 4.0.0: pgr_bellmanFord - 实验性 [3] |
|
Changed in version 4.0.0: pgr_binaryBreadthFirstSearch - 实验性 [3] |
|
Changed in version 4.0.0: pgr_dagShortestPath - 实验性 [3] |
|
Changed in version 4.0.0: pgr_edwardMoore - 实验性 [3] |
|
Changed in version 4.0.0: pgr_withPoints [2] |
|
Changed in version 4.0.0: pgr_withPointsCost [2] |
|
Changed in version 4.0.0: pgr_withPointsCostMatrix [2] |
Migration of cost functions¶
The standardized 成本函数结果列 are
(start_vid, end_vid, agg_cost)
The following functions need to be migrated when they are being used in an application.
Function |
Version |
From |
|---|---|---|
|
v < 4.0 |
|
|
v < 4.0 |
|
to (start_vid, end_vid, agg_cost)
Migration of (start_pid, end_pid, agg_cost)
要迁移的签名：
One to One
一对多
Many to One
Many to Many
组合
Warning
Breaking change
If using pgr_withPointsCost: column names must be changed after updating
pgRouting
New output columns are (start_vid, end_vid, agg_cost)
To get the old version column names: rename start_vid to start_pid and
end_vid to end_pid.
Examples for One to One for cost functions¶
Using pgr_withPointsCost
Migrating this v3.8 example.
- 从:
(start_pid, end_pid, agg_cost)- 到:
(start_vid, end_vid, agg_cost)
SELECT * FROM pgr_withPointsCost(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
-1, 10, 'r');
start_vid | end_vid | agg_cost
-----------+---------+----------
-1 | 10 | 6.4
(1 row)
To get the old version column names: rename start_vid to start_pid and
end_vid to end_pid.
SELECT start_vid AS start_pid, end_vid AS end_pid, agg_cost
FROM pgr_withPointsCost(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
-1, 10, 'r');
start_pid | end_pid | agg_cost
-----------+---------+----------
-1 | 10 | 6.4
(1 row)
Warning
Breaking change
If using pgr_withPointsCost: column names must be changed after updating
pgRouting
Examples for One to Many for cost functions¶
Using pgr_withPointsCost
Migrating this v3.8 example.
- 从:
(start_pid, end_pid, agg_cost)- 到:
(start_vid, end_vid, agg_cost)
SELECT * FROM pgr_withPointsCost(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
-1, ARRAY[-3, 7],
'b', directed => false);
start_vid | end_vid | agg_cost
-----------+---------+----------
-1 | -3 | 3.2
-1 | 7 | 1.6
(2 rows)
To get the old version column names: rename start_vid to start_pid and
end_vid to end_pid.
SELECT start_vid AS start_pid, end_vid AS end_pid, agg_cost
FROM pgr_withPointsCost(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
-1, ARRAY[-3, 7],
'b', directed => false);
start_pid | end_pid | agg_cost
-----------+---------+----------
-1 | -3 | 3.2
-1 | 7 | 1.6
(2 rows)
Warning
Breaking change
If using pgr_withPointsCost: column names must be changed after updating
pgRouting
Examples for Many to One for cost functions¶
Using pgr_withPointsCost
Migrating this v3.8 example.
- 从:
(start_pid, end_pid, agg_cost)- 到:
(start_vid, end_vid, agg_cost)
SELECT * FROM pgr_withPointsCost(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
ARRAY[-1, 6], -3, 'r');
start_vid | end_vid | agg_cost
-----------+---------+----------
-1 | -3 | 4
6 | -3 | 2.6
(2 rows)
To get the old version column names: rename start_vid to start_pid and
end_vid to end_pid.
SELECT start_vid AS start_pid, end_vid AS end_pid, agg_cost
FROM pgr_withPointsCost(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
ARRAY[-1, 6], -3, 'r');
start_pid | end_pid | agg_cost
-----------+---------+----------
-1 | -3 | 4
6 | -3 | 2.6
(2 rows)
Warning
Breaking change
If using pgr_withPointsCost: column names must be changed after updating
pgRouting
Examples for Many to Many for cost functions¶
Using pgr_withPointsCost
Migrating this v3.8 example.
- 从:
(start_pid, end_pid, agg_cost)- 到:
(start_vid, end_vid, agg_cost)
SELECT * FROM pgr_withPointsCost(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
ARRAY[-1, 6], ARRAY[-3, 1], 'r');
start_vid | end_vid | agg_cost
-----------+---------+----------
-1 | -3 | 4
-1 | 1 | 4.4
6 | -3 | 2.6
6 | 1 | 3
(4 rows)
To get the old version column names: rename start_vid to start_pid and
end_vid to end_pid.
SELECT start_vid AS start_pid, end_vid AS end_pid, agg_cost
FROM pgr_withPointsCost(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
ARRAY[-1, 6], ARRAY[-3, 1], 'r');
start_pid | end_pid | agg_cost
-----------+---------+----------
-1 | -3 | 4
-1 | 1 | 4.4
6 | -3 | 2.6
6 | 1 | 3
(4 rows)
Using pgr_withPointsCost
Warning
Breaking change
If using pgr_withPointsCost: column names must be changed after updating
pgRouting
Examples for Combinations for cost functions¶
Using pgr_withPointsCost
Migrating this v3.8 example.
- 从:
(start_pid, end_pid, agg_cost)- 到:
(start_vid, end_vid, agg_cost)
SELECT * FROM pgr_withPointsCost(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
'SELECT * FROM (VALUES (-1, 10), (6, -3)) AS combinations(source, target)',
'r');
start_vid | end_vid | agg_cost
-----------+---------+----------
-1 | 10 | 6.4
6 | -3 | 2.6
(2 rows)
To get the old version column names: rename start_vid to start_pid and
end_vid to end_pid.
SELECT start_vid AS start_pid, end_vid AS end_pid, agg_cost
FROM pgr_withPointsCost(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
'SELECT * FROM (VALUES (-1, 10), (6, -3)) AS combinations(source, target)',
'r');
start_pid | end_pid | agg_cost
-----------+---------+----------
-1 | 10 | 6.4
6 | -3 | 2.6
(2 rows)
Warning
Breaking change
If using pgr_withPointsCost: column names must be changed after updating
pgRouting
Migration of single path functions¶
THe standardized Result columns for single path functions are (seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
The following functions need to be migrated when they are being used in an application.
Function |
Version |
From |
|---|---|---|
pgr_dijkstra` |
v < 3.5 |
|
|
v < 3.6 |
|
|
v < 4.0 |
|
|
v < 4.0 |
|
|
v < 4.0 |
|
|
v < 4.0 |
|
|
v < 4.0 |
|
- 到:
(seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
Migration of (seq, path_seq, [start_vid], [end_vid], node, edge, cost, agg_cost)
要迁移的签名：
One to One
一对多
Many to One
Before updating pgRouting, enumerate the corresponding columns of the signature
Skip when applicable,
start_vidSkip when applicable,
end_vid
Migration of (seq, path_seq, [start_pid], [end_pid], node, edge, cost, agg_cost)
要迁移的签名：
One to One
一对多
Many to One
Many to Many
组合
To get the old version column names, depending on the signature:
Filter out the columns:
start_vidand/orend_vidRename the columns:
start_vidtostart_pidend_vidtoend_pid
Warning
Breaking change
If using pgr_withPoints: column names must be changed after updating
pgRouting
Migration of (seq, path_seq, node, edge, cost, agg_cost)
要迁移的签名：
One to One
一对多
Many to One
Many to Many
组合
To get the old version column names:
Filter out the columns:
start_vidandend_vid
Examples with One to One with one route result¶
Using pgr_aStar
Migrating this v3.5 example.
SELECT * FROM pgr_aStar(
$$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
6, 10);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 6 | 10 | 6 | 4 | 1 | 0
2 | 2 | 6 | 10 | 7 | 8 | 1 | 1
3 | 3 | 6 | 10 | 11 | 9 | 1 | 2
4 | 4 | 6 | 10 | 16 | 16 | 1 | 3
5 | 5 | 6 | 10 | 15 | 3 | 1 | 4
6 | 6 | 6 | 10 | 10 | -1 | 0 | 5
(6 rows)
Before updating pgRouting enumerate the columns: (seq, path_seq, node, edge, cost, agg_cost)
SELECT seq, path_seq, node, edge, cost, agg_cost FROM pgr_aStar(
$$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
6, 10);
seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
1 | 1 | 6 | 4 | 1 | 0
2 | 2 | 7 | 8 | 1 | 1
3 | 3 | 11 | 9 | 1 | 2
4 | 4 | 16 | 16 | 1 | 3
5 | 5 | 15 | 3 | 1 | 4
6 | 6 | 10 | -1 | 0 | 5
(6 rows)
Using pgr_bdDijkstra
Migrating this v3.8 example.
SELECT * FROM pgr_bdDijkstra(
'select id, source, target, cost, reverse_cost from edges',
6, 10, true);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 6 | 10 | 6 | 4 | 1 | 0
2 | 2 | 6 | 10 | 7 | 8 | 1 | 1
3 | 3 | 6 | 10 | 11 | 9 | 1 | 2
4 | 4 | 6 | 10 | 16 | 16 | 1 | 3
5 | 5 | 6 | 10 | 15 | 3 | 1 | 4
6 | 6 | 6 | 10 | 10 | -1 | 0 | 5
(6 rows)
Before updating pgRouting enumerate the columns: (seq, path_seq, node, edge, cost, agg_cost)
SELECT seq, path_seq, node, edge, cost, agg_cost
FROM pgr_bdDijkstra(
'select id, source, target, cost, reverse_cost from edges',
6, 10, true);
seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
1 | 1 | 6 | 4 | 1 | 0
2 | 2 | 7 | 8 | 1 | 1
3 | 3 | 11 | 9 | 1 | 2
4 | 4 | 16 | 16 | 1 | 3
5 | 5 | 15 | 3 | 1 | 4
6 | 6 | 10 | -1 | 0 | 5
(6 rows)
Using pgr_DAGshortestPath
Migrating this v3.8 example.
SELECT * FROM pgr_DAGshortestPath(
'select id, source, target, cost from edges',
5, 11);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 5 | 11 | 5 | 1 | 1 | 0
2 | 2 | 5 | 11 | 6 | 4 | 1 | 1
3 | 3 | 5 | 11 | 7 | 8 | 1 | 2
4 | 4 | 5 | 11 | 11 | -1 | 0 | 3
(4 rows)
Before updating pgRouting enumerate the columns: (seq, path_seq, node, edge, cost, agg_cost)
SELECT seq, path_seq, node, edge, cost, agg_cost
FROM pgr_DAGshortestPath(
'select id, source, target, cost from edges',
5, 11);
seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
1 | 1 | 5 | 1 | 1 | 0
2 | 2 | 6 | 4 | 1 | 1
3 | 3 | 7 | 8 | 1 | 2
4 | 4 | 11 | -1 | 0 | 3
(4 rows)
Note
This applies to all signatures of pgr_DAGshortestPath
Examples for One to Many with one route result¶
Using pgr_bdAstar
Migrating this v3.5 example.
SELECT * FROM pgr_bdAstar(
$$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
6, ARRAY[10, 12],
heuristic => 3, factor := 3.5);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 6 | 10 | 6 | 4 | 1 | 0
2 | 2 | 6 | 10 | 7 | 8 | 1 | 1
3 | 3 | 6 | 10 | 11 | 9 | 1 | 2
4 | 4 | 6 | 10 | 16 | 16 | 1 | 3
5 | 5 | 6 | 10 | 15 | 3 | 1 | 4
6 | 6 | 6 | 10 | 10 | -1 | 0 | 5
7 | 1 | 6 | 12 | 6 | 4 | 1 | 0
8 | 2 | 6 | 12 | 7 | 8 | 1 | 1
9 | 3 | 6 | 12 | 11 | 11 | 1 | 2
10 | 4 | 6 | 12 | 12 | -1 | 0 | 3
(10 rows)
Before updating pgRouting enumerate the columns: (seq, path_seq, end_vid, node, edge, cost, agg_cost)
SELECT * FROM pgr_bdAstar(
$$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
6, ARRAY[10, 12],
heuristic => 3, factor := 3.5);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 6 | 10 | 6 | 4 | 1 | 0
2 | 2 | 6 | 10 | 7 | 8 | 1 | 1
3 | 3 | 6 | 10 | 11 | 9 | 1 | 2
4 | 4 | 6 | 10 | 16 | 16 | 1 | 3
5 | 5 | 6 | 10 | 15 | 3 | 1 | 4
6 | 6 | 6 | 10 | 10 | -1 | 0 | 5
7 | 1 | 6 | 12 | 6 | 4 | 1 | 0
8 | 2 | 6 | 12 | 7 | 8 | 1 | 1
9 | 3 | 6 | 12 | 11 | 11 | 1 | 2
10 | 4 | 6 | 12 | 12 | -1 | 0 | 3
(10 rows)
Using pgr_withPoints
Migrating this v3.8 example.
SELECT * FROM pgr_withPoints(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
-1, ARRAY[-3, 7],
'b', directed => false);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | -1 | -3 | -1 | 1 | 0.6 | 0
2 | 2 | -1 | -3 | 6 | 4 | 1 | 0.6
3 | 3 | -1 | -3 | 7 | 10 | 1 | 1.6
4 | 4 | -1 | -3 | 8 | 12 | 0.6 | 2.6
5 | 5 | -1 | -3 | -3 | -1 | 0 | 3.2
6 | 1 | -1 | 7 | -1 | 1 | 0.6 | 0
7 | 2 | -1 | 7 | 6 | 4 | 1 | 0.6
8 | 3 | -1 | 7 | 7 | -1 | 0 | 1.6
(8 rows)
Warning
Breaking change
If using pgr_withPoints: column names must be changed after updating
pgRouting
- 从:
(seq, path_seq, end_pid, node, edge, cost, agg_cost)- 到:
(seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
To get the old signature column names: filter out the
column start_vid and rename end_vid to end_pid.
SELECT seq, path_seq, end_vid AS end_pid, node, edge, cost, agg_cost
FROM pgr_withPoints(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
-1, ARRAY[-3, 7],
'b', directed => false);
seq | path_seq | end_pid | node | edge | cost | agg_cost
-----+----------+---------+------+------+------+----------
1 | 1 | -3 | -1 | 1 | 0.6 | 0
2 | 2 | -3 | 6 | 4 | 1 | 0.6
3 | 3 | -3 | 7 | 10 | 1 | 1.6
4 | 4 | -3 | 8 | 12 | 0.6 | 2.6
5 | 5 | -3 | -3 | -1 | 0 | 3.2
6 | 1 | 7 | -1 | 1 | 0.6 | 0
7 | 2 | 7 | 6 | 4 | 1 | 0.6
8 | 3 | 7 | 7 | -1 | 0 | 1.6
(8 rows)
Examples for Many to One with one route result¶
Using pgr_bdDijkstra
Migrating this v3.8 example.
SELECT * FROM pgr_bdDijkstra(
'select id, source, target, cost, reverse_cost from edges',
ARRAY[6, 1], 17);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 1 | 17 | 1 | 6 | 1 | 0
2 | 2 | 1 | 17 | 3 | 7 | 1 | 1
3 | 3 | 1 | 17 | 7 | 8 | 1 | 2
4 | 4 | 1 | 17 | 11 | 11 | 1 | 3
5 | 5 | 1 | 17 | 12 | 13 | 1 | 4
6 | 6 | 1 | 17 | 17 | -1 | 0 | 5
7 | 1 | 6 | 17 | 6 | 4 | 1 | 0
8 | 2 | 6 | 17 | 7 | 8 | 1 | 1
9 | 3 | 6 | 17 | 11 | 11 | 1 | 2
10 | 4 | 6 | 17 | 12 | 13 | 1 | 3
11 | 5 | 6 | 17 | 17 | -1 | 0 | 4
(11 rows)
Before updating pgRouting enumerate the columns: (seq, path_seq, start_vid, node, edge, cost, agg_cost)
SELECT seq, path_seq, start_vid AS start_pid, node, edge, cost, agg_cost
FROM pgr_bdDijkstra(
'select id, source, target, cost, reverse_cost from edges',
ARRAY[6, 1], 17);
seq | path_seq | start_pid | node | edge | cost | agg_cost
-----+----------+-----------+------+------+------+----------
1 | 1 | 1 | 1 | 6 | 1 | 0
2 | 2 | 1 | 3 | 7 | 1 | 1
3 | 3 | 1 | 7 | 8 | 1 | 2
4 | 4 | 1 | 11 | 11 | 1 | 3
5 | 5 | 1 | 12 | 13 | 1 | 4
6 | 6 | 1 | 17 | -1 | 0 | 5
7 | 1 | 6 | 6 | 4 | 1 | 0
8 | 2 | 6 | 7 | 8 | 1 | 1
9 | 3 | 6 | 11 | 11 | 1 | 2
10 | 4 | 6 | 12 | 13 | 1 | 3
11 | 5 | 6 | 17 | -1 | 0 | 4
(11 rows)
Using pgr_dijkstra
Migrating this v3.4 example.
SELECT * FROM pgr_dijkstra(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
ARRAY[6, 1], 17);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 1 | 17 | 1 | 6 | 1 | 0
2 | 2 | 1 | 17 | 3 | 7 | 1 | 1
3 | 3 | 1 | 17 | 7 | 8 | 1 | 2
4 | 4 | 1 | 17 | 11 | 11 | 1 | 3
5 | 5 | 1 | 17 | 12 | 13 | 1 | 4
6 | 6 | 1 | 17 | 17 | -1 | 0 | 5
7 | 1 | 6 | 17 | 6 | 4 | 1 | 0
8 | 2 | 6 | 17 | 7 | 8 | 1 | 1
9 | 3 | 6 | 17 | 11 | 11 | 1 | 2
10 | 4 | 6 | 17 | 12 | 13 | 1 | 3
11 | 5 | 6 | 17 | 17 | -1 | 0 | 4
(11 rows)
Before updating pgRouting enumerate the columns: (seq, path_seq, start_vid, node, edge, cost, agg_cost)
SELECT seq, path_seq, start_vid, node, edge, cost, agg_cost FROM pgr_dijkstra(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
ARRAY[6, 1], 17);
seq | path_seq | start_vid | node | edge | cost | agg_cost
-----+----------+-----------+------+------+------+----------
1 | 1 | 1 | 1 | 6 | 1 | 0
2 | 2 | 1 | 3 | 7 | 1 | 1
3 | 3 | 1 | 7 | 8 | 1 | 2
4 | 4 | 1 | 11 | 11 | 1 | 3
5 | 5 | 1 | 12 | 13 | 1 | 4
6 | 6 | 1 | 17 | -1 | 0 | 5
7 | 1 | 6 | 6 | 4 | 1 | 0
8 | 2 | 6 | 7 | 8 | 1 | 1
9 | 3 | 6 | 11 | 11 | 1 | 2
10 | 4 | 6 | 12 | 13 | 1 | 3
11 | 5 | 6 | 17 | -1 | 0 | 4
(11 rows)
Examples for Many to Many with one route result¶
Using pgr_withPoints
Migrating this v3.8 example.
SELECT * FROM pgr_withPoints(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
ARRAY[-1, 6], ARRAY[-3, 1], 'r');
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | -1 | -3 | -1 | 1 | 1.4 | 0
2 | 2 | -1 | -3 | 6 | 4 | 1 | 1.4
3 | 3 | -1 | -3 | 7 | 10 | 1 | 2.4
4 | 4 | -1 | -3 | 8 | 12 | 0.6 | 3.4
5 | 5 | -1 | -3 | -3 | -1 | 0 | 4
6 | 1 | -1 | 1 | -1 | 1 | 1.4 | 0
7 | 2 | -1 | 1 | 6 | 4 | 1 | 1.4
8 | 3 | -1 | 1 | 7 | 7 | 1 | 2.4
9 | 4 | -1 | 1 | 3 | 6 | 1 | 3.4
10 | 5 | -1 | 1 | 1 | -1 | 0 | 4.4
11 | 1 | 6 | -3 | 6 | 4 | 1 | 0
12 | 2 | 6 | -3 | 7 | 10 | 1 | 1
13 | 3 | 6 | -3 | 8 | 12 | 0.6 | 2
14 | 4 | 6 | -3 | -3 | -1 | 0 | 2.6
15 | 1 | 6 | 1 | 6 | 4 | 1 | 0
16 | 2 | 6 | 1 | 7 | 7 | 1 | 1
17 | 3 | 6 | 1 | 3 | 6 | 1 | 2
18 | 4 | 6 | 1 | 1 | -1 | 0 | 3
(18 rows)
Warning
Breaking change
If using pgr_withPoints: column names must be changed after updating
pgRouting
- 从:
(seq, path_seq, start_pid, end_pid, node, edge, cost, agg_cost)- 到:
(seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
To get the old version column names: rename start_vid to start_pid and
end_vid to end_pid.
SELECT seq, path_seq, start_vid AS start_pid, end_vid AS end_pid,node, edge, cost, agg_cost
FROM pgr_withPoints(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
ARRAY[-1, 6], ARRAY[-3, 1], 'r');
seq | path_seq | start_pid | end_pid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | -1 | -3 | -1 | 1 | 1.4 | 0
2 | 2 | -1 | -3 | 6 | 4 | 1 | 1.4
3 | 3 | -1 | -3 | 7 | 10 | 1 | 2.4
4 | 4 | -1 | -3 | 8 | 12 | 0.6 | 3.4
5 | 5 | -1 | -3 | -3 | -1 | 0 | 4
6 | 1 | -1 | 1 | -1 | 1 | 1.4 | 0
7 | 2 | -1 | 1 | 6 | 4 | 1 | 1.4
8 | 3 | -1 | 1 | 7 | 7 | 1 | 2.4
9 | 4 | -1 | 1 | 3 | 6 | 1 | 3.4
10 | 5 | -1 | 1 | 1 | -1 | 0 | 4.4
11 | 1 | 6 | -3 | 6 | 4 | 1 | 0
12 | 2 | 6 | -3 | 7 | 10 | 1 | 1
13 | 3 | 6 | -3 | 8 | 12 | 0.6 | 2
14 | 4 | 6 | -3 | -3 | -1 | 0 | 2.6
15 | 1 | 6 | 1 | 6 | 4 | 1 | 0
16 | 2 | 6 | 1 | 7 | 7 | 1 | 1
17 | 3 | 6 | 1 | 3 | 6 | 1 | 2
18 | 4 | 6 | 1 | 1 | -1 | 0 | 3
(18 rows)
Examples for combinations with one route result¶
Using pgr_withPoints
Migrating this v3.8 `this example.
SELECT * FROM pgr_withPoints(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
'SELECT * FROM (VALUES (-1, 10), (6, -3)) AS combinations(source, target)',
'r', details => true);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | -1 | 10 | -1 | 1 | 0.4 | 0
2 | 2 | -1 | 10 | 5 | 1 | 1 | 0.4
3 | 3 | -1 | 10 | 6 | 4 | 0.7 | 1.4
4 | 4 | -1 | 10 | -6 | 4 | 0.3 | 2.1
5 | 5 | -1 | 10 | 7 | 8 | 1 | 2.4
6 | 6 | -1 | 10 | 11 | 9 | 1 | 3.4
7 | 7 | -1 | 10 | 16 | 16 | 1 | 4.4
8 | 8 | -1 | 10 | 15 | 3 | 1 | 5.4
9 | 9 | -1 | 10 | 10 | -1 | 0 | 6.4
10 | 1 | 6 | -3 | 6 | 4 | 0.7 | 0
11 | 2 | 6 | -3 | -6 | 4 | 0.3 | 0.7
12 | 3 | 6 | -3 | 7 | 10 | 1 | 1
13 | 4 | 6 | -3 | 8 | 12 | 0.6 | 2
14 | 5 | 6 | -3 | -3 | -1 | 0 | 2.6
(14 rows)
Warning
Breaking change
If using pgr_withPoints: column names must be changed after updating
pgRouting
- 从:
(seq, path_seq, start_pid, end_pid, node, edge, cost, agg_cost)- 到:
(seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
To get the old version column names: rename start_vid to start_pid and
end_vid to end_pid.
SELECT seq, path_seq, start_vid AS start_pid, end_vid AS end_pid,node, edge, cost, agg_cost
FROM pgr_withPoints(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
'SELECT pid, edge_id, fraction, side from pointsOfInterest',
'SELECT * FROM (VALUES (-1, 10), (6, -3)) AS combinations(source, target)',
'r', details => true);
seq | path_seq | start_pid | end_pid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | -1 | 10 | -1 | 1 | 0.4 | 0
2 | 2 | -1 | 10 | 5 | 1 | 1 | 0.4
3 | 3 | -1 | 10 | 6 | 4 | 0.7 | 1.4
4 | 4 | -1 | 10 | -6 | 4 | 0.3 | 2.1
5 | 5 | -1 | 10 | 7 | 8 | 1 | 2.4
6 | 6 | -1 | 10 | 11 | 9 | 1 | 3.4
7 | 7 | -1 | 10 | 16 | 16 | 1 | 4.4
8 | 8 | -1 | 10 | 15 | 3 | 1 | 5.4
9 | 9 | -1 | 10 | 10 | -1 | 0 | 6.4
10 | 1 | 6 | -3 | 6 | 4 | 0.7 | 0
11 | 2 | 6 | -3 | -6 | 4 | 0.3 | 0.7
12 | 3 | 6 | -3 | 7 | 10 | 1 | 1
13 | 4 | 6 | -3 | 8 | 12 | 0.6 | 2
14 | 5 | 6 | -3 | -3 | -1 | 0 | 2.6
(14 rows)
Migration of spanning tree functions¶
The standardized 生成树函数的结果列 are (seq, depth, start_vid, pred, node, edge, cost, agg_cost)
Function |
Version |
From |
|---|---|---|
|
v < 3.7 |
|
|
v < 3.7 |
|
|
v < 3.7 |
|
|
v < 3.7 |
|
|
v < 3.7 |
|
|
v < 3.7 |
|
|
v < 3.6 |
|
|
v < 3.6 |
|
to (seq, depth, start_vid, pred, node, edge, cost, agg_cost)
Migration of (seq, depth, start_vid, node, edge, cost, agg_cost)
要迁移的签名：
单个顶点
多个顶点
Before updating pgRouting enumerate the columns: (seq, depth, start_vid, node, edge, cost, agg_cost)
Migration of (seq, [from_v,] node, edge, cost, agg_cost)
要迁移的签名：
单个顶点
多个顶点
Migration depends on the signature.
For single vertex:
Before updating pgRouting, enumerate
(seq, path_seq, node, edge, cost, agg_cost)columns
For multiple vertices:
Warning
Breaking change
If using pgr_drivingDistance with multiple vertices: column names must be
changed after updating pgRouting.
To get the old version column names (seq, from_v, node, edge, cost, agg_cost)
Enumerate
(seq, from_v, node, edge, cost, agg_cost)Rename
start_vidtofrom_v.
Migration of (seq, [start_vid], node, edge, cost, agg_cost)
要迁移的签名：
单个顶点
多个顶点
Warning
Breaking change
If using pgr_withPointsDD: function call must be changed after updating
pgRouting.
pgr_withPointsDD's parameter driving_side changed from named optional
to unnamed positional parameter (position 5 in the function call) and its
validity differ for directed and undirected graphs.
Migration depends on the signature.
For pgr_withPointsDD Single vertex
To get the * Output columns were (seq, node, edge, cost, agg_cost)
* Does not have start_vid, pred and depth result columns.
* driving_side parameter was named optional now it is compulsory unnamed.
For pgr_withPointsDD (Multiple vertices)
输出列为
(seq, start_vid, node, edge, cost, agg_cost)没有
depth和pred结果列。driving_side参数以前被命名为可选，现在是强制性的未命名参数。
Validity of driving_side:
在有向图上
b可以用作 行驶方向On undirected graph
r,lcould be used as driving side
迁移后
注意附加结果列的存在。
Output columns are
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)
driving side 参数是未命名的强制参数，有向图和无向图的有效值不同。
没有默认值。
在有向图中：有效值为 [
r,R,l,L]在无向图中：有效值为 [
b,B]使用无效值会引发
ERROR。
Examples for single vertex¶
Using pgr_kruskalDD¶
Migrating this v3.6 example.
SELECT * FROM pgr_kruskalDD(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
6, 3.5);
seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
1 | 0 | 6 | 6 | 6 | -1 | 0 | 0
2 | 1 | 6 | 6 | 5 | 1 | 1 | 1
3 | 1 | 6 | 6 | 10 | 2 | 1 | 1
4 | 2 | 6 | 10 | 15 | 3 | 1 | 2
5 | 3 | 6 | 15 | 16 | 16 | 1 | 3
(5 rows)
Before updating pgRouting enumerate the columns: (seq, depth, start_vid, node, edge, cost, agg_cost).
SELECT seq, depth, start_vid, node, edge, cost, agg_cost
FROM pgr_kruskalDD(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
6, 3.5);
seq | depth | start_vid | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+----------
1 | 0 | 6 | 6 | -1 | 0 | 0
2 | 1 | 6 | 5 | 1 | 1 | 1
3 | 1 | 6 | 10 | 2 | 1 | 1
4 | 2 | 6 | 15 | 3 | 1 | 2
5 | 3 | 6 | 16 | 16 | 1 | 3
(5 rows)
Using pgr_primBFS¶
Migrating this v3.6 example.
SELECT * FROM pgr_primBFS(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
6);
seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
1 | 0 | 6 | 6 | 6 | -1 | 0 | 0
2 | 1 | 6 | 6 | 5 | 1 | 1 | 1
3 | 1 | 6 | 6 | 10 | 2 | 1 | 1
4 | 1 | 6 | 6 | 7 | 4 | 1 | 1
5 | 2 | 6 | 10 | 15 | 3 | 1 | 2
6 | 2 | 6 | 10 | 11 | 5 | 1 | 2
7 | 2 | 6 | 7 | 3 | 7 | 1 | 2
8 | 2 | 6 | 7 | 8 | 10 | 1 | 2
9 | 3 | 6 | 11 | 16 | 9 | 1 | 3
10 | 3 | 6 | 11 | 12 | 11 | 1 | 3
11 | 3 | 6 | 3 | 1 | 6 | 1 | 3
12 | 3 | 6 | 8 | 9 | 14 | 1 | 3
13 | 4 | 6 | 12 | 17 | 13 | 1 | 4
(13 rows)
Before updating pgRouting enumerate the columns: (seq, depth, start_vid, node, edge, cost, agg_cost).
SELECT seq, depth, start_vid, node, edge, cost, agg_cost
FROM pgr_primBFS(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
6);
seq | depth | start_vid | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+----------
1 | 0 | 6 | 6 | -1 | 0 | 0
2 | 1 | 6 | 5 | 1 | 1 | 1
3 | 1 | 6 | 10 | 2 | 1 | 1
4 | 1 | 6 | 7 | 4 | 1 | 1
5 | 2 | 6 | 15 | 3 | 1 | 2
6 | 2 | 6 | 11 | 5 | 1 | 2
7 | 2 | 6 | 3 | 7 | 1 | 2
8 | 2 | 6 | 8 | 10 | 1 | 2
9 | 3 | 6 | 16 | 9 | 1 | 3
10 | 3 | 6 | 12 | 11 | 1 | 3
11 | 3 | 6 | 1 | 6 | 1 | 3
12 | 3 | 6 | 9 | 14 | 1 | 3
13 | 4 | 6 | 17 | 13 | 1 | 4
(13 rows)
Using pgr_drivingDistance¶
Migrating this v3.5 example.
SELECT * FROM pgr_drivingDistance(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
11, 3.0);
seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
1 | 0 | 11 | 11 | 11 | -1 | 0 | 0
2 | 1 | 11 | 11 | 7 | 8 | 1 | 1
3 | 1 | 11 | 11 | 12 | 11 | 1 | 1
4 | 1 | 11 | 11 | 16 | 9 | 1 | 1
5 | 2 | 11 | 7 | 3 | 7 | 1 | 2
6 | 2 | 11 | 7 | 6 | 4 | 1 | 2
7 | 2 | 11 | 7 | 8 | 10 | 1 | 2
8 | 2 | 11 | 16 | 15 | 16 | 1 | 2
9 | 2 | 11 | 16 | 17 | 15 | 1 | 2
10 | 3 | 11 | 3 | 1 | 6 | 1 | 3
11 | 3 | 11 | 6 | 5 | 1 | 1 | 3
12 | 3 | 11 | 8 | 9 | 14 | 1 | 3
13 | 3 | 11 | 15 | 10 | 3 | 1 | 3
(13 rows)
Before updating pgRouting, enumerate (seq, node, edge, cost, agg_cost) columns
SELECT seq, node, edge, cost, agg_cost
FROM pgr_drivingDistance(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
11, 3.0);
seq | node | edge | cost | agg_cost
-----+------+------+------+----------
1 | 11 | -1 | 0 | 0
2 | 7 | 8 | 1 | 1
3 | 12 | 11 | 1 | 1
4 | 16 | 9 | 1 | 1
5 | 3 | 7 | 1 | 2
6 | 6 | 4 | 1 | 2
7 | 8 | 10 | 1 | 2
8 | 15 | 16 | 1 | 2
9 | 17 | 15 | 1 | 2
10 | 1 | 6 | 1 | 3
11 | 5 | 1 | 1 | 3
12 | 9 | 14 | 1 | 3
13 | 10 | 3 | 1 | 3
(13 rows)
Using pgr_withPointsDD¶
Migrating this v3.5 example.
Warning
Breaking change
If using pgr_withPointsDD: function call must be changed after updating
pgRouting.
pgr_withPointsDD's parameter driving_side changed from named optional
to unnamed positional parameter (position 5 in the function call) and its
validity differ for directed and undirected graphs.
SELECT * FROM pgr_withPointsDD(
$$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
$$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
-1, 3.3, 'r', details =>true);
seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
1 | 0 | -1 | -1 | -1 | -1 | 0 | 0
2 | 1 | -1 | -1 | 5 | 1 | 0.4 | 0.4
3 | 2 | -1 | 5 | 6 | 1 | 1 | 1.4
4 | 3 | -1 | 6 | -6 | 4 | 0.7 | 2.1
5 | 4 | -1 | -6 | 7 | 4 | 0.3 | 2.4
(5 rows)
Before updating pgRouting, enumerate
(seq, node, edge, cost, agg_cost)columnsAfter updating pgROuting use an unnamed valid value for driving side after the distance parameter.
SELECT seq, node, edge, cost, agg_cost FROM pgr_withPointsDD(
$$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
$$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
-1, 3.3, 'r', details => true);
seq | node | edge | cost | agg_cost
-----+------+------+------+----------
1 | -1 | -1 | 0 | 0
2 | 5 | 1 | 0.4 | 0.4
3 | 6 | 1 | 1 | 1.4
4 | -6 | 4 | 0.7 | 2.1
5 | 7 | 4 | 0.3 | 2.4
(5 rows)
Examples for multiple vertices¶
Using pgr_kruskalDFS
Migrating this v3.6 example.
SELECT * FROM pgr_kruskalDFS(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
ARRAY[9, 6], max_depth => 3);
seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
1 | 0 | 6 | 6 | 6 | -1 | 0 | 0
2 | 1 | 6 | 6 | 5 | 1 | 1 | 1
3 | 1 | 6 | 6 | 10 | 2 | 1 | 1
4 | 2 | 6 | 10 | 15 | 3 | 1 | 2
5 | 3 | 6 | 15 | 16 | 16 | 1 | 3
6 | 0 | 9 | 9 | 9 | -1 | 0 | 0
7 | 1 | 9 | 9 | 8 | 14 | 1 | 1
8 | 2 | 9 | 8 | 7 | 10 | 1 | 2
9 | 3 | 9 | 7 | 3 | 7 | 1 | 3
10 | 2 | 9 | 8 | 12 | 12 | 1 | 2
11 | 3 | 9 | 12 | 11 | 11 | 1 | 3
12 | 3 | 9 | 12 | 17 | 13 | 1 | 3
(12 rows)
Before updating pgRouting enumerate the columns: (seq, depth, start_vid, node, edge, cost, agg_cost).
SELECT seq, depth, start_vid, node, edge, cost, agg_cost
FROM pgr_kruskalDFS(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
ARRAY[9, 6], max_depth => 3);
seq | depth | start_vid | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+----------
1 | 0 | 6 | 6 | -1 | 0 | 0
2 | 1 | 6 | 5 | 1 | 1 | 1
3 | 1 | 6 | 10 | 2 | 1 | 1
4 | 2 | 6 | 15 | 3 | 1 | 2
5 | 3 | 6 | 16 | 16 | 1 | 3
6 | 0 | 9 | 9 | -1 | 0 | 0
7 | 1 | 9 | 8 | 14 | 1 | 1
8 | 2 | 9 | 7 | 10 | 1 | 2
9 | 3 | 9 | 3 | 7 | 1 | 3
10 | 2 | 9 | 12 | 12 | 1 | 2
11 | 3 | 9 | 11 | 11 | 1 | 3
12 | 3 | 9 | 17 | 13 | 1 | 3
(12 rows)
Using pgr_primDD
Migrating this v3.6 example.
SELECT * FROM pgr_primDD(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
ARRAY[9, 6], 3.5);
seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
1 | 0 | 6 | 6 | 6 | -1 | 0 | 0
2 | 1 | 6 | 6 | 5 | 1 | 1 | 1
3 | 1 | 6 | 6 | 10 | 2 | 1 | 1
4 | 2 | 6 | 10 | 15 | 3 | 1 | 2
5 | 2 | 6 | 10 | 11 | 5 | 1 | 2
6 | 3 | 6 | 11 | 16 | 9 | 1 | 3
7 | 3 | 6 | 11 | 12 | 11 | 1 | 3
8 | 1 | 6 | 6 | 7 | 4 | 1 | 1
9 | 2 | 6 | 7 | 3 | 7 | 1 | 2
10 | 3 | 6 | 3 | 1 | 6 | 1 | 3
11 | 2 | 6 | 7 | 8 | 10 | 1 | 2
12 | 3 | 6 | 8 | 9 | 14 | 1 | 3
13 | 0 | 9 | 9 | 9 | -1 | 0 | 0
14 | 1 | 9 | 9 | 8 | 14 | 1 | 1
15 | 2 | 9 | 8 | 7 | 10 | 1 | 2
16 | 3 | 9 | 7 | 6 | 4 | 1 | 3
17 | 3 | 9 | 7 | 3 | 7 | 1 | 3
(17 rows)
Before updating pgRouting enumerate the columns: (seq, depth, start_vid, node, edge, cost, agg_cost).
SELECT seq, depth, start_vid, node, edge, cost, agg_cost
FROM pgr_primDD(
'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
ARRAY[9, 6], 3.5);
seq | depth | start_vid | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+----------
1 | 0 | 6 | 6 | -1 | 0 | 0
2 | 1 | 6 | 5 | 1 | 1 | 1
3 | 1 | 6 | 10 | 2 | 1 | 1
4 | 2 | 6 | 15 | 3 | 1 | 2
5 | 2 | 6 | 11 | 5 | 1 | 2
6 | 3 | 6 | 16 | 9 | 1 | 3
7 | 3 | 6 | 12 | 11 | 1 | 3
8 | 1 | 6 | 7 | 4 | 1 | 1
9 | 2 | 6 | 3 | 7 | 1 | 2
10 | 3 | 6 | 1 | 6 | 1 | 3
11 | 2 | 6 | 8 | 10 | 1 | 2
12 | 3 | 6 | 9 | 14 | 1 | 3
13 | 0 | 9 | 9 | -1 | 0 | 0
14 | 1 | 9 | 8 | 14 | 1 | 1
15 | 2 | 9 | 7 | 10 | 1 | 2
16 | 3 | 9 | 6 | 4 | 1 | 3
17 | 3 | 9 | 3 | 7 | 1 | 3
(17 rows)
Using pgr_drivingDistance
Migrating this v3.5 example.
SELECT *
FROM pgr_drivingDistance(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
ARRAY[11, 16], 3.0, equicost => true);
seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
1 | 0 | 11 | 11 | 11 | -1 | 0 | 0
2 | 1 | 11 | 11 | 7 | 8 | 1 | 1
3 | 1 | 11 | 11 | 12 | 11 | 1 | 1
4 | 2 | 11 | 7 | 3 | 7 | 1 | 2
5 | 2 | 11 | 7 | 6 | 4 | 1 | 2
6 | 2 | 11 | 7 | 8 | 10 | 1 | 2
7 | 3 | 11 | 3 | 1 | 6 | 1 | 3
8 | 3 | 11 | 6 | 5 | 1 | 1 | 3
9 | 3 | 11 | 8 | 9 | 14 | 1 | 3
10 | 0 | 16 | 16 | 16 | -1 | 0 | 0
11 | 1 | 16 | 16 | 15 | 16 | 1 | 1
12 | 1 | 16 | 16 | 17 | 15 | 1 | 1
13 | 2 | 16 | 15 | 10 | 3 | 1 | 2
(13 rows)
Warning
Breaking change
If using pgr_drivingDistance with multiple vertices: column names must be
changed after updating pgRouting
To get the old version column names (seq, from_v, node, edge, cost, agg_cost): filter out the column
pred and depth and rename start_vid to from_v.
SELECT seq, start_vid AS from_v, node, edge, cost, agg_cost
FROM pgr_drivingDistance(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
ARRAY[11, 16], 3.0, equicost => true);
seq | from_v | node | edge | cost | agg_cost
-----+--------+------+------+------+----------
1 | 11 | 11 | -1 | 0 | 0
2 | 11 | 7 | 8 | 1 | 1
3 | 11 | 12 | 11 | 1 | 1
4 | 11 | 3 | 7 | 1 | 2
5 | 11 | 6 | 4 | 1 | 2
6 | 11 | 8 | 10 | 1 | 2
7 | 11 | 1 | 6 | 1 | 3
8 | 11 | 5 | 1 | 1 | 3
9 | 11 | 9 | 14 | 1 | 3
10 | 16 | 16 | -1 | 0 | 0
11 | 16 | 15 | 16 | 1 | 1
12 | 16 | 17 | 15 | 1 | 1
13 | 16 | 10 | 3 | 1 | 2
(13 rows)
Migration of deleted functions¶
Functions no longer on pgRouting
迁移 pgr_alphaShape¶
** 弃用前：** 原计算方式如下：
已计算 alphaShape
停用后：
PostGIS 提供两种生成 alphaShape 的方法。
如果你有 SFCGAL，可以通过以下方式安装
CREATE EXTENSION postgis_sfcgal
自 PostGIS 3.5 起，请使用 CG_AlphaShape 函数
对于 PostGIS 3.5+ 版本，请继续使用旧函数名
ST_AlphaShape
Other PostGIS options are
Migration of pgr_nodeNetwork¶
Before Deprecation: A table with <edges>_nodded was created. with split edges.
Migration
Use pgr_separateTouching and/or use pgr_separateCrossing
迁移 pgr_createTopology¶
** 弃用前：** 原计算方式如下：
创建了一个包含 <edges>_vertices_pgr 的表。
弃用后： 用户需自行构建完整拓扑。
构建路由拓扑¶
使用大多数 pgRouting 函数的基本信息 id, source, target, cost, [reverse_cost] 在 pgRouting 中被称为路由拓扑。
reverse_cost 是可选的，但强烈建议使用，以便由于几何列的大小而减小数据库的大小。 话虽如此，在本文档中使用了 reverse_cost 。
当数据带有几何图形并且没有路由拓扑时，则需要此步骤。
几何图的所有开始和结束顶点都需要一个标识符，该标识符将存储在数据表的 source 列和 target 列中。 同样， cost 和 reverse_cost 需要具有在两个方向上遍历边的值。
如果这些列不存在，则需要将它们添加到相关表中。 （参见 ALTER TABLE ）
函数 pgr_extractVertices 用于根据边标识符和图边的几何形状创建顶点表。
SELECT * INTO vertices
FROM pgr_extractVertices('SELECT id, geom FROM edges ORDER BY id');
SELECT 18
最后使用存储在顶点表上的数据填充 source 和 target 。
/* -- set the source information */
UPDATE edges AS e
SET source = v.id, x1 = x, y1 = y
FROM vertices AS v
WHERE ST_StartPoint(e.geom) = v.geom;
UPDATE 24
/* -- set the target information */
UPDATE edges AS e
SET target = v.id, x2 = x, y2 = y
FROM vertices AS v
WHERE ST_EndPoint(e.geom) = v.geom;
UPDATE 24
迁移 pgr_createVerticesTable¶
** 弃用前：** 原计算方式如下：
创建了一个包含 <edges>_vertices_pgr 的表。
弃用后： 用户需自行创建顶点表、索引等结构，可调用 pgr_extractVertices 函数实现该功能。
SELECT * INTO vertices
FROM pgr_extractVertices('SELECT id, geom FROM edges ORDER BY id');
SELECT 17
迁移 pgr_analyzeOneWay¶
** 弃用前：** 原计算方式如下：
方向性潜在问题数量分析
WHERE
方向性问题已根据预设规则代码完成计算。
死胡同。
当某个节点仅存在单向通行路径（仅有进入或仅有离开的边线）时，将导致路由问题：
通过保存或直接调用 pgr_extractVertices 获取断头路信息，并判断相邻边线是否为单行道。
在本例中 pgr_extractVertices 已经应用。
WITH
deadends AS (
SELECT (in_edges || out_edges)[1] as id
FROM vertices where array_length(in_edges || out_edges, 1) = 1)
SELECT * FROM edges JOIN deadends USING (id)
WHERE cost < 0 OR reverse_cost < 0;
id | source | target | cost | reverse_cost | capacity | reverse_capacity | x1 | y1 | x2 | y2 | geom
----+--------+--------+------+--------------+----------+------------------+----+----+----+----+------
(0 rows)
桥梁。
另一个路由问题可能出现在以下情况：当无向图中存在这样一条边——删除该边会增加图的连通分量数量（即该边是桥接边），而该桥接边却是单向通行的。
判断桥梁是否为单行道。
SELECT id, cost < 0 OR reverse_cost<0 AS is_OneWway
FROM pgr_bridges('SELECT id, source, target, cost, reverse_cost FROM edges')
JOIN edges ON (edge = id);
id | is_onewway
----+------------
1 | f
6 | f
7 | f
14 | f
17 | f
18 | f
(6 rows)
迁移 pgr_analyzeGraph¶
** 弃用前：** 原计算方式如下：
隔离段的数量。
死胡同数量。
在断头路附近发现的潜在缺口数量。
交叉点数量。(两条边之间）
WHERE
- 图形组件:
不属于任何较大连通子图的连通子图。
- 隔离段:
单一孤立路段组成的网络组件。
- 死端:
仅关联单条边线的顶点。
- 差距:
两个几何对象之间的空间。
- 交叉路口:
是两个几何体之间的拓扑关系。
迁移。
组件。
不仅统计孤立线段，还要识别图中的所有连通子图（组件）。
根据最终应用需求选择：
示例：
SELECT *
FROM pgr_connectedComponents(
'SELECT id, source, target, cost, reverse_cost FROM edges'
);
seq | component | node
-----+-----------+------
1 | 1 | 1
2 | 1 | 3
3 | 1 | 5
4 | 1 | 6
5 | 1 | 7
6 | 1 | 8
7 | 1 | 9
8 | 1 | 10
9 | 1 | 11
10 | 1 | 12
11 | 1 | 15
12 | 1 | 16
13 | 1 | 17
14 | 2 | 2
15 | 2 | 4
16 | 13 | 13
17 | 13 | 14
(17 rows)
死胡同。
使用 pgr_degree 确定图形的所有死角，而不是计算死角。
示例：
SELECT *
FROM pgr_degree($$SELECT id, source, target FROM edges$$)
WHERE degree = 1;
node | degree
------+--------
9 | 1
5 | 1
4 | 1
14 | 1
13 | 1
2 | 1
1 | 1
(7 rows)
断头路附近的潜在缺口。
使用 pgr_findCloseEdges 来确定图形中的几何间隙，而不是计算几何图形之间的潜在间隙。
示例：
WITH
deadends AS (
SELECT id,geom, (in_edges || out_edges)[1] as inhere
FROM vertices where array_length(in_edges || out_edges, 1) = 1),
results AS (
SELECT (pgr_findCloseEdges('SELECT id, geom FROM edges WHERE id != ' || inhere , geom, 0.001)).*
FROM deadends)
SELECT d.id, edge_id, distance, st_AsText(geom) AS point, st_asText(edge) edge
FROM results JOIN deadends d USING (geom);
id | edge_id | distance | point | edge
----+---------+-------------------+---------------------------+--------------------------------------
4 | 14 | 1.00008890058e-12 | POINT(1.999999999999 3.5) | LINESTRING(1.999999999999 3.5,2 3.5)
(1 row)
拓扑关系。
确定几何图形之间的拓扑关系，而不是计算交叉点。
可以使用几个 PostGIS 函数： ST_Intersects, ST_Crosses, ST_Overlaps, 等。
示例：
SELECT e1.id AS id1, e2.id AS id2
FROM edges e1, edges e2 WHERE e1 < e2 AND st_crosses(e1.geom, e2.geom);
id1 | id2
-----+-----
13 | 18
(1 row)
迁移 pgr_trsp (顶点）¶
签名：
pgr_trsp(Edges SQL, source, target, directed boolean, has_rcost boolean
[,restrict_sql text]);
RETURNS SETOF (seq, id1, id2, cost)
另请参阅
当没有限制条件时，使用 pgr_dijkstra 。¶
使用 pgr_dijkstra` 替代。
SELECT * FROM pgr_dijkstra(
$$SELECT id, source, target, cost, reverse_cost
FROM edges WHERE id != 16$$,
15, 16);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 15 | 16 | 15 | 3 | 1 | 0
2 | 2 | 15 | 16 | 10 | 5 | 1 | 1
3 | 3 | 15 | 16 | 11 | 9 | 1 | 2
4 | 4 | 15 | 16 | 16 | -1 | 0 | 3
(4 rows)
获取原始列名：
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)
id1是节点id2是边
当存在限制条件时，使用 pgr_trsp 。¶
使用 pgr_trsp (One to One) 替代。
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)
获取原始列名：
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)
id1是节点id2是边
迁移 pgr_trsp (边)¶
签名：
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)
另请参阅
迁移 pgr_trspViaVertices¶
签名：
pgr_trspViaVertices(sql text, vids integer[],
directed boolean, has_rcost boolean
[, turn_restrict_sql text]);
RETURNS SETOF (seq, id1, id2, id3, cost)
另请参阅
无限制条件时，使用 pgr_dijkstraVia¶
使用 pgr_dijkstraVia - 提议中 替代。
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)
获取原始列名：
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)
id1是路径标识符id2是节点id3是边
当存在限制条件时，使用 pgr_trspVia¶
使用 pgr_trspVia 替代。
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)
获取原始列名：
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)
id1是路径标识符id2是节点id3是边
迁移 pgr_trspViaEdges¶
签名：
pgr_trspViaEdges(sql text, eids integer[], pcts float8[],
directed boolean, has_rcost boolean
[, turn_restrict_sql text]);
RETURNS SETOF (seq, id1, id2, id3, cost)
另请参阅
无限制条件时，使用 pgr_withPointsVia¶
使用 pgr_withPointsVia 替代。
SELECT * FROM pgr_withPointsVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (3, 4, 6)$$,
ARRAY[-4, -3, -6],
driving_side => 'r', details => false);
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost | route_agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------+----------------
1 | 1 | 1 | -4 | -3 | -4 | 6 | 0.7 | 0 | 0
2 | 1 | 2 | -4 | -3 | 3 | 7 | 1 | 0.7 | 0.7
3 | 1 | 3 | -4 | -3 | 7 | 10 | 1 | 1.7 | 1.7
4 | 1 | 4 | -4 | -3 | 8 | 12 | 0.6 | 2.7 | 2.7
5 | 1 | 5 | -4 | -3 | -3 | -1 | 0 | 3.3 | 3.3
6 | 2 | 1 | -3 | -6 | -3 | 12 | 0.4 | 0 | 3.3
7 | 2 | 2 | -3 | -6 | 12 | 13 | 1 | 0.4 | 3.7
8 | 2 | 3 | -3 | -6 | 17 | 15 | 1 | 1.4 | 4.7
9 | 2 | 4 | -3 | -6 | 16 | 9 | 1 | 2.4 | 5.7
10 | 2 | 5 | -3 | -6 | 11 | 8 | 1 | 3.4 | 6.7
11 | 2 | 6 | -3 | -6 | 7 | 4 | 0.3 | 4.4 | 7.7
12 | 2 | 7 | -3 | -6 | -6 | -2 | 0 | 4.7 | 8
(12 rows)
获取原始列名：
SELECT row_number() over(ORDER BY seq) AS seq,
path_id::INTEGER AS id1, node::INTEGER AS id2,
CASE WHEN edge >= 0 THEN edge::INTEGER ELSE -1 END AS id3, cost::FLOAT
FROM pgr_withPointsVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (3, 4, 6)$$,
ARRAY[-4, -3, -6],
driving_side => 'r', details => false);
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 | 1 | -4 | 6 | 0.7
2 | 1 | 3 | 7 | 1
3 | 1 | 7 | 10 | 1
4 | 1 | 8 | 12 | 0.6
5 | 1 | -3 | -1 | 0
6 | 2 | -3 | 12 | 0.4
7 | 2 | 12 | 13 | 1
8 | 2 | 17 | 15 | 1
9 | 2 | 16 | 9 | 1
10 | 2 | 11 | 8 | 1
11 | 2 | 7 | 4 | 0.3
12 | 2 | -6 | -1 | 0
(12 rows)
id1是路径标识符id2是节点id3是边
当存在限制条件时，使用 pgr_trspVia_withPoints¶
使用 pgr_trspVia_withPoints 替代。
SELECT * FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM new_restrictions$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (3, 4, 6)$$,
ARRAY[-4, -3, -6],
driving_side => 'r', details => false);
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost | route_agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------+----------------
1 | 1 | 1 | -4 | -3 | -4 | 6 | 0.7 | 0 | 0
2 | 1 | 2 | -4 | -3 | 3 | 7 | 1 | 0.7 | 0.7
3 | 1 | 3 | -4 | -3 | 7 | 4 | 0.6 | 1.7 | 1.7
4 | 1 | 4 | -4 | -3 | 7 | 10 | 1 | 2.3 | 2.3
5 | 1 | 5 | -4 | -3 | 8 | 12 | 0.6 | 3.3 | 3.3
6 | 1 | 6 | -4 | -3 | -3 | -1 | 0 | 3.9 | 3.9
7 | 2 | 1 | -3 | -6 | -3 | 12 | 0.4 | 0 | 3.9
8 | 2 | 2 | -3 | -6 | 12 | 13 | 1 | 0.4 | 4.3
9 | 2 | 3 | -3 | -6 | 17 | 15 | 1 | 1.4 | 5.3
10 | 2 | 4 | -3 | -6 | 16 | 9 | 1 | 2.4 | 6.3
11 | 2 | 5 | -3 | -6 | 11 | 8 | 1 | 3.4 | 7.3
12 | 2 | 6 | -3 | -6 | 7 | 4 | 0.3 | 4.4 | 8.3
13 | 2 | 7 | -3 | -6 | -6 | -2 | 0 | 4.7 | 8.6
(13 rows)
获取原始列名：
SELECT row_number() over(ORDER BY seq) AS seq,
path_id::INTEGER AS id1, node::INTEGER AS id2,
CASE WHEN edge >= 0 THEN edge::INTEGER ELSE -1 END AS id3, cost::FLOAT
FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM new_restrictions$$,
$$SELECT * FROM (VALUES (1, 6, 0.3),(2, 12, 0.6),(3, 4, 0.7)) AS t(pid, edge_id, fraction)$$,
ARRAY[-1, -2, -3],
driving_side => 'r', details => false);
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 | 1 | -1 | 6 | 0.7
2 | 1 | 3 | 7 | 1
3 | 1 | 7 | 4 | 0.6
4 | 1 | 7 | 10 | 1
5 | 1 | 8 | 12 | 0.6
6 | 1 | -2 | -1 | 0
7 | 2 | -2 | 12 | 0.4
8 | 2 | 12 | 13 | 1
9 | 2 | 17 | 15 | 1
10 | 2 | 16 | 9 | 1
11 | 2 | 11 | 8 | 1
12 | 2 | 7 | 4 | 0.3
13 | 2 | -3 | -1 | 0
(13 rows)
id1是路径标识符id2是节点id3是边
Not yet classified migrations¶
迁移 pgr_KSP¶
- 从:
(seq, path_id, path_seq, node, edge, cost, agg_cost)- 到:
(seq, path_id, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
要迁移的签名：
pgr_KSP(One to One)
- 迁移前:
输出列是
(seq, path_id, path_seq, node, edge, cost, agg_cost)start_vid和end_vid列不存在。pgr_KSP(One to One) 没有start_vid和``end_vid`` 。
- 迁移:
注意附加栏的存在。
If needed filter out the added columns, for example, to return the original columns.
pgr_KSP (One to One)¶
使用 这个 示例。
start_vid包含 起始 vid 参数值。end_vid包含 结束 vid 参数值。
SELECT * FROM pgr_KSP(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
6, 17, 2);
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------
1 | 1 | 1 | 6 | 17 | 6 | 4 | 1 | 0
2 | 1 | 2 | 6 | 17 | 7 | 10 | 1 | 1
3 | 1 | 3 | 6 | 17 | 8 | 12 | 1 | 2
4 | 1 | 4 | 6 | 17 | 12 | 13 | 1 | 3
5 | 1 | 5 | 6 | 17 | 17 | -1 | 0 | 4
6 | 2 | 1 | 6 | 17 | 6 | 4 | 1 | 0
7 | 2 | 2 | 6 | 17 | 7 | 8 | 1 | 1
8 | 2 | 3 | 6 | 17 | 11 | 9 | 1 | 2
9 | 2 | 4 | 6 | 17 | 16 | 15 | 1 | 3
10 | 2 | 5 | 6 | 17 | 17 | -1 | 0 | 4
(10 rows)
如果需要，过滤掉添加的列，例如，返回原始列：
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)
迁移 pgr_withPointsKSP¶
从 v3.6.0 pgr_withPointsKSP 开始对结果列进行标准化。
- 从:
(seq, path_id, path_seq, node, edge, cost, agg_cost)- 到:
(seq, path_id, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
而且 driving side 参数从命名的可选参数变为未命名的必选参数 行驶方向，其有效性对于有向图和无向图有所不同。
要迁移的签名：
pgr_withPointsKSP(One to One)
- 迁移前:
输出列是
(seq, path_seq, [start_pid], [end_pid], node, edge, cost, agg_cost)start_vid和end_vid列不存在。
- 迁移:
注意附加结果列的存在。
New output columns are
(seq, path_id, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)driving side 参数是未命名的强制参数，有向图和无向图的有效值不同。
没有默认值。
在有向图中：有效值为 [
r,R,l,L]在无向图中：有效值为 [
b,B]使用无效值会引发
ERROR。
pgr_withPointsKSP (One to One)¶
使用 这个 示例。
start_vid包含 起始 vid 参数值。end_vid包含 结束 vid 参数值。
SELECT * FROM pgr_withPointsKSP(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
-1, -2, 2, 'l');
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------
1 | 1 | 1 | -1 | -2 | -1 | 1 | 0.6 | 0
2 | 1 | 2 | -1 | -2 | 6 | 4 | 1 | 0.6
3 | 1 | 3 | -1 | -2 | 7 | 8 | 1 | 1.6
4 | 1 | 4 | -1 | -2 | 11 | 11 | 1 | 2.6
5 | 1 | 5 | -1 | -2 | 12 | 13 | 1 | 3.6
6 | 1 | 6 | -1 | -2 | 17 | 15 | 0.6 | 4.6
7 | 1 | 7 | -1 | -2 | -2 | -1 | 0 | 5.2
8 | 2 | 1 | -1 | -2 | -1 | 1 | 0.6 | 0
9 | 2 | 2 | -1 | -2 | 6 | 4 | 1 | 0.6
10 | 2 | 3 | -1 | -2 | 7 | 8 | 1 | 1.6
11 | 2 | 4 | -1 | -2 | 11 | 9 | 1 | 2.6
12 | 2 | 5 | -1 | -2 | 16 | 15 | 1.6 | 3.6
13 | 2 | 6 | -1 | -2 | -2 | -1 | 0 | 5.2
(13 rows)
如果需要，过滤掉附加列，例如，返回原始列：
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)
无限制条件时，使用 pgr_withPoints 。¶
使用 pgr_withPoints (One to One) 替代。
SELECT * FROM pgr_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (4, 3)$$,
-4, -3,
'r', details => false);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | -4 | -3 | -4 | 6 | 0.7 | 0
2 | 2 | -4 | -3 | 3 | 7 | 1 | 0.7
3 | 3 | -4 | -3 | 7 | 10 | 1 | 1.7
4 | 4 | -4 | -3 | 8 | 12 | 0.6 | 2.7
5 | 5 | -4 | -3 | -3 | -1 | 0 | 3.3
(5 rows)
获取原始列名：
SELECT seq, node::INTEGER AS id1, edge::INTEGER AS id2, cost
FROM pgr_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM (VALUES (1, 6, 0.3),(2, 12, 0.6)) AS t(pid, edge_id, fraction)$$,
-1, -2,
'r', details => false);
seq | id1 | id2 | cost
-----+-----+-----+------
1 | -1 | 6 | 0.7
2 | 3 | 7 | 1
3 | 7 | 10 | 1
4 | 8 | 12 | 0.6
5 | -2 | -1 | 0
(5 rows)
id1是节点id2是边
当存在限制条件时，使用 pgr_trsp_withPoints 。¶
使用 pgr_trsp_withPoints 替代。
SELECT * FROM pgr_trsp_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM new_restrictions$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (4, 3)$$,
-4, -3,
driving_side => 'r', details => false);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | -4 | -3 | -4 | 6 | 0.7 | 0
2 | 2 | -4 | -3 | 3 | 7 | 1 | 0.7
3 | 3 | -4 | -3 | 7 | 8 | 1 | 1.7
4 | 4 | -4 | -3 | 11 | 9 | 1 | 2.7
5 | 5 | -4 | -3 | 16 | 16 | 1 | 3.7
6 | 6 | -4 | -3 | 15 | 3 | 1 | 4.7
7 | 7 | -4 | -3 | 10 | 2 | 1 | 5.7
8 | 8 | -4 | -3 | 6 | 4 | 1 | 6.7
9 | 9 | -4 | -3 | 7 | 10 | 1 | 7.7
10 | 10 | -4 | -3 | 8 | 12 | 0.6 | 8.7
11 | 11 | -4 | -3 | -3 | -1 | 0 | 9.3
(11 rows)
获取原始列名：
SELECT seq, node::INTEGER AS id1, edge::INTEGER AS id2, cost
FROM pgr_trsp_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM new_restrictions$$,
$$SELECT * FROM (VALUES (1, 6, 0.3),(2, 12, 0.6)) AS t(pid, edge_id, fraction)$$,
-1, -2,
driving_side => 'r', details => false)
WHERE edge != -1;
seq | id1 | id2 | cost
-----+-----+-----+------
1 | -1 | 6 | 0.7
2 | 3 | 7 | 1
3 | 7 | 8 | 1
4 | 11 | 9 | 1
5 | 16 | 16 | 1
6 | 15 | 3 | 1
7 | 10 | 2 | 1
8 | 6 | 4 | 1
9 | 7 | 10 | 1
10 | 8 | 12 | 0.6
(10 rows)
id1是节点id2是边
迁移 pgr_maxCardinalityMatch¶
pgr_maxCardinalityMatch 仅适用于无向图，因此 有向 标志已被删除。
开始于 v3.4.0
待迁移签名：
pgr_maxCardinalityMatch(Edges SQL, [directed])
RETURNS SETOF (seq, edge, source, target)
需要迁移，因为：
在内部查询上使用
cost和reverse_cost结果已排序
适用于无向图。
新签名
pgr_maxCardinalityMatch(text)仅仅返回边列.可选的
directed标志被删除。
- 迁移前:
所使用的列是
going和coming，用于表示边的存在。directed标志用于指示是 有向 图还是 无向 图。directed标志被忽略。无论它的值如何，它都会给出将图视为 无向 的结果。
- 迁移:
使用
cost和reverse_cost列来表示边的存在。不要使用
directed标志。查询中仅返回
edge列。
SELECT * FROM pgr_maxCardinalityMatch(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$
);
edge
------
1
5
6
13
14
16
17
18
(8 rows)
限制迁移¶
开始于 v3.4.0
限制的结构发生了变化：
旧的限制结构¶
关于已弃用的签名：
列
rid被忽略via_path必须是相反的顺序。
属于
TEXT类型。当多个过孔边必须用
,分隔时。
target_id是禁止路径的最后一条边。
类型为
INTEGER。
to_cost类型为
FLOAT。
创建旧限制表
CREATE TABLE old_restrictions (
rid BIGINT NOT NULL,
to_cost FLOAT,
target_id BIGINT,
via_path TEXT
);
CREATE TABLE
旧限制已满
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
旧限制内容¶
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)
rid = 2 的限制表示的是路径
以相反的顺序位于
via_path列上类型为
TEXT
位于列
target_id类型为
INTEGER
新的限制结构¶
列
id被忽略列
path类型为
ARRAY[ANY-INTEGER]。包含限制所涉及的所有边。
该数组具有限制的有序边。
列
cost类型为
ANY-NUMERICAL
限制表的创建
CREATE TABLE restrictions (
id SERIAL PRIMARY KEY,
path BIGINT[],
cost FLOAT
);
CREATE TABLE
添加限制
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
限制数据
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)
rid = 2 的限制表示的是路径
通过检查，路径是清晰的。
迁移¶
要将旧的限制表转换为新的限制结构，
使用新的限制结构创建一个新表。
在此迁移指南中使用了
new_restrictions。
为了进行这次迁移，pgRouting 提供了一个用于反转数组的辅助函数
_ pgr_array_reverse，这在迁移过程中是必需的。_pgr_array_reverse:是为此迁移临时创建的
没有记录。
将在下一个正式版本 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
迁移后的表内容：
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)
另请参阅¶
索引和表格