-
Notifications
You must be signed in to change notification settings - Fork 8
/
function-centreline_case1.sql
246 lines (210 loc) · 9.55 KB
/
function-centreline_case1.sql
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
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
DROP FUNCTION IF EXISTS gis._centreline_case1 (text, text, text, double precision);
CREATE OR REPLACE FUNCTION gis._centreline_case1(
highway2 text,
btwn2 text,
direction_btwn2 text,
metres_btwn2 double precision
)
RETURNS TABLE (
int1 integer,
geo_id integer,
lf_name character varying,
ind_line_geom geometry,
line_geom geometry,
line_geom_cut geometry,
section numrange,
combined_section numrange,
oid1_geom geometry,
oid1_geom_translated geometry,
objectid integer,
fcode integer,
fcode_desc character varying,
lev_sum integer
)
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
BEGIN
CREATE TEMP TABLE IF NOT EXISTS _wip(
int1 int,
geo_id integer,
lf_name varchar,
ind_line_geom geometry,
line_geom geometry,
new_line geometry,
section numrange,
oid1_geom geometry,
oid1_geom_translated geometry,
objectid integer,
fcode int,
fcode_desc varchar,
lev_sum int,
line_geom_cut geometry,
line_geom_reversed geometry,
combined_section numrange,
whole_centreline geometry
);
TRUNCATE TABLE _wip;
INSERT INTO _wip (int1, geo_id, lf_name, ind_line_geom, new_line, oid1_geom, oid1_geom_translated,
objectid, fcode, fcode_desc, lev_sum)
WITH get_int AS
(
SELECT
oid_geom AS oid1_geom,
oid_geom_translated AS oid1_geom_translated,
ST_MakeLine(oid_geom, oid_geom_translated) AS new_line, -- line from the intersection point to the translated point
int_id_found AS int1,
get_geom.lev_sum
FROM gis._get_intersection_geom(highway2, btwn2, direction_btwn2, metres_btwn2, 0) get_geom
),
get_lines AS (
SELECT
cl.centreline_id AS geo_id,
cl.linear_name_full AS lf_name,
cl.objectid,
cl.feature_code AS fcode,
cl.feature_code_desc AS fcode_desc,
cl.geom,
get_int.oid1_geom,
get_int.oid1_geom_translated,
ST_DWithin(
ST_Transform(cl.geom, 2952),
ST_BUFFER(ST_Transform(get_int.new_line, 2952), 3*metres_btwn2, 'endcap=flat join=round'),
10
) AS dwithin
FROM gis_core.centreline_latest AS cl, get_int
WHERE
ST_DWithin(
ST_Transform(cl.geom, 2952),
ST_BUFFER(ST_Transform(get_int.new_line, 2952), 3*metres_btwn2, 'endcap=flat join=round'),
10) = TRUE
--as some centreline is much longer compared to the short road segment, the ratio is set to 0.1 instead of 0.9
AND ST_Length(
ST_Intersection(
ST_Buffer(ST_Transform(get_int.new_line, 2952), 3*(ST_Length(ST_Transform(get_int.new_line, 2952))), 'endcap=flat join=round') ,
ST_Transform(cl.geom, 2952))
) / ST_Length(ST_Transform(cl.geom, 2952)) > 0.1
)
SELECT
get_int.int1,
get_lines.geo_id,
get_lines.lf_name,
ST_LineMerge(get_lines.geom) AS ind_line_geom,
get_int.new_line,
get_int.oid1_geom,
get_int.oid1_geom_translated,
get_lines.objectid,
get_lines.fcode,
get_lines.fcode_desc,
get_int.lev_sum
FROM get_int, get_lines
WHERE get_lines.lf_name = highway2;
RAISE NOTICE 'Centrelines within the buffer and have the same bylaws highway name are found.';
--TO CUT combined ind_line_geom and put into line_geom
UPDATE _wip SET whole_centreline =
(
SELECT ST_LineMerge(ST_Union(_wip.ind_line_geom))
FROM _wip
GROUP BY _wip.lf_name
);
UPDATE _wip SET line_geom_cut = (
CASE WHEN metres_btwn2 > ST_Length(ST_Transform(_wip.whole_centreline, 2952))
AND metres_btwn2 - ST_Length(ST_Transform(_wip.whole_centreline, 2952)) < 15
THEN _wip.whole_centreline
-- metres_btwn2/ST_Length(d.geom) is the fraction that is supposed to be cut off from the dissolved centreline segment(s)
-- cut off the first fraction of the dissolved line, and the second and check to see which one is closer to the original interseciton
-- aka to get the starting point of how the line is drawn and then cut accordingly
--when the from_intersection is at the end point of the original centreline
WHEN ST_LineLocatePoint(_wip.whole_centreline, _wip.oid1_geom)
> ST_LineLocatePoint(_wip.whole_centreline, ST_ClosestPoint(_wip.whole_centreline, ST_EndPoint(_wip.new_line)))
THEN ST_LineSubstring(_wip.whole_centreline,
ST_LineLocatePoint(_wip.whole_centreline, _wip.oid1_geom) - (metres_btwn2/ST_Length(ST_Transform(_wip.whole_centreline, 2952))),
ST_LineLocatePoint(_wip.whole_centreline, _wip.oid1_geom) )
--when the from_intersection is at the start point of the original centreline
WHEN ST_LineLocatePoint(_wip.whole_centreline, _wip.oid1_geom)
< ST_LineLocatePoint(_wip.whole_centreline, ST_ClosestPoint(_wip.whole_centreline, ST_EndPoint(_wip.new_line)))
-- take the substring from the intersection to the point x metres ahead of it
THEN ST_LineSubstring(_wip.whole_centreline,
ST_LineLocatePoint(_wip.whole_centreline, _wip.oid1_geom),
ST_LineLocatePoint(_wip.whole_centreline, _wip.oid1_geom) + (metres_btwn2/ST_Length(ST_Transform(_wip.whole_centreline, 2952))) )
END
);
UPDATE _wip SET combined_section = (
-- case where the section of street from the intersection in the specified direction is shorter than x metres
--take the whole centreline, range is [0,1]
CASE
WHEN metres_btwn2 > ST_Length(ST_Transform(_wip.whole_centreline, 2952))
AND metres_btwn2 - ST_Length(ST_Transform(_wip.whole_centreline, 2952)) < 15
THEN numrange(0, 1, '[]')
--when the from_intersection is at the end point of the original centreline
--range is [xxx, 1]
WHEN ST_LineLocatePoint(_wip.whole_centreline, _wip.oid1_geom)
> ST_LineLocatePoint(_wip.whole_centreline, ST_ClosestPoint(_wip.whole_centreline, ST_EndPoint(new_line)))
THEN numrange ((ST_LineLocatePoint(_wip.whole_centreline, _wip.oid1_geom) - (metres_btwn2/ST_Length(ST_Transform(_wip.whole_centreline, 2952))))::numeric , 1::numeric, '[]')
--when the from_intersection is at the start point of the original centreline
--range is [0, xxx]
WHEN ST_LineLocatePoint(_wip.whole_centreline, _wip.oid1_geom)
< ST_LineLocatePoint(_wip.whole_centreline, ST_ClosestPoint(_wip.whole_centreline, ST_EndPoint(new_line)))
-- take the substring from the intersection to the point x metres ahead of it
THEN numrange(0::numeric, (ST_LineLocatePoint(_wip.whole_centreline, _wip.oid1_geom) + (metres_btwn2/ST_Length(ST_Transform(_wip.whole_centreline, 2952))))::numeric, '[]')
END
);
RAISE NOTICE 'Centrelines are now combined and cut as specified on the bylaws.
direction_btwn2: %, metres_btwn2: %, whole_centreline: %, line_geom: %',
direction_btwn2, metres_btwn2, ST_ASText(ST_Union(_wip.whole_centreline)) FROM _wip,
ST_ASText(ST_Union(_wip.line_geom_cut)) FROM _wip;
--TO SEPARATE line_geom that got cut into individual rows with section stated
UPDATE _wip SET line_geom_reversed = (
--TO SEPARATE line_geom that got cut into individual rows with section stated
CASE WHEN ABS(DEGREES(ST_Azimuth(ST_StartPoint(_wip.ind_line_geom), ST_EndPoint(_wip.ind_line_geom)))
- DEGREES(ST_Azimuth(ST_StartPoint(_wip.line_geom_cut), ST_EndPoint(_wip.line_geom_cut)))
) > 180
--The lines are two different orientations
THEN ST_Reverse(_wip.line_geom_cut)
ELSE _wip.line_geom_cut
END
);
UPDATE _wip SET section = (
--combined_section = '[0,1]'
CASE WHEN lower(_wip.combined_section) = 0 AND upper(_wip.combined_section) = 1
THEN numrange(0, 1,'[]')
--for combined_section = '[%,1]' or '[0,%]'
--where the whole centreline is within the buffer
WHEN ST_Within(ST_Transform(_wip.ind_line_geom, 2952), ST_BUFFER(ST_Transform(_wip.line_geom_reversed, 2952), 2, 'endcap=square join=round')) = TRUE
THEN numrange(0, 1, '[]')
--where part of the centreline is within the buffer, and then find out the startpoint of the individual centreline to know which part of the centreline needs to be cut
WHEN ST_Within(ST_StartPoint(ST_Transform(_wip.ind_line_geom, 2952)), ST_BUFFER(ST_Transform(_wip.line_geom_reversed, 2952), 2, 'endcap=square join=round')) = TRUE
THEN numrange(0, (ST_LineLocatePoint(_wip.ind_line_geom, ST_EndPoint(_wip.line_geom_reversed)))::numeric, '[]')
WHEN ST_Within(ST_EndPoint(ST_Transform(_wip.ind_line_geom, 2952)), ST_BUFFER(ST_Transform(_wip.line_geom_reversed, 2952), 2, 'endcap=square join=round')) = TRUE
THEN numrange((ST_LineLocatePoint(_wip.ind_line_geom, ST_StartPoint(_wip.line_geom_reversed)))::numeric, 1, '[]')
ELSE NULL
END);
UPDATE _wip SET line_geom = (
--combined_section = '[0,1]'
CASE WHEN lower(_wip.combined_section) = 0 AND upper(_wip.combined_section) = 1
THEN _wip.ind_line_geom
--for combined_section = '[%,1]' or '[0,%]'
--where the whole centreline is within the buffer
WHEN ST_Within(ST_Transform(_wip.ind_line_geom, 2952), ST_BUFFER(ST_Transform(_wip.line_geom_reversed, 2952), 2, 'endcap=square join=round')) = TRUE
THEN _wip.ind_line_geom
--where part of the centreline is within the buffer
ELSE ST_Intersection(ST_Buffer(_wip.line_geom_reversed, 0.00001), _wip.ind_line_geom)
END);
RAISE NOTICE 'Centrelines are now separated into their respective geo_id rows.';
RETURN QUERY
SELECT _wip.int1, _wip.geo_id, _wip.lf_name,
_wip.ind_line_geom, _wip.line_geom, _wip.line_geom_cut,
_wip.section, _wip.combined_section,
_wip.oid1_geom, _wip.oid1_geom_translated,
_wip.objectid, _wip.fcode, _wip.fcode_desc, _wip.lev_sum
FROM _wip;
DROP TABLE _wip;
EXCEPTION WHEN SQLSTATE 'XX000' THEN
RAISE WARNING 'Internal error at case2 for highway2 = % , btwn2 = % : ''%'' ',
highway2, btwn2, SQLERRM ;
END;
$BODY$;
ALTER FUNCTION gis._centreline_case1(text, text, text, double precision)
OWNER TO gis_admins;