Mercurial > gemma
view schema/updates/1466/02.fix_bn_areas.sql @ 5677:a57be8bfb6ea sr-v2
More suited file names.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Sun, 11 Feb 2024 09:51:21 +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 $$;