Mercurial > gemma

annotate schema/updates/1466/02.fix_bn_areas.sql @ 5513:68358e4603c8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use current axis only for calculating bottleneck areas This is a fixup of rev. cf25b23e3eec, which introduced historic data for the waterway axis but missed to take this into account in the calculation of bottleneck areas, leading to sometimes excessive runtime and bad results due to multiple (almost) equal axis geometries being considered as candidates in the bottleneck stretch. The database migration tries to recalculate all bottleneck areas, while some might fail that did not fail on import. A warning message is emitted for these and the area is left untouched.
author Tom Gottfried <tom@intevation.de>
date Tue, 19 Oct 2021 13:12:39 +0200
parents
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 $$;