pgr_separateCrossing

pgr_separateCrossing - 从交叉几何图形生成不交叉的几何图形。

可用性

Version 3.8.0

• 函数正式发布。

• 提议的函数。

描述

这是一个用于分离交叉边线的辅助函数。

标识

pgr_separateCrossing(Edges SQL, [tolerance, dryrun])

返回 (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	TEXT	Edges SQL 如下所述

可选参数

参数	类型	默认	描述
tolerance	FLOAT	0.01	在 ST_Split 前使用 ST_Snap
dryrun	BOOLEAN	false	当为真时，不要处理查询，而是获取一个通知（NOTICE）来显示查询的结果。

内部查询

Edges SQL

列	类型	描述
id	ANY-INTEGER	（可选）边的标识符。
geom	LINESTRING	边的几何形状。

示例

获取进一步完善的代码。

当由于最终应用或数据质量需要特别处理时，可以通过在 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)

修复交叉路口

在此示例中，原始边线表将用于存储新增的几何图形。

使用示例（无结果输出）

从 \(1\) 到 \(18\) 的路由无解。

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

示例结果

从 \(1\) 到 \(18\) 的路径给出了解决方案。

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 - 函数族 用于路由算法的拓扑概述。

索引和表格

• Index

• Search Page