pgr_separateTouching
¶
pgr_separateTouching
- 将相互接触的几何图形转换为端点正确连接的几何图形
可用性
Version 3.8.0
函数正式发布。
提议的函数。
描述¶
此辅助函数用于处理几何图形相互接触但未共享端点的情况,通过在相交点处分割图形来增强网络连通性。
标识¶
返回
(seq,id,sub_id,geom)
- 示例:
获取交叉点几何形状的线段
SELECT *
FROM pgr_separateTouching('SELECT id, geom FROM edges');
seq | id | sub_id | geom
-----+----+--------+------------------------------------------------------------------------------------
1 | 14 | 1 | 0102000000020000000000000000000040000000000000084068EEFFFFFFFFFF3F0000000000000C40
2 | 14 | 2 | 01020000000200000068EEFFFFFFFFFF3F0000000000000C4000000000000000400000000000001040
(2 rows)
参数¶
参数 |
类型 |
描述 |
---|---|---|
|
Edges SQL 如下所述 |
可选参数¶
参数 |
类型 |
默认 |
描述 |
---|---|---|---|
|
|
0.01 |
在 ST_Split 前使用 ST_Snap |
|
|
|
|
内部查询¶
Edges SQL¶
列 |
类型 |
描述 |
---|---|---|
|
ANY-INTEGER |
(可选)边的标识符。 |
|
|
边的几何形状。 |
示例¶
获取进一步完善的代码。¶
当由于最终应用或数据质量需要特别处理时,可以通过在 PostgreSQL 中使用 NOTICE
和 dryrun
标志来获取代码。
SELECT *
FROM pgr_separateTouching('SELECT id, geom FROM edges', dryrun => true);
NOTICE:
WITH
edges_table AS (
SELECT id, geom FROM edges
),
get_touching AS (
SELECT e1.id id1, e2.id id2, ST_Snap(e1.geom, e2.geom, 0.01) AS geom, e1.geom AS g1, e2.geom AS g2
FROM edges_table e1, edges_table e2
WHERE e1.id != e2.id AND ST_DWithin(e1.geom, e2.geom, 0.01) AND NOT(
ST_StartPoint(e1.geom) = ST_StartPoint(e2.geom) OR ST_StartPoint(e1.geom) = ST_EndPoint(e2.geom)
OR ST_EndPoint(e1.geom) = ST_StartPoint(e2.geom) OR ST_EndPoint(e1.geom) = ST_EndPoint(e2.geom))
),
touchings AS (
SELECT id1, g1, g2, st_intersection(geom, g2) AS point
FROM get_touching
WHERE NOT (geom = g1) OR
(ST_touches(g1, g2) AND NOT
(ST_Intersection(geom, g2) = ST_StartPoint(g1)
OR ST_Intersection(geom, g2) = ST_EndPoint(g1)))
),
blades AS (
SELECT id1, g1, ST_UnaryUnion(ST_Collect(point)) AS blade
FROM touchings
GROUP BY id1, g1
),
collection AS (
SELECT id1, (st_dump(st_split(st_snap(g1, blade, 0.01), blade))).*
FROM blades
)
SELECT row_number() over()::INTEGER AS seq, id1::BIGINT, path[1], geom
FROM collection;
;
seq | id | sub_id | geom
-----+----+--------+------
(0 rows)
弥补差距¶
在此示例中,原始边线表将用于存储新增的几何图形。
使用示例(无结果输出)
从
SELECT *
FROM pgr_dijkstra( 'SELECT id, source, target, cost, reverse_cost FROM edges', 1, 2);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
(0 rows)
分析路网的断点情况。
WITH
deadends AS (
SELECT id AS vid, (in_edges || out_edges)[1] AS edge, geom AS vgeom
FROM vertices
WHERE array_length(in_edges || out_edges, 1) = 1
)
SELECT id, ST_AsText(geom), vid, ST_AsText(vgeom), ST_Distance(geom, vgeom)
FROM edges, deadends
WHERE id != edge AND ST_Distance(geom, vgeom) < 0.1;
id | st_astext | vid | st_astext | st_distance
----+---------------------+-----+---------------------------+-----------------------
14 | LINESTRING(2 3,2 4) | 4 | POINT(1.999999999999 3.5) | 1.000088900582341e-12
(1 row)
分析表明该路网存在断点。
准备数据表
用于控制路段来源的附加列。
ALTER TABLE edges ADD old_id BIGINT;
ALTER TABLE
添加新线段。
调用 pgr_separateTouching 并将新的线段添加到边缘表中。
INSERT INTO edges (old_id, geom)
SELECT id, geom
FROM pgr_separateTouching('SELECT id, geom FROM edges');
INSERT 0 2
更新其他数值
在此示例中,仅更新了 cost
和 reverse_cost
列,其值基于几何长度并通过 sign
函数保持方向性。
WITH
costs AS (
SELECT e2.id,
sign(e1.cost) * ST_Length(e2.geom) AS cost,
sign(e1.reverse_cost) * ST_Length(e2.geom) AS reverse_cost
FROM edges e1
JOIN edges e2 ON (e1.id = e2.old_id)
)
UPDATE edges e SET (cost, reverse_cost) = (c.cost, c.reverse_cost)
FROM costs AS c
WHERE e.id = c.id;
UPDATE 2
更新拓扑结构
如有新顶点,则插入。
WITH new_vertex AS (
SELECT ev.*
FROM pgr_extractVertices('SELECT id, geom FROM edges WHERE old_id IS NOT NULL') ev
LEFT JOIN vertices v using(geom)
WHERE v IS NULL
)
INSERT INTO vertices (in_edges, out_edges,x,y,geom)
SELECT in_edges, out_edges,x,y,geom
FROM new_vertex;
INSERT 0 0
更新边缘表中的源信息和目标信息。
/* -- set the source information */
UPDATE edges AS e
SET source = v.id, x1 = x, y1 = y
FROM vertices AS v
WHERE source IS NULL AND ST_StartPoint(e.geom) = v.geom;
UPDATE 2
/* -- set the target information */
UPDATE edges AS e
SET target = v.id, x2 = x, y2 = y
FROM vertices AS v
WHERE target IS NULL AND ST_EndPoint(e.geom) = v.geom;
UPDATE 2
示例结果
从
SELECT *
FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM edges', 1, 2);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 1 | 2 | 1 | 6 | 1 | 0
2 | 2 | 1 | 2 | 3 | 7 | 1 | 1
3 | 3 | 1 | 2 | 7 | 10 | 1 | 2
4 | 4 | 1 | 2 | 8 | 19 | 0.5 | 3
5 | 5 | 1 | 2 | 4 | 17 | 1 | 3.5
6 | 6 | 1 | 2 | 2 | -1 | 0 | 4.5
(6 rows)
另请参阅¶
Topology - 函数族 用于路由算法的拓扑概述。
索引和表格