pgr_separateCrossing
¶
pgr_separateCrossing
- 从交叉几何图形生成不交叉的几何图形。
可用性
Version 3.8.0
函数正式发布。
提议的函数。
描述¶
这是一个用于分离交叉边线的辅助函数。
标识¶
返回
(seq,id,sub_id,geom)
- 示例:
获取交叉点几何形状的线段
SELECT id, sub_id, ST_AsText(geom)
FROM pgr_separateCrossing('SELECT id, geom FROM edges')
ORDER BY id, sub_id;
id | sub_id | st_astext
----+--------+---------------------------
13 | 1 | LINESTRING(3 3,3.5 3)
13 | 2 | LINESTRING(3.5 3,4 3)
18 | 1 | LINESTRING(3.5 2.3,3.5 3)
18 | 2 | LINESTRING(3.5 3,3.5 4)
(4 rows)
参数¶
参数 |
类型 |
描述 |
---|---|---|
|
Edges SQL 如下所述 |
可选参数¶
参数 |
类型 |
默认 |
描述 |
---|---|---|---|
|
|
0.01 |
在 ST_Split 前使用 ST_Snap |
|
|
|
|
内部查询¶
Edges SQL¶
列 |
类型 |
描述 |
---|---|---|
|
ANY-INTEGER |
(可选)边的标识符。 |
|
|
边的几何形状。 |
示例¶
获取进一步完善的代码。¶
当由于最终应用或数据质量需要特别处理时,可以通过在 PostgreSQL 中使用 NOTICE
和 dryrun
标志来获取代码。
SELECT *
FROM pgr_separateCrossing('SELECT id, geom FROM edges', dryrun => true);
NOTICE:
WITH
edges_table AS (
SELECT id, geom FROM edges
),
get_crossings AS (
SELECT e1.id id1, e2.id id2, e1.geom AS g1, e2.geom AS g2, ST_Intersection(e1.geom, e2.geom) AS point
FROM edges_table e1, edges_table e2
WHERE e1.id < e2.id AND ST_Crosses(e1.geom, e2.geom)
),
crossings AS (
SELECT id1, g1, point FROM get_crossings
UNION
SELECT id2, g2, point FROM get_crossings
),
blades AS (
SELECT id1, g1, ST_UnaryUnion(ST_Collect(point)) AS blade
FROM crossings
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, 18);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
(0 rows)
分析路网的相交情况。
SELECT
e1.id id1, e2.id id2,
ST_AsText(ST_Intersection(e1.geom, e2.geom)) AS point
FROM edges e1, edges e2
WHERE e1.id < e2.id AND ST_Crosses(e1.geom, e2.geom);
id1 | id2 | point
-----+-----+--------------
13 | 18 | POINT(3.5 3)
(1 row)
分析结果表明该路网存在交叉点。
准备数据表
用于控制路段来源的附加列。
ALTER TABLE edges ADD old_id BIGINT;
ALTER TABLE
添加新线段。
调用 pgr_separateCrossing 并将新的线段添加到边缘表中。
INSERT INTO edges (old_id, geom)
SELECT id, geom
FROM pgr_separateCrossing('SELECT id, geom FROM edges');
INSERT 0 4
更新其他数值
在此示例中,仅更新了 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 4
更新拓扑结构
如有新顶点,则插入。
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 1
更新边缘表中的源信息和目标信息。
/* -- 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 4
/* -- 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 4
示例结果
从
SELECT *
FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM edges', 1, 18);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 1 | 18 | 1 | 6 | 1 | 0
2 | 2 | 1 | 18 | 3 | 7 | 1 | 1
3 | 3 | 1 | 18 | 7 | 10 | 1 | 2
4 | 4 | 1 | 18 | 8 | 12 | 1 | 3
5 | 5 | 1 | 18 | 12 | 19 | 0.5 | 4
6 | 6 | 1 | 18 | 18 | -1 | 0 | 4.5
(6 rows)
另请参阅¶
Topology - 函数族 用于路由算法的拓扑概述。
索引和表格