Mercurial > gemma

annotate schema/updates/1466/02.fix_bn_areas.sql @ 5666:37c2354a6024 clickable-links

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Render links only to known bottlenecks
author Thomas Junk <thomas.junk@intevation.de>
date Tue, 05 Dec 2023 15:34:31 +0100
parents 68358e4603c8
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5513
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 DO $$
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 DECLARE
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 bns CURSOR FOR SELECT * FROM waterway.bottlenecks FOR UPDATE;
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 call_stack text;
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 BEGIN
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 FOR cur_bn IN bns LOOP
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 BEGIN
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 UPDATE waterway.bottlenecks bn SET area = ISRSrange_area(
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 ISRSrange_axis(stretch,
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 COALESCE((
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 -- Guess tolerance from the last successful
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 -- bottleneck import owned by a waterway_admin of
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 -- the country matching the bottleneck_id
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 SELECT DISTINCT ON (usr.country)
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 CAST(substring(msg FROM '((\d*\.)?\d+)$')
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 AS float)
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 FROM import.import_logs log
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 JOIN import.imports imp
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 ON log.import_id = imp.id
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 JOIN users.list_users usr USING (username)
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 WHERE starts_with(log.msg,
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 'Tolerance used to snap waterway axis:')
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 AND imp.kind = 'bn'
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 AND imp.state IN('accepted', 'reviewed')
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 AND usr.rolname = 'waterway_admin'
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 AND usr.country = substring(
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 bn.bottleneck_id FROM 1 FOR 2)
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 ORDER BY usr.country, imp.changed DESC),
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 -- Use default tolerance if originally used cannot
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 -- be determined
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 5)),
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 (SELECT ST_Collect(CAST(area AS geometry))
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 FROM waterway.waterway_area))
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 WHERE CURRENT OF bns;
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 EXCEPTION
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 WHEN no_data_found THEN
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 GET STACKED DIAGNOSTICS call_stack = PG_EXCEPTION_CONTEXT;
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 RAISE WARNING '% (%): %, CONTEXT: %',
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 cur_bn.bottleneck_id, cur_bn.validity,
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 SQLERRM, call_stack;
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 END;
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 END LOOP;
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 END $$;