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
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
|
/*PGR-GNU*****************************************************************
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_findCloseEdges(
TEXT,
geometry[],
FLOAT,
cap INTEGER DEFAULT 1,
dryrun BOOLEAN default false,
OUT edge_id BIGINT,
OUT fraction FLOAT,
OUT side CHAR,
OUT distance FLOAT,
OUT geom geometry,
OUT edge geometry)
returns SETOF RECORD AS
$BODY$
DECLARE
geom_arr geometry[] := $2;
tolerance FLOAT := $3;
cap INTEGER := $4;
dryrun BOOLEAN := $5;
edges_SQL TEXT;
has_id BOOLEAN;
has_geom BOOLEAN;
ret_query TEXT;
ret_query_end TEXT;
sqlhint TEXT;
BEGIN
IF (tolerance < 0) THEN
RAISE EXCEPTION 'Invalid value for "tolerance"';
END IF;
IF (cap < 0) THEN
RAISE EXCEPTION 'Invalid value for "cap"';
END IF;
BEGIN
edges_sql := _pgr_checkQuery($1);
has_id := _pgr_checkColumn(edges_sql, 'id', 'ANY-INTEGER', false, dryrun => dryrun);
has_geom := _pgr_checkColumn(edges_sql, 'geom', 'geometry', false, dryrun => dryrun);
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS sqlhint = PG_EXCEPTION_HINT;
RAISE EXCEPTION '%', SQLERRM USING HINT = sqlhint, ERRCODE = SQLSTATE;
END;
ret_query = format(
$q$
WITH
edges_sql AS (%1$s),
point_sql AS (SELECT unnest(%2$L::geometry[]) AS point),
results AS (
SELECT
id::BIGINT AS edge_id,
ST_LineLocatePoint(geom, point) AS fraction,
CASE WHEN ST_Intersects(ST_Buffer(geom, %3$s, 'side=right endcap=flat'), point)
THEN 'r'
ELSE 'l' END::CHAR AS side,
geom <-> point AS distance,
point,
$q$, edges_sql, geom_arr, tolerance);
ret_query_end = format(
$q$
FROM edges_sql, point_sql
WHERE ST_DWithin(geom, point, %1$s)
ORDER BY geom <-> point),
prepare_cap AS (
SELECT row_number() OVER (PARTITION BY point ORDER BY point, distance) AS rn, *
FROM results)
SELECT edge_id, fraction, side, distance, point, new_line
FROM prepare_cap
WHERE rn <= %2$s
$q$, tolerance, cap);
ret_query = ret_query
|| $q$ST_MakeLine(point, ST_ClosestPoint(geom, point)) AS new_line $q$
|| ret_query_end;
IF dryrun THEN
RAISE NOTICE '%', ret_query;
RETURN;
END IF;
RETURN query EXECUTE ret_query;
END;
$BODY$
LANGUAGE PLPGSQL VOLATILE STRICT
COST ${COST_HIGH} ROWS ${ROWS_LOW};
COMMENT ON FUNCTION pgr_findCloseEdges(TEXT, GEOMETRY[], FLOAT, INTEGER, BOOLEAN)
IS 'pgr_findCloseEdges(Many Points)
- Parameters:
- Edges SQL with columns: id, geom
- Array of POINT geometries
- Maximum separation between geometries
- Optional Parameters
- cap => 1: at most one answer
- dryrun => false: do not output code
- Documentation:
- ${PROJECT_DOC_LINK}/pgr_findCloseEdges.html
';
--v3.8
CREATE FUNCTION pgr_findCloseEdges(
TEXT,
geometry,
FLOAT,
cap INTEGER DEFAULT 1,
dryrun BOOLEAN default false,
OUT edge_id BIGINT,
OUT fraction FLOAT,
OUT side CHAR,
OUT distance FLOAT,
OUT geom geometry,
OUT edge geometry)
returns SETOF RECORD AS
$BODY$
SELECT edge_id, fraction, side, distance, geom, edge
FROM pgr_findCloseEdges($1, ARRAY[$2]::GEOMETRY[], $3, cap, dryrun);
$BODY$
LANGUAGE SQL VOLATILE STRICT
COST ${COST_HIGH} ROWS ${ROWS_LOW};
COMMENT ON FUNCTION pgr_findCloseEdges(TEXT, GEOMETRY, FLOAT, INTEGER, BOOLEAN)
IS 'pgr_findCloseEdges(One Point)
- Parameters:
- Edges SQL with columns: id, geom
- POINT geometry
- Maximum separation between geometries
- Optional Parameters
- cap => 1: at most one answer
- dryrun => false: do not output code
- Documentation:
- ${PROJECT_DOC_LINK}/pgr_findCloseEdges.html
';
|
