Mercurial > gemma

comparison 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
comparison
equal deleted inserted replaced
5512:6738655809eb 5513:68358e4603c8
1 DO $$
2 DECLARE
3 bns CURSOR FOR SELECT * FROM waterway.bottlenecks FOR UPDATE;
4 call_stack text;
5 BEGIN
6 FOR cur_bn IN bns LOOP
7 BEGIN
8 UPDATE waterway.bottlenecks bn SET area = ISRSrange_area(
9 ISRSrange_axis(stretch,
10 COALESCE((
11 -- Guess tolerance from the last successful
12 -- bottleneck import owned by a waterway_admin of
13 -- the country matching the bottleneck_id
14 SELECT DISTINCT ON (usr.country)
15 CAST(substring(msg FROM '((\d*\.)?\d+)$')
16 AS float)
17 FROM import.import_logs log
18 JOIN import.imports imp
19 ON log.import_id = imp.id
20 JOIN users.list_users usr USING (username)
21 WHERE starts_with(log.msg,
22 'Tolerance used to snap waterway axis:')
23 AND imp.kind = 'bn'
24 AND imp.state IN('accepted', 'reviewed')
25 AND usr.rolname = 'waterway_admin'
26 AND usr.country = substring(
27 bn.bottleneck_id FROM 1 FOR 2)
28 ORDER BY usr.country, imp.changed DESC),
29 -- Use default tolerance if originally used cannot
30 -- be determined
31 5)),
32 (SELECT ST_Collect(CAST(area AS geometry))
33 FROM waterway.waterway_area))
34 WHERE CURRENT OF bns;
35 EXCEPTION
36 WHEN no_data_found THEN
37 GET STACKED DIAGNOSTICS call_stack = PG_EXCEPTION_CONTEXT;
38 RAISE WARNING '% (%): %, CONTEXT: %',
39 cur_bn.bottleneck_id, cur_bn.validity,
40 SQLERRM, call_stack;
41 END;
42 END LOOP;
43 END $$;