迁移指南¶
多个函数的签名发生了变化，和/或已被新函数取代。
结果可能会因变化而不同。
Warning
All deprecated functions will be removed on next major version 4.0.0
Migration of pgr_alphaShape¶
Starting from v3.8.0
Before Deprecation: The following was calculated:
An alphaShape was calculated
After Deprecation:
PostGIS has two ways of generating alphaShape.
If you have SFCGAL, which you can install using
CREATE EXTENSION postgis_sfcgal
Since PostGIS 3.5+ use CG_AlphaShape
For PostGIS 3.5+ use the old name
ST_AlphaShape
Other PostGIS options are * ST_ConvexHull * ST_ConcaveHull
Migration of pgr_createTopology¶
Starting from v3.8.0
Before Deprecation: The following was calculated:
A table with <edges>_vertices_pgr was created.
After Deprecation: The user is responsible to create the complete topology.
构建路由拓扑¶
使用大多数 pgRouting 函数的基本信息``id, source, target, cost, [reverse_cost]`` 在 pgRouting 中被称为路由拓扑。
reverse_cost 是可选的，但强烈建议使用，以便由于几何列的大小而减小数据库的大小。 话虽如此，在本文档中使用了 reverse_cost。
当数据带有几何图形并且没有路由拓扑时，则需要此步骤。
几何图的所有开始和结束顶点都需要一个标识符，该标识符将存储在数据表的``source``列和``target``列中。 同样，cost 和 reverse_cost 需要具有在两个方向上遍历边的值。
如果这些列不存在，则需要将它们添加到相关表中。 （参见`ALTER TABLE <https://www.postgresql.org/docs/current/sql-altertable.html>`__ ）
函数 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
Migration of pgr_createVerticesTable¶
Starting from v3.8.0
Before Deprecation: The following was calculated:
A table with <edges>_vertices_pgr was created.
After Deprecation: The user is responsible to create the vertices table, indexes, etc. They may use pgr_extractVertices for that purpose.
SELECT * INTO vertices
FROM pgr_extractVertices('SELECT id, geom FROM edges ORDER BY id');
SELECT 17
Migration of pgr_analyzeOneWay¶
Starting from v3.8.0
Before Deprecation: The following was calculated:
Number of potential problems in directionality
WHERE
Directionality problems were calculated based on codes.
Dead ends.
A routing problem can arise when from a vertex there is only a way on or a way out but not both:
Either saving or using directly pgr_extractVertices get the dead ends information and determine if the adjacent edge is one way or not.
In this example pgr_extractVertices has already been applied.
WITH
deadends AS (
SELECT (in_edges || out_edges)[1] as id
FROM vertices where array_length(in_edges || out_edges, 1) = 1)
SELECT * FROM edges JOIN deadends USING (id)
WHERE cost < 0 OR reverse_cost < 0;
id | source | target | cost | reverse_cost | capacity | reverse_capacity | x1 | y1 | x2 | y2 | geom
----+--------+--------+------+--------------+----------+------------------+----+----+----+----+------
(0 rows)
Bridges.
Another routing problem can arise when there is an edge of an undirected graph whose deletion increases its number of connected components, and the bridge is only one way.
To determine if the bridges are or not one way.
SELECT id, cost < 0 OR reverse_cost<0 AS is_OneWway
FROM pgr_bridges('SELECT id, source, target, cost, reverse_cost FROM edges')
JOIN edges ON (edge = id);
id | is_onewway
----+------------
1 | f
6 | f
7 | f
14 | f
17 | f
18 | f
(6 rows)
Migration of pgr_analyzeGraph¶
Starting from v3.8.0
Before Deprecation: The following was calculated:
Number of isolated segments.
Number of dead ends.
Number of potential gaps found near dead ends.
Number of intersections. (between 2 edges)
WHERE
- Graph component:
A connected subgraph that is not part of any larger connected subgraph.
- Isolated segment:
A graph component with only one segment.
- 死端:
A vertex that participates in only one edge.
- gaps:
Space between two geometries.
- Intersection:
Is a topological relationship between two geometries.
Migration.
Components.
Instead of counting only isolated segments, determine all the components of the graph.
Depending of the final application requirements use:
For example:
SELECT *
FROM pgr_connectedComponents(
'SELECT id, source, target, cost, reverse_cost FROM edges'
);
seq | component | node
-----+-----------+------
1 | 1 | 1
2 | 1 | 3
3 | 1 | 5
4 | 1 | 6
5 | 1 | 7
6 | 1 | 8
7 | 1 | 9
8 | 1 | 10
9 | 1 | 11
10 | 1 | 12
11 | 1 | 15
12 | 1 | 16
13 | 1 | 17
14 | 2 | 2
15 | 2 | 4
16 | 13 | 13
17 | 13 | 14
(17 rows)
Dead ends.
Instead of counting the dead ends, determine all the dead ends of the graph using pgr_degree.
For example:
SELECT *
FROM pgr_degree($$SELECT id, source, target FROM edges$$)
WHERE degree = 1;
node | degree
------+--------
9 | 1
5 | 1
4 | 1
14 | 1
13 | 1
2 | 1
1 | 1
(7 rows)
Potential gaps near dead ends.
Instead of counting potential gaps between geometries, determine the geometric gaps in the graph using pgr_findCloseEdges.
For example:
WITH
deadends AS (
SELECT id,geom, (in_edges || out_edges)[1] as inhere
FROM vertices where array_length(in_edges || out_edges, 1) = 1),
results AS (
SELECT (pgr_findCloseEdges('SELECT id, geom FROM edges WHERE id != ' || inhere , geom, 0.001)).*
FROM deadends)
SELECT d.id, edge_id, distance, st_AsText(geom) AS point, st_asText(edge) edge
FROM results JOIN deadends d USING (geom);
id | edge_id | distance | point | edge
----+---------+-------------------+---------------------------+--------------------------------------
4 | 14 | 1.00008890058e-12 | POINT(1.999999999999 3.5) | LINESTRING(1.999999999999 3.5,2 3.5)
(1 row)
Topological relationships.
Instead of counting intersections, determine topological relationships between geometries.
Several PostGIS functions can be used: ST_Intersects, ST_Crosses, ST_Overlaps, etc.
For example:
SELECT e1.id AS id1, e2.id AS id2
FROM edges e1, edges e2 WHERE e1 < e2 AND st_crosses(e1.geom, e2.geom);
id1 | id2
-----+-----
13 | 18
(1 row)
pgr_aStar 迁移¶
开始从 v3.6.0
要迁移的签名：
pgr_aStar(一对一)pgr_aStar(一对多)pgr_aStar(多对一)
迁移前
输出列是
(seq, path_seq, [start_vid], [end_vid], node, edge, cost, agg_cost)根据所使用的重载，
start_vid和``end_vid`` 列可能会丢失：pgr_aStar(一对一)没有start_vid和end_vid。pgr_aStar(一对多) 没有start_vid。pgr_aStar(多对一)没有end_vid。
- 迁移:
注意附加栏的存在。
在
pgr_aStar(一对一)中start_vid包含 起始 vid 参数值。end_vid包含 结束 vid 参数值。
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)
在
pgr_aStar(一对多)中start_vid包含 起始 vid 参数值。
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)
在
pgr_aStar(多对一)中end_vid包含 结束 vid 参数值。
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)
如果需要，过滤掉添加的列，例如：
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)
如果需要添加新列，类似于以下使用
pgr_dijkstra的示例，并且必须修改该函数才能返回新列：在`v3.0 <https://docs.pgrouting.org/3.0/en/contraction-family.html#case-1-both-source-and-target-belong-to-the-contracted-graph>`__ 中，函数``my_dijkstra`` 使用
pgr_dijkstra。从 v3.5 开始，函数
my_dijkstra返回pgr_dijkstra的新附加列。
迁移 pgr_bdAstar¶
开始从 v3.6.0
要迁移的签名：
pgr_bdAstar(一对一)pgr_bdAstar(一对多)pgr_bdAstar(多对一)
- 迁移前:
输出列是
(seq, path_seq, [start_vid], [end_vid], node, edge, cost, agg_cost)根据所使用的重载，
start_vid和``end_vid`` 列可能会丢失：pgr_bdAstar(一对一)没有start_vid和end_vid。pgr_bdAstar(一对多) 没有start_vid。pgr_bdAstar(多对一) 没有end_vid。
- 迁移:
注意附加栏的存在。
在
pgr_bdAstar(一对一)中start_vid包含 起始 vid 参数值。end_vid包含 结束 vid 参数值。
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)
在
pgr_bdAstar(一对多)中start_vid包含 起始 vid 参数值。
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)
在
pgr_bdAstar(多对一)中end_vid包含 结束 vid 参数值。
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)
如果需要，过滤掉添加的列，例如：
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)
如果需要添加新列，类似于以下使用
pgr_dijkstra的示例，并且必须修改该函数才能返回新列：在`v3.0 <https://docs.pgrouting.org/3.0/en/contraction-family.html#case-1-both-source-and-target-belong-to-the-contracted-graph>`__ 中，函数``my_dijkstra`` 使用
pgr_dijkstra。从 v3.5 开始，函数
my_dijkstra返回pgr_dijkstra的新附加列。
迁移 pgr_dijkstra¶
从`v3.5.0 <https://docs.pgrouting.org/3.5/en/migration.html>`__ 版本开始
要迁移的签名：
pgr_dijkstra(一对一)pgr_dijkstra(一对多)pgr_dijkstra(多对一)
- 迁移前:
输出列是
(seq, path_seq, [start_vid], [end_vid], node, edge, cost, agg_cost)根据所使用的重载，
start_vid和``end_vid`` 列可能会丢失：pgr_dijkstra(一对一)没有start_vid和end_vid。pgr_dijkstra(一对多) 没有start_vid。pgr_dijkstra(多对一) 没有end_vid。
- 迁移:
注意附加栏的存在。
在
pgr_dijkstra(一对一)中start_vid包含 起始 vid 参数值。end_vid包含 结束 vid 参数值。
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)
在
pgr_dijkstra(一对多)中start_vid包含 起始 vid 参数值。
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)
在
pgr_dijkstra(多对一)中end_vid包含 结束 vid 参数值。
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)
如果需要，过滤掉添加的列，例如：
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)
如果需要，过滤掉添加的列，例如：
在`v3.0 <https://docs.pgrouting.org/3.0/en/contraction-family.html#case-1-both-source-and-target-belong-to-the-contracted-graph>`__ 中，函数``my_dijkstra`` 使用
pgr_dijkstra。从 v3.5 开始，函数
my_dijkstra返回pgr_dijkstra的新附加列。
Migration of pgr_drivingDistance¶
- 从:
(seq, [from_v,] node, edge, cost, agg_cost)- 到:
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)
要迁移的签名：
pgr_drivingDistance(Single vertex)
pgr_drivingDistance(Multiple vertices)
- 迁移前:
输出列是 (seq, [from_v,] node, edge, cost, agg_cost)
pgr_drivingDistance(Single vertex)
没有
start_vid和depth结果列。
pgr_drivingDistance(Multiple vertices)
有
from_v而不是start_vid结果列。没有
depth结果列。
- 迁移:
注意结果列的存在和名称变化。
pgr_drivingDistance(Single vertex)¶
使用`这个 <https://docs.pgrouting.org/3.5/en/pgr_drivingDistance.html#single-vertex>`__ 示例。
start_vid包含 起始 vid 参数值。depth包含node的深度。pred包含node的前驱。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)
如果需要，过滤掉添加的列，例如返回原始列
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)¶
使用 这个 示例。
from_v`结果列名称更改为 ``start_vid。depth包含node的深度。pred包含node的前驱。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)
如有需要，可过滤掉列并重新命名列，例如，返回原始列：
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)
pgr_kruskalDD` / pgr_kruskalBFS` / pgr_kruskalDFS` 的迁移¶
Starting from v3.7.0 pgr_kruskalDD, pgr_kruskalBFS and pgr_kruskalDFS result columns are being standardized.
- 从:
(seq, depth, start_vid, node, edge, cost, agg_cost)- 到:
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)
pgr_kruskalDD单顶点
多个顶点
pgr_kruskalDFS单顶点
多个顶点
pgr_kruskalBFS单顶点
多个顶点
- 迁移前:
输出列为 (seq, depth, start_vid, node, edge, cost, agg_cost)
单顶点和多顶点
没有
pred结果列。
- 迁移:
注意`pred`结果列的存在。
如果需要，可过滤掉添加的列
Kruskal 单顶点¶
以 pgr_KruskalDD 为例。迁移与所有受影响的函数类似。
与 this 示例比较。
现在列 pred 已经存在，并包含了 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)
如果需要，过滤掉添加的列，例如返回原始列
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 多顶点¶
以 pgr_KruskalDD 为例。迁移与所有受影响的函数类似。
与 this 示例比较。
现在列 pred 已经存在，并包含了 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)
如果需要，过滤掉添加的列，例如返回原始列
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)
迁移 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(一对一)
- 迁移前:
输出列是
(seq, path_id, path_seq, node, edge, cost, agg_cost)start_vid和end_vid列不存在。pgr_KSP(一对一) 没有start_vid和``end_vid``。
- 迁移:
注意附加栏的存在。
pgr_KSP (一对一)¶
使用 this 示例。
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_maxCardinalityMatch¶
pgr_maxCardinalityMatch 仅适用于无向图，因此 有向 标志已被删除。
从`v3.4.0 <https://docs.pgrouting.org/3.4/en/migration.html>`__ 开始
待迁移签名：
pgr_maxCardinalityMatch(Edges SQL, [directed])
RETURNS SETOF (seq, edge, source, target)
需要迁移，因为：
在内部查询上使用
cost和reverse_cost结果已排序
适用于无向图。
新签名
pgr_maxCardinalityMatch(text)仅仅返回``边`` 列.可选的
directed标志被删除。
- 迁移前:
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)
所使用的列是
going和coming，用于表示边的存在。directed标志用于指示是 有向 图还是 无向 图。directed标志被忽略。无论它的值如何，它都会给出将图视为 无向 的结果。
- 迁移:
使用
cost和reverse_cost列来表示边的存在。不要使用
directed标志。查询中仅返回
边列。
SELECT * FROM pgr_maxCardinalityMatch(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$
);
edge
------
1
5
6
13
14
16
17
18
(8 rows)
pgr_primDD` / pgr_primBFS` / pgr_primDFS` 的迁移¶
Starting from v3.7.0 pgr_primDD, pgr_primBFS and pgr_primDFS result columns are being standardized.
- 从:
(seq, depth, start_vid, node, edge, cost, agg_cost)- 到:
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)
pgr_primDD单顶点
多个顶点
pgr_primDFS单顶点
多个顶点
pgr_primBFS单顶点
多个顶点
- 迁移前:
输出列为 (seq, depth, start_vid, node, edge, cost, agg_cost)
单顶点和多顶点
没有
pred结果列。
- 迁移:
注意`pred`结果列的存在。
如果需要，可过滤掉添加的列
Prim 单一节点¶
以 pgr_primDD 为例。迁移与所有受影响的函数类似。
与 this 例子比较。
现在列 pred 已经存在，并包含了 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)
如果需要，过滤掉添加的列，例如返回原始列
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 多个顶点¶
以 pgr_primDD 为例。迁移与所有受影响的函数类似。
与 this 例子比较。
现在列 pred 已经存在，并包含了 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)
如果需要，过滤掉添加的列，例如返回原始列
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)
迁移 pgr_withPointsDD¶
从 v3.6.0 pgr_withPointsDD - Proposed 结果列开始标准化。
- 从:
(seq, [start_vid], node, edge, cost, agg_cost)- 到:
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)
并且 driving_side 参数从命名可选变为未命名强制 driving side ，其有效性对于有向图和无向图是不同的。
要迁移的签名：
pgr_withPointsDD(单个顶点)pgr_withPointsDD(多个顶点)
- 迁移前:
pgr_withPointsDD(单个顶点)输出列为
(seq, node, edge, cost, agg_cost)没有
start_vid，pred和``depth`` 结果列。driving_side参数以前被命名为可选，现在是强制性的未命名参数。
pgr_withPointsDD(多个顶点)输出列为
(seq, start_vid, node, edge, cost, agg_cost)没有
depth和``pred`` 结果列。driving_side参数以前被命名为可选，现在是强制性的未命名参数。
驾驶侧是可选的
此查询的默认值为：
- 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)
驾驶侧被命名为可选
此查询的默认值为：
- 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)
在有向图上 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, 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)
在无向图上 r 可以用作 行驶方向
此外， l 也可用作 驾驶侧
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)
- 迁移后:
注意附加结果列的存在。
新的输出列是
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)driving side 参数是未命名的强制参数，有向图和无向图的有效值不同。
没有默认值。
在有向图中：有效值为 [
r,R,l,L]在无向图中：有效值为 [
b,B]使用无效值会引发
ERROR。
pgr_withPointsDD (单个顶点)¶
使用`这个 <https://docs.pgrouting.org/3.5/en/pgr_withPointsDD.html#single-vertex>`__ 示例。
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)start_vid包含 起始 vid 参数值。depth包含从start_vid顶点到node的 深度。pred包含node的前驱。
要进行迁移，请在 distance 参数之后使用一个未命名的有效值作为 行驶方向：
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)
要获取以前版本的结果：
过滤掉额外的列，例如；
当设置
details => false以移除点时，请使用WHERE 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 (多个顶点)¶
使用`这个 <https://docs.pgrouting.org/3.5/en/pgr_withPointsDD.html#multiple-vertices>`__ 示例。
(seq, depth, start_vid, pred, node, edge, cost, agg_cost)depth包含从start_vid顶点到node的 深度。pred包含node的前驱。
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)
要获取以前版本的结果：
过滤掉额外的列
当设置
details => false以移除点时，请使用WHERE 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)
迁移 pgr_withPointsKSP¶
从 v3.6.0 pgr_withPointsKSP - Proposed 开始对结果列进行标准化。
- 从:
(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(一对一)
- 迁移前:
输出列是
(seq, path_seq, [start_pid], [end_pid], node, edge, cost, agg_cost)start_vid和end_vid列不存在。
- 迁移:
注意附加结果列的存在。
新的输出列是
(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 (一对一)¶
使用 这个 示例。
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_trsp （顶点）¶
标识：
pgr_trsp(Edges SQL, source, target, directed boolean, has_rcost boolean
[,restrict_sql text]);
RETURNS SETOF (seq, id1, id2, cost)
- Deprecated:
另请参阅
Use pgr_dijkstra when there are no restrictions.¶
请改用 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)
To get the original column names:
SELECT seq, node::INTEGER AS id1, edge::INTEGER AS id2, cost
FROM pgr_dijkstra(
$$SELECT id, source, target, cost, reverse_cost
FROM edges WHERE id != 16$$,
15, 16);
seq | id1 | id2 | cost
-----+-----+-----+------
1 | 15 | 3 | 1
2 | 10 | 5 | 1
3 | 11 | 9 | 1
4 | 16 | -1 | 0
(4 rows)
id1是节点id2是边
Use pgr_trsp when there are restrictions.¶
请改用 pgr_trsp - Proposed (一对一）。
SELECT * FROM pgr_trsp(
$$SELECT id, source, target, cost, reverse_cost
FROM edges WHERE id != 16$$,
$$SELECT * FROM new_restrictions$$,
15, 16);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 15 | 16 | 15 | 3 | 1 | 0
2 | 2 | 15 | 16 | 10 | 5 | 1 | 1
3 | 3 | 15 | 16 | 11 | 11 | 1 | 2
4 | 4 | 15 | 16 | 12 | 13 | 1 | 3
5 | 5 | 15 | 16 | 17 | 15 | 1 | 4
6 | 6 | 15 | 16 | 16 | -1 | 0 | 5
(6 rows)
To get the original column names:
SELECT seq, node::INTEGER AS id1, edge::INTEGER AS id2, cost
FROM pgr_trsp(
$$SELECT id, source, target, cost, reverse_cost
FROM edges WHERE id != 16$$,
$$SELECT * FROM new_restrictions$$,
15, 16);
seq | id1 | id2 | cost
-----+-----+-----+------
1 | 15 | 3 | 1
2 | 10 | 5 | 1
3 | 11 | 11 | 1
4 | 12 | 13 | 1
5 | 17 | 15 | 1
6 | 16 | -1 | 0
(6 rows)
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)
- Deprecated:
另请参阅
Use pgr_withPoints when there are no restrictions.¶
Use pgr_withPoints - Proposed (One to One) instead.
SELECT * FROM pgr_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (4, 3)$$,
-4, -3,
details => false);
seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
1 | 1 | -4 | 6 | 0.7 | 0
2 | 2 | 3 | 7 | 1 | 0.7
3 | 3 | 7 | 10 | 1 | 1.7
4 | 4 | 8 | 12 | 0.6 | 2.7
5 | 5 | -3 | -1 | 0 | 3.3
(5 rows)
To get the original column names:
SELECT seq, node::INTEGER AS id1, edge::INTEGER AS id2, cost
FROM pgr_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM (VALUES (1, 6, 0.3),(2, 12, 0.6)) AS t(pid, edge_id, fraction)$$,
-1, -2,
details => false);
seq | id1 | id2 | cost
-----+-----+-----+------
1 | -1 | 6 | 0.7
2 | 3 | 7 | 1
3 | 7 | 10 | 1
4 | 8 | 12 | 0.6
5 | -2 | -1 | 0
(5 rows)
id1是节点id2是边
Use pgr_trsp_withPoints when there are restrictions.¶
请使用 pgr_trsp_withPoints - Proposed。
SELECT * FROM pgr_trsp_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM new_restrictions$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (4, 3)$$,
-4, -3,
details => false);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | -4 | -3 | -4 | 6 | 0.7 | 0
2 | 2 | -4 | -3 | 3 | 7 | 1 | 0.7
3 | 3 | -4 | -3 | 7 | 8 | 1 | 1.7
4 | 4 | -4 | -3 | 11 | 9 | 1 | 2.7
5 | 5 | -4 | -3 | 16 | 16 | 1 | 3.7
6 | 6 | -4 | -3 | 15 | 3 | 1 | 4.7
7 | 7 | -4 | -3 | 10 | 2 | 1 | 5.7
8 | 8 | -4 | -3 | 6 | 4 | 1 | 6.7
9 | 9 | -4 | -3 | 7 | 10 | 1 | 7.7
10 | 10 | -4 | -3 | 8 | 12 | 0.6 | 8.7
11 | 11 | -4 | -3 | -3 | -1 | 0 | 9.3
(11 rows)
To get the original column names:
SELECT seq, node::INTEGER AS id1, edge::INTEGER AS id2, cost
FROM pgr_trsp_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM new_restrictions$$,
$$SELECT * FROM (VALUES (1, 6, 0.3),(2, 12, 0.6)) AS t(pid, edge_id, fraction)$$,
-1, -2,
details => false)
WHERE edge != -1;
seq | id1 | id2 | cost
-----+-----+-----+------
1 | -1 | 6 | 0.7
2 | 3 | 7 | 1
3 | 7 | 8 | 1
4 | 11 | 9 | 1
5 | 16 | 16 | 1
6 | 15 | 3 | 1
7 | 10 | 2 | 1
8 | 6 | 4 | 1
9 | 7 | 10 | 1
10 | 8 | 12 | 0.6
(10 rows)
id1是节点id2是边
迁移 pgr_trspViaVertices¶
标识：
pgr_trspViaVertices(sql text, vids integer[],
directed boolean, has_rcost boolean
[, turn_restrict_sql text]);
RETURNS SETOF (seq, id1, id2, id3, cost)
- Deprecated:
另请参阅
Use pgr_dijkstraVia when there are no restrictions¶
请使用 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)
To get the original column names:
SELECT row_number() over(ORDER BY seq) AS seq,
path_id::INTEGER AS id1, node::INTEGER AS id2,
CASE WHEN edge >= 0 THEN edge::INTEGER ELSE -1 END AS id3, cost::FLOAT
FROM pgr_dijkstraVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
ARRAY[6, 3, 6])
WHERE edge != -1;
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 | 1 | 6 | 4 | 1
2 | 1 | 7 | 7 | 1
3 | 2 | 3 | 7 | 1
4 | 2 | 7 | 4 | 1
5 | 2 | 6 | -1 | 0
(5 rows)
id1是路径标识符id2是节点id3是边
Use pgr_trspVia when there are restrictions¶
SELECT * FROM pgr_trspVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM new_restrictions$$,
ARRAY[6, 3, 6]);
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost | route_agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------+----------------
1 | 1 | 1 | 6 | 3 | 6 | 4 | 1 | 0 | 0
2 | 1 | 2 | 6 | 3 | 7 | 8 | 1 | 1 | 1
3 | 1 | 3 | 6 | 3 | 11 | 9 | 1 | 2 | 2
4 | 1 | 4 | 6 | 3 | 16 | 16 | 1 | 3 | 3
5 | 1 | 5 | 6 | 3 | 15 | 3 | 1 | 4 | 4
6 | 1 | 6 | 6 | 3 | 10 | 5 | 1 | 5 | 5
7 | 1 | 7 | 6 | 3 | 11 | 8 | 1 | 6 | 6
8 | 1 | 8 | 6 | 3 | 7 | 7 | 1 | 7 | 7
9 | 1 | 9 | 6 | 3 | 3 | -1 | 0 | 8 | 8
10 | 2 | 1 | 3 | 6 | 3 | 7 | 1 | 0 | 8
11 | 2 | 2 | 3 | 6 | 7 | 4 | 1 | 1 | 9
12 | 2 | 3 | 3 | 6 | 6 | -2 | 0 | 2 | 10
(12 rows)
To get the original column names:
SELECT row_number() over(ORDER BY seq) AS seq,
path_id::INTEGER AS id1, node::INTEGER AS id2,
CASE WHEN edge >= 0 THEN edge::INTEGER ELSE -1 END AS id3, cost::FLOAT
FROM pgr_trspVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM new_restrictions$$,
ARRAY[6, 3, 6])
WHERE edge != -1;
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 | 1 | 6 | 4 | 1
2 | 1 | 7 | 8 | 1
3 | 1 | 11 | 9 | 1
4 | 1 | 16 | 16 | 1
5 | 1 | 15 | 3 | 1
6 | 1 | 10 | 5 | 1
7 | 1 | 11 | 8 | 1
8 | 1 | 7 | 7 | 1
9 | 2 | 3 | 7 | 1
10 | 2 | 7 | 4 | 1
11 | 2 | 6 | -1 | 0
(11 rows)
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)
- Deprecated:
另请参阅
Use pgr_withPointsVia when there are no restrictions¶
请使用 pgr_withPointsVia - Proposed。
SELECT * FROM pgr_withPointsVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (3, 4, 6)$$,
ARRAY[-4, -3, -6],
details => false);
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost | route_agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------+----------------
1 | 1 | 1 | -4 | -3 | -4 | 6 | 0.7 | 0 | 0
2 | 1 | 2 | -4 | -3 | 3 | 7 | 1 | 0.7 | 0.7
3 | 1 | 3 | -4 | -3 | 7 | 10 | 1 | 1.7 | 1.7
4 | 1 | 4 | -4 | -3 | 8 | 12 | 0.6 | 2.7 | 2.7
5 | 1 | 5 | -4 | -3 | -3 | -1 | 0 | 3.3 | 3.3
6 | 2 | 1 | -3 | -6 | -3 | 12 | 0.4 | 0 | 3.3
7 | 2 | 2 | -3 | -6 | 12 | 13 | 1 | 0.4 | 3.7
8 | 2 | 3 | -3 | -6 | 17 | 15 | 1 | 1.4 | 4.7
9 | 2 | 4 | -3 | -6 | 16 | 9 | 1 | 2.4 | 5.7
10 | 2 | 5 | -3 | -6 | 11 | 8 | 1 | 3.4 | 6.7
11 | 2 | 6 | -3 | -6 | 7 | 4 | 0.3 | 4.4 | 7.7
12 | 2 | 7 | -3 | -6 | -6 | -2 | 0 | 4.7 | 8
(12 rows)
To get the original column names:
SELECT row_number() over(ORDER BY seq) AS seq,
path_id::INTEGER AS id1, node::INTEGER AS id2,
CASE WHEN edge >= 0 THEN edge::INTEGER ELSE -1 END AS id3, cost::FLOAT
FROM pgr_withPointsVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * 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)
id1是路径标识符id2是节点id3是边
Use pgr_trspVia_withPoints when there are restrictions¶
使用 pgr_trspVia_withPoints - Proposed 代替。
SELECT * FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM new_restrictions$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (3, 4, 6)$$,
ARRAY[-4, -3, -6],
details => false);
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost | route_agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------+----------------
1 | 1 | 1 | -4 | -3 | -4 | 6 | 0.7 | 0 | 0
2 | 1 | 2 | -4 | -3 | 3 | 7 | 1 | 0.7 | 0.7
3 | 1 | 3 | -4 | -3 | 7 | 4 | 0.6 | 1.7 | 1.7
4 | 1 | 4 | -4 | -3 | 7 | 10 | 1 | 2.3 | 2.3
5 | 1 | 5 | -4 | -3 | 8 | 12 | 0.6 | 3.3 | 3.3
6 | 1 | 6 | -4 | -3 | -3 | -1 | 0 | 3.9 | 3.9
7 | 2 | 1 | -3 | -6 | -3 | 12 | 0.4 | 0 | 3.9
8 | 2 | 2 | -3 | -6 | 12 | 13 | 1 | 0.4 | 4.3
9 | 2 | 3 | -3 | -6 | 17 | 15 | 1 | 1.4 | 5.3
10 | 2 | 4 | -3 | -6 | 16 | 9 | 1 | 2.4 | 6.3
11 | 2 | 5 | -3 | -6 | 11 | 8 | 1 | 3.4 | 7.3
12 | 2 | 6 | -3 | -6 | 7 | 4 | 0.3 | 4.4 | 8.3
13 | 2 | 7 | -3 | -6 | -6 | -2 | 0 | 4.7 | 8.6
(13 rows)
To get the original column names:
SELECT row_number() over(ORDER BY seq) AS seq,
path_id::INTEGER AS id1, node::INTEGER AS id2,
CASE WHEN edge >= 0 THEN edge::INTEGER ELSE -1 END AS id3, cost::FLOAT
FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM new_restrictions$$,
$$SELECT * FROM (VALUES (1, 6, 0.3),(2, 12, 0.6),(3, 4, 0.7)) AS t(pid, edge_id, fraction)$$,
ARRAY[-1, -2, -3],
details => false);
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 | 1 | -1 | 6 | 0.7
2 | 1 | 3 | 7 | 1
3 | 1 | 7 | 4 | 0.6
4 | 1 | 7 | 10 | 1
5 | 1 | 8 | 12 | 0.6
6 | 1 | -2 | -1 | 0
7 | 2 | -2 | 12 | 0.4
8 | 2 | 12 | 13 | 1
9 | 2 | 17 | 15 | 1
10 | 2 | 16 | 9 | 1
11 | 2 | 11 | 8 | 1
12 | 2 | 7 | 4 | 0.3
13 | 2 | -3 | -1 | 0
(13 rows)
id1是路径标识符id2是节点id3是边
限制迁移¶
从`v3.4.0 <https://docs.pgrouting.org/3.4/en/migration.html>`__ 开始
限制的结构发生了变化：
旧的限制结构¶
关于已弃用的签名：
列
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)
另请参阅¶
索引和表格