1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
|
/*PGR-GNU*****************************************************************
Copyright (c) 2025-2026 pgRouting developers
Mail: project@pgrouting.org
Copyright (c) 2025 Celia Virginia Vergara Castillo
Mail: vicky at erosion.dev
------
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
********************************************************************PGR-GNU*/
--v3.8
CREATE FUNCTION pgr_separateCrossing(
TEXT, -- edges SQL
tolerance FLOAT DEFAULT 0.01, -- tolerance
dryrun BOOLEAN DEFAULT false,
OUT seq INTEGER,
OUT id BIGINT,
OUT sub_id INTEGER,
OUT geom geometry)
RETURNS SETOF RECORD AS
$BODY$
DECLARE
edges_sql TEXT := $1;
the_query TEXT;
sqlhint TEXT;
has_geom BOOLEAN;
has_id BOOLEAN;
BEGIN
IF tolerance <= 0 THEN
RAISE EXCEPTION $$'tolerance' must be a positive number (given %)$$, tolerance
USING ERRCODE = '22023'; -- invalid_parameter_value
END IF;
BEGIN
edges_sql := _pgr_checkQuery($1);
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS sqlhint = PG_EXCEPTION_HINT;
RAISE EXCEPTION '%', SQLERRM USING HINT = sqlhint, ERRCODE = SQLSTATE;
END;
has_id := _pgr_checkColumn(edges_sql, 'id', 'ANY-INTEGER', false, dryrun);
has_geom := _pgr_checkColumn(edges_sql, 'geom', 'geometry', false, dryrun);
IF NOT has_id OR NOT has_geom THEN
RAISE EXCEPTION $$'id' or 'geom' are missing$$ USING HINT = edges_sql;
END IF;
the_query := format(
$$
WITH
edges_table AS (
%s
),
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, %2$s), blade))).*
FROM blades
)
SELECT row_number() over()::INTEGER AS seq, id1::BIGINT, path[1], geom
FROM collection;
$$,
edges_sql, tolerance);
IF dryrun THEN
RAISE NOTICE '%', the_query || ';';
ELSE
RETURN QUERY EXECUTE the_query;
END IF;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE STRICT;
COMMENT ON FUNCTION pgr_separateCrossing(TEXT, FLOAT, BOOLEAN)
IS 'pgr_separateCrossing
- Parameters
- Edges SQL with columns: id, geom
- Optional parameters
- tolerance => 0.01
- dryrun => true
- DOCUMENTATION:
- ${PROJECT_DOC_LINK}/pgr_separateCrossing.html
';
|
