Mercurial > gemma
view schema/updates/1306/01.fillin_sr_surtyp.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 | 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 ;