Mercurial > gemma
annotate schema/updates/1466/02.fix_bn_areas.sql @ 5560:f2204f91d286
Join the log lines of imports to the log exports to recover data from them.
Used in SR export to extract information that where in the meta json
but now are only found in the log.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 09 Feb 2022 18:34:40 +0100 |
parents | 68358e4603c8 |
children |
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 $$; |