pgr_separateTouching - pgRouting Manual (3.8)
pgr_separateTouching
pgr_separateTouching
- From touching geometries generates geometries that
are properly connected at endpoints
Availability
Version 3.8.0
-
Function promoted to official.
-
Proposed function.
Description
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.
Signature
- Example :
-
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)
Parameters
|
Parameter |
Type |
Description |
|---|---|---|
|
|
Edges SQL as described below |
Optional parameters
|
Parameter |
Type |
Default |
Description |
|---|---|---|---|
|
|
|
0.01 |
Used in ST_Snap before ST_Split |
|
|
|
|
|
Inner Queries
Edges SQL
|
Column |
Type |
Description |
|---|---|---|
|
|
ANY-INTEGER |
(Optional) identifier of the edge. |
|
|
|
Geometry of the edge. |
Examples
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)
See Also
Topology - Family of Functions for an overview of a topology for routing algorithms.
Indices and tables