Mercurial > gemma
view schema/updates/1306/01.fillin_sr_surtyp.sql @ 5007:799e8248de8d
First step to move geometry creation for WFS features to loader.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 11 Mar 2020 14:23:52 +0100 |
parents | 901a74d866ac |
children |
line wrap: on
line source
-- This is Free Software under GNU Affero General Public License v >= 3.0 -- without warranty, see README.md and license for details. -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt -- Copyright (C) 2019 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Sascha Wilde <sascha.wilde@intevation.de> -- -- This is is an dummy update which does not change the data base -- schema but does fill in missing data. The goal is to add missing -- surtyp in sounding reults by extracting it from the import logs. -- WITH loginfo AS ( SELECT i.enqueued, (i.data::json -> 'date')::text::date AS date_info, array_agg(l.msg ORDER BY l.msg) AS msgs FROM import.imports i, import.import_logs l WHERE i.kind='sr' AND i.state IN ('accepted','pending') AND l.import_id = i.id AND ( l.msg LIKE 'Bottleneck: %' OR l.msg LIKE 'Processing as %' ) GROUP BY i.enqueued, i.data ), typeinfo AS ( SELECT DISTINCT ON (date_info, msgs[1]) enqueued, date_info, substring(msgs[1] from 13) AS bn_name, CASE WHEN msgs[2] = 'Processing as multi beam scan.' THEN 'multi' ELSE 'single' END AS surtype FROM loginfo ORDER BY date_info, msgs[1], enqueued DESC ), bnid_type AS ( SELECT DISTINCT t.*, bn.bottleneck_id FROM typeinfo t, waterway.bottlenecks bn WHERE t.bn_name = bn.objnam ) UPDATE waterway.sounding_results AS sr SET surtyp = bt.surtype FROM bnid_type bt WHERE sr.bottleneck_id = bt.bottleneck_id AND sr.date_info = bt.date_info AND surtyp IS NULL ;