Mercurial > gemma
view 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 |
line wrap: on
line source
DO $$ DECLARE bns CURSOR FOR SELECT * FROM waterway.bottlenecks FOR UPDATE; call_stack text; BEGIN FOR cur_bn IN bns LOOP BEGIN UPDATE waterway.bottlenecks bn SET area = ISRSrange_area( ISRSrange_axis(stretch, COALESCE(( -- Guess tolerance from the last successful -- bottleneck import owned by a waterway_admin of -- the country matching the bottleneck_id SELECT DISTINCT ON (usr.country) CAST(substring(msg FROM '((\d*\.)?\d+)$') AS float) FROM import.import_logs log JOIN import.imports imp ON log.import_id = imp.id JOIN users.list_users usr USING (username) WHERE starts_with(log.msg, 'Tolerance used to snap waterway axis:') AND imp.kind = 'bn' AND imp.state IN('accepted', 'reviewed') AND usr.rolname = 'waterway_admin' AND usr.country = substring( bn.bottleneck_id FROM 1 FOR 2) ORDER BY usr.country, imp.changed DESC), -- Use default tolerance if originally used cannot -- be determined 5)), (SELECT ST_Collect(CAST(area AS geometry)) FROM waterway.waterway_area)) WHERE CURRENT OF bns; EXCEPTION WHEN no_data_found THEN GET STACKED DIAGNOSTICS call_stack = PG_EXCEPTION_CONTEXT; RAISE WARNING '% (%): %, CONTEXT: %', cur_bn.bottleneck_id, cur_bn.validity, SQLERRM, call_stack; END; END LOOP; END $$;