pgr_separateTouching

pgr_separateTouching - 将相互接触的几何图形转换为端点正确连接的几何图形

可用性

Version 3.8.0

• 函数正式发布。

• 提议的函数。

描述

此辅助函数用于处理几何图形相互接触但未共享端点的情况，通过在相交点处分割图形来增强网络连通性。

标识

pgr_separateTouching(Edges SQL, [tolerance, dryrun])

返回 (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	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_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)

弥补差距

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

使用示例（无结果输出）

从 \(1\) 到 \(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
-----+----------+-----------+---------+------+------+------+----------
(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

示例结果

从 \(1\) 到 \(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 - 函数族 用于路由算法的拓扑概述。

索引和表格

• Index

• Search Page