pgr_extractVertices¶
pgr_extractVertices — 提取顶点信息
可用性
Version 3.8.0
Error messages adjustment.
Function promoted to official.
版本 3.3.0
Function promoted to proposed.
版本3.0.0
New experimental function.
描述¶
这是一个用于提取图的边集的顶点信息的辅助函数。
当给出边标识符时，它也会计算出入边
Boost Graph Inside
签名¶
总结
pgr_extractVertices(Edges SQL, [
dryrun])RETURNS SETOF
(id, in_edges, out_edges, x, y, geom)OR EMTPY SET
- 示例:
提取顶点信息
SELECT * FROM pgr_extractVertices(
'SELECT id, geom FROM edges');
id | in_edges | out_edges | x | y | geom
----+----------+-----------+----------------+-----+--------------------------------------------
1 | | {6} | 0 | 2 | 010100000000000000000000000000000000000040
2 | | {17} | 0.5 | 3.5 | 0101000000000000000000E03F0000000000000C40
3 | {6} | {7} | 1 | 2 | 0101000000000000000000F03F0000000000000040
4 | {17} | | 1.999999999999 | 3.5 | 010100000068EEFFFFFFFFFF3F0000000000000C40
5 | | {1} | 2 | 0 | 010100000000000000000000400000000000000000
6 | {1} | {2,4} | 2 | 1 | 01010000000000000000000040000000000000F03F
7 | {4,7} | {8,10} | 2 | 2 | 010100000000000000000000400000000000000040
8 | {10} | {12,14} | 2 | 3 | 010100000000000000000000400000000000000840
9 | {14} | | 2 | 4 | 010100000000000000000000400000000000001040
10 | {2} | {3,5} | 3 | 1 | 01010000000000000000000840000000000000F03F
11 | {5,8} | {9,11} | 3 | 2 | 010100000000000000000008400000000000000040
12 | {11,12} | {13} | 3 | 3 | 010100000000000000000008400000000000000840
13 | | {18} | 3.5 | 2.3 | 01010000000000000000000C406666666666660240
14 | {18} | | 3.5 | 4 | 01010000000000000000000C400000000000001040
15 | {3} | {16} | 4 | 1 | 01010000000000000000001040000000000000F03F
16 | {9,16} | {15} | 4 | 2 | 010100000000000000000010400000000000000040
17 | {13,15} | | 4 | 3 | 010100000000000000000010400000000000000840
(17 rows)
参数¶
参数 |
类型 |
描述 |
|---|---|---|
|
Edges SQL 如下所述 |
可选参数¶
参数 |
类型 |
默认 |
描述 |
|---|---|---|---|
|
|
|
|
内部查询¶
Edges SQL¶
当线的几何形状已知时¶
列 |
类型 |
描述 |
|---|---|---|
|
|
（可选）边的标识符。 |
|
|
边的几何形状。 |
该内部查询优先于接下来的两个内部查询，因此当 geom 列出现时，其他列将被忽略。
忽略的列：
startpointendpointsourcetarget
当顶点几何形状已知时¶
要使用此内部查询，列 geom 不应成为列集的一部分。
列 |
类型 |
描述 |
|---|---|---|
|
|
（可选）边的标识符。 |
|
|
起始顶点的 POINT 几何图形。 |
|
|
结束顶点POINT的几何图形。 |
此内部查询优先于下一个内部查询，因此当出现 startpoint 和 endpoint 列时，其他列将被忽略。
忽略的列：
sourcetarget
当顶点的标识符已知时¶
要使用此内部查询，列 geom、startpoint 和``endpoint`` 不应成为列集的一部分。
列 |
类型 |
描述 |
|---|---|---|
|
|
（可选）边的标识符。 |
|
|
边的第一个端点顶点的标识符。 |
|
|
边的第二个端点顶点的标识符。 |
结果列¶
列 |
类型 |
描述 |
|---|---|---|
|
|
顶点标识符 |
|
|
以顶点
|
|
|
以顶点
|
|
|
点几何的X值
|
|
|
点几何的X值
|
|
|
点几何
|
其他示例¶
模拟执行¶
要获取用于生成顶点信息的查询，请使用 dryrun := true。
结果可作为基础代码，根据后台开发需要进行改进。
SELECT * FROM pgr_extractVertices(
'SELECT id, geom FROM edges',
dryrun => true);
NOTICE:
WITH
main_sql AS (
SELECT id, geom FROM edges
),
the_out AS (
SELECT id::BIGINT AS out_edge, ST_StartPoint(geom) AS geom
FROM main_sql
),
agg_out AS (
SELECT array_agg(out_edge ORDER BY out_edge) AS out_edges, ST_x(geom) AS x, ST_Y(geom) AS y, geom
FROM the_out
GROUP BY geom
),
the_in AS (
SELECT id::BIGINT AS in_edge, ST_EndPoint(geom) AS geom
FROM main_sql
),
agg_in AS (
SELECT array_agg(in_edge ORDER BY in_edge) AS in_edges, ST_x(geom) AS x, ST_Y(geom) AS y, geom
FROM the_in
GROUP BY geom
),
the_points AS (
SELECT in_edges, out_edges, coalesce(agg_out.geom, agg_in.geom) AS geom
FROM agg_out
FULL OUTER JOIN agg_in USING (x, y)
)
SELECT row_number() over(ORDER BY ST_X(geom), ST_Y(geom)) AS id, in_edges, out_edges, ST_X(geom), ST_Y(geom), geom
FROM the_points;
id | in_edges | out_edges | x | y | geom
----+----------+-----------+---+---+------
(0 rows)
创建路由拓扑¶
确保数据库没有 vertices_table¶
DROP TABLE IF EXISTS vertices_table;
NOTICE: table "vertices_table" does not exist, skipping
DROP TABLE
清理要创建的路由拓扑的列¶
UPDATE edges
SET source = NULL, target = NULL,
x1 = NULL, y1 = NULL,
x2 = NULL, y2 = NULL;
UPDATE 18
创建顶点表¶
当
LINESTRING``具有 SRID 时，请使用 ``geom::geometry(POINT, <SRID>)对于已经准备好的大型边表，
将其创建为
UNLOGGED并创建表后，执行
ALTER TABLE .. SET LOGGED操作
SELECT * INTO vertices_table
FROM pgr_extractVertices('SELECT id, geom FROM edges ORDER BY id');
SELECT 17
检查顶点表¶
SELECT *
FROM vertices_table;
id | in_edges | out_edges | x | y | geom
----+----------+-----------+----------------+-----+--------------------------------------------
1 | | {6} | 0 | 2 | 010100000000000000000000000000000000000040
2 | | {17} | 0.5 | 3.5 | 0101000000000000000000E03F0000000000000C40
3 | {6} | {7} | 1 | 2 | 0101000000000000000000F03F0000000000000040
4 | {17} | | 1.999999999999 | 3.5 | 010100000068EEFFFFFFFFFF3F0000000000000C40
5 | | {1} | 2 | 0 | 010100000000000000000000400000000000000000
6 | {1} | {2,4} | 2 | 1 | 01010000000000000000000040000000000000F03F
7 | {4,7} | {8,10} | 2 | 2 | 010100000000000000000000400000000000000040
8 | {10} | {12,14} | 2 | 3 | 010100000000000000000000400000000000000840
9 | {14} | | 2 | 4 | 010100000000000000000000400000000000001040
10 | {2} | {3,5} | 3 | 1 | 01010000000000000000000840000000000000F03F
11 | {5,8} | {9,11} | 3 | 2 | 010100000000000000000008400000000000000040
12 | {11,12} | {13} | 3 | 3 | 010100000000000000000008400000000000000840
13 | | {18} | 3.5 | 2.3 | 01010000000000000000000C406666666666660240
14 | {18} | | 3.5 | 4 | 01010000000000000000000C400000000000001040
15 | {3} | {16} | 4 | 1 | 01010000000000000000001040000000000000F03F
16 | {9,16} | {15} | 4 | 2 | 010100000000000000000010400000000000000040
17 | {13,15} | | 4 | 3 | 010100000000000000000010400000000000000840
(17 rows)
在边表上创建路由拓扑¶
更新 source 信息
WITH
out_going AS (
SELECT id AS vid, unnest(out_edges) AS eid, x, y
FROM vertices_table
)
UPDATE edges
SET source = vid, x1 = x, y1 = y
FROM out_going WHERE id = eid;
UPDATE 18
更新 target 信息
WITH
in_coming AS (
SELECT id AS vid, unnest(in_edges) AS eid, x, y
FROM vertices_table
)
UPDATE edges
SET target = vid, x2 = x, y2 = y
FROM in_coming WHERE id = eid;
UPDATE 18
检查路由拓扑¶
SELECT id, source, target, x1, y1, x2, y2
FROM edges ORDER BY id;
id | source | target | x1 | y1 | x2 | y2
----+--------+--------+-----+-----+----------------+-----
1 | 5 | 6 | 2 | 0 | 2 | 1
2 | 6 | 10 | 2 | 1 | 3 | 1
3 | 10 | 15 | 3 | 1 | 4 | 1
4 | 6 | 7 | 2 | 1 | 2 | 2
5 | 10 | 11 | 3 | 1 | 3 | 2
6 | 1 | 3 | 0 | 2 | 1 | 2
7 | 3 | 7 | 1 | 2 | 2 | 2
8 | 7 | 11 | 2 | 2 | 3 | 2
9 | 11 | 16 | 3 | 2 | 4 | 2
10 | 7 | 8 | 2 | 2 | 2 | 3
11 | 11 | 12 | 3 | 2 | 3 | 3
12 | 8 | 12 | 2 | 3 | 3 | 3
13 | 12 | 17 | 3 | 3 | 4 | 3
14 | 8 | 9 | 2 | 3 | 2 | 4
15 | 16 | 17 | 4 | 2 | 4 | 3
16 | 15 | 16 | 4 | 1 | 4 | 2
17 | 2 | 4 | 0.5 | 3.5 | 1.999999999999 | 3.5
18 | 13 | 14 | 3.5 | 2.3 | 3.5 | 4
(18 rows)
生成拓扑¶
交叉边¶
要获取交叉边：
SELECT a.id, b.id
FROM edges AS a, edges AS b
WHERE a.id < b.id AND st_crosses(a.geom, b.geom);
id | id
----+----
13 | 18
(1 row)
这些信息是正确的，例如，就车辆而言，是隧道还是横跨另一条道路的桥梁。
它可能是不正确的，例如：
当它实际上是道路交叉口时，车辆可以转弯。
在电力线路方面，电力线能够在隧道或桥梁上甚至切换道路。
当不正确时，需要修复：
对于车辆和行人
如果数据来自 OSM 并使用
osm2pgrouting导入到数据库，则需要在 OSM portal 中完成修复并再次导入数据。一般来说，当数据来自为车辆路线准备数据的供应商时，并且出现问题时，需要从供应商处修复数据
对于非常具体的应用
从路线车辆或行人的角度来看，数据是正确的。
数据需要针对特定应用程序进行本地修复。
对交叉点进行一一分析后，对于需要局部修复的交叉点，需要 分割 边。
SELECT ST_AsText((ST_Dump(ST_Split(a.geom, b.geom))).geom)
FROM edges AS a, edges AS b
WHERE a.id = 13 AND b.id = 18
UNION
SELECT ST_AsText((ST_Dump(ST_Split(b.geom, a.geom))).geom)
FROM edges AS a, edges AS b
WHERE a.id = 13 AND b.id = 18;
st_astext
---------------------------
LINESTRING(3.5 2.3,3.5 3)
LINESTRING(3 3,3.5 3)
LINESTRING(3.5 3,4 3)
LINESTRING(3.5 3,3.5 4)
(4 rows)
需要将新边添加到边表中，需要更新新边中的其余属性，需要删除旧边并需要更新路由拓扑。
添加分割边¶
对于每一对交叉边，必须执行与此类似的过程。
插入的列和计算方式取决于应用程序。 例如，如果边具有特征 名称 ，则将复制该列。
用于 pgRouting 计算
基于边相交位置的 因子 可用于调整
cost和reverse_cost列。分割边时，在 Flow - 函数族 函数中使用的容量信息不需要改变。
WITH
first_edge AS (
SELECT (ST_Dump(ST_Split(a.geom, b.geom))).path[1],
(ST_Dump(ST_Split(a.geom, b.geom))).geom,
ST_LineLocatePoint(a.geom,ST_Intersection(a.geom,b.geom)) AS factor
FROM edges AS a, edges AS b
WHERE a.id = 13 AND b.id = 18),
first_segments AS (
SELECT path, first_edge.geom,
capacity, reverse_capacity,
CASE WHEN path=1 THEN factor * cost
ELSE (1 - factor) * cost END AS cost,
CASE WHEN path=1 THEN factor * reverse_cost
ELSE (1 - factor) * reverse_cost END AS reverse_cost
FROM first_edge , edges WHERE id = 13),
second_edge AS (
SELECT (ST_Dump(ST_Split(b.geom, a.geom))).path[1],
(ST_Dump(ST_Split(b.geom, a.geom))).geom,
ST_LineLocatePoint(b.geom,ST_Intersection(a.geom,b.geom)) AS factor
FROM edges AS a, edges AS b
WHERE a.id = 13 AND b.id = 18),
second_segments AS (
SELECT path, second_edge.geom,
capacity, reverse_capacity,
CASE WHEN path=1 THEN factor * cost
ELSE (1 - factor) * cost END AS cost,
CASE WHEN path=1 THEN factor * reverse_cost
ELSE (1 - factor) * reverse_cost END AS reverse_cost
FROM second_edge , edges WHERE id = 18),
all_segments AS (
SELECT * FROM first_segments
UNION
SELECT * FROM second_segments)
INSERT INTO edges
(capacity, reverse_capacity,
cost, reverse_cost,
x1, y1, x2, y2,
geom)
(SELECT capacity, reverse_capacity, cost, reverse_cost,
ST_X(ST_StartPoint(geom)), ST_Y(ST_StartPoint(geom)),
ST_X(ST_EndPoint(geom)), ST_Y(ST_EndPoint(geom)),
geom
FROM all_segments);
INSERT 0 4
添加新的顶点¶
添加应用程序所需的所有分割边后，需要将新创建的顶点添加到顶点表中。
INSERT INTO vertices (in_edges, out_edges, x, y, geom)
(SELECT nv.in_edges, nv.out_edges, nv.x, nv.y, nv.geom
FROM pgr_extractVertices('SELECT id, geom FROM edges') AS nv
LEFT JOIN vertices AS v USING(geom) WHERE v.geom IS NULL);
INSERT 0 1
更新边拓扑¶
/* -- set the source information */
UPDATE edges AS e
SET source = v.id
FROM vertices AS v
WHERE source IS NULL AND ST_StartPoint(e.geom) = v.geom;
UPDATE 4
/* -- set the target information */
UPDATE edges AS e
SET target = v.id
FROM vertices AS v
WHERE target IS NULL AND ST_EndPoint(e.geom) = v.geom;
UPDATE 4
去除多余的边¶
一旦应用程序所需的所有重要信息都已传输到新边，则可以删除交叉边。
DELETE FROM edges WHERE id IN (13, 18);
DELETE 2
还有其他选项可以完成此任务，例如创建视图或物化视图。
更新顶点拓扑¶
为了保持图的一致性，需要更新顶点拓扑
UPDATE vertices AS v SET
in_edges = nv.in_edges, out_edges = nv.out_edges
FROM (SELECT * FROM pgr_extractVertices('SELECT id, geom FROM edges')) AS nv
WHERE v.geom = nv.geom;
UPDATE 18
检查交叉边¶
图上没有交叉边。
SELECT a.id, b.id
FROM edges AS a, edges AS b
WHERE a.id < b.id AND st_crosses(a.geom, b.geom);
id | id
----+----
(0 rows)
没有几何信息的图¶
本示例使用此表设计：
CREATE TABLE wiki (
id SERIAL,
source INTEGER,
target INTEGER,
cost INTEGER);
CREATE TABLE
插入数据¶
INSERT INTO wiki (source, target, cost) VALUES
(1, 2, 7), (1, 3, 9), (1, 6, 14),
(2, 3, 10), (2, 4, 15),
(3, 6, 2), (3, 4, 11),
(4, 5, 6),
(5, 6, 9);
INSERT 0 9
寻找最短路径¶
为了解决这个例子，使用了 pgr_dijkstra`：
SELECT * FROM pgr_dijkstra(
'SELECT id, source, target, cost FROM wiki',
1, 5, false);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 1 | 5 | 1 | 2 | 9 | 0
2 | 2 | 1 | 5 | 3 | 6 | 2 | 9
3 | 3 | 1 | 5 | 6 | 9 | 9 | 11
4 | 4 | 1 | 5 | 5 | -1 | 0 | 20
(4 rows)
从 \(1\) 到 \(5\) 的路径要经过以下顶点： \(1 \rightarrow 3 \rightarrow 6 \rightarrow 5\)
![graph G {
rankdir="LR";
1 [color="red"];
5 [color="green"];
1 -- 2 [label="(7)"];
5 -- 6 [label="(9)", color="blue"];
1 -- 3 [label="(9)", color="blue"];
1 -- 6 [label="(14)"];
2 -- 3 [label="(10)"];
2 -- 4 [label="(13)"];
3 -- 4 [label="(11)"];
3 -- 6 [label="(2)", color="blue"];
4 -- 5 [label="(6)"];
}](_images/graphviz-b8d11e7fcbeda9c107d77aca46bd54e9becbfde3.png)
顶点信息¶
要获取顶点信息，请使用 pgr_extractVertices
SELECT id, in_edges, out_edges
FROM pgr_extractVertices('SELECT id, source, target FROM wiki');
id | in_edges | out_edges
----+----------+-----------
3 | {2,4} | {6,7}
5 | {8} | {9}
4 | {5,7} | {8}
2 | {1} | {4,5}
1 | | {1,2,3}
6 | {3,6,9} |
(6 rows)
另请参阅¶
索引和表格