pgr_separateTouching

pgr_separateTouching - From touching geometries generates geometries that are properly connected at endpoints

可用性

Version 3.8.0

• 函数正式发布。

• Proposed function.

描述

This is an auxiliary function for processing geometries that touch but don't share exact endpoints, splitting them at their intersection points to improve network connectivity.

标识

pgr_separateTouching(Edges SQL, [tolerance, dryrun])

RETURNS (seq,id,sub_id,geom)

示例:

Get the segments of the crossing geometries

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	Used in ST_Snap before ST_Split
dryrun	BOOLEAN	false	当为真时，不要处理查询，而是获取一个通知（NOTICE）来显示查询的结果。

内部查询

Edges SQL

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

示例

Get the code for further refinement.

When there are special details that need to be taken care of because of the final application or the quality of the data, the code can be obtained On a PostgreSQL NOTICE using the dryrun flag.

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)

Fixing a gap

In this example the original edge table will be used to store the additional geometries.

An example use without results

Routing from \(1\) to \(2\) gives no solution.

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)

Analyze the network for gaps.

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)

The analysis tell us that the network has a gap.

Prepare tables

Additional columns to control the origin of the segments.

ALTER TABLE edges ADD old_id BIGINT;
ALTER TABLE

Adding new segments.

Calling pgr_separateTouching and adding the new segments to the edges table.

INSERT INTO edges (old_id, geom)
SELECT id, geom
FROM pgr_separateTouching('SELECT id, geom FROM edges');
INSERT 0 2

Update other values

In this example only cost and reverse_cost are updated, where they are based on the length of the geometry and the directionality is kept using the sign function.

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

Update the topology

Insert the new vertices if any.

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

Update source and target information on the edges table.

/* -- 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

The example has results

Routing from \(1\) to \(2\) gives a solution.

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