Mercurial > gemma

annotate schema/updates/1306/01.fillin_sr_surtyp.sql @ 5666:37c2354a6024 clickable-links

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Render links only to known bottlenecks
author Thomas Junk <thomas.junk@intevation.de>
date Tue, 05 Dec 2023 15:34:31 +0100
parents 901a74d866ac
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4705
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
2 -- without warranty, see README.md and license for details.
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
3
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
6
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
7 -- Copyright (C) 2019 by via donau
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
9 -- Software engineering by Intevation GmbH
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
10
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
11 -- Author(s):
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
12 -- * Sascha Wilde <sascha.wilde@intevation.de>
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
13
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
14 --
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
15 -- This is is an dummy update which does not change the data base
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
16 -- schema but does fill in missing data. The goal is to add missing
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
17 -- surtyp in sounding reults by extracting it from the import logs.
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
18 --
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
19
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
20 WITH loginfo AS (
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
21 SELECT i.enqueued,
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
22 (i.data::json -> 'date')::text::date AS date_info,
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
23 array_agg(l.msg ORDER BY l.msg) AS msgs
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
24 FROM import.imports i,
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
25 import.import_logs l
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
26 WHERE i.kind='sr' AND i.state IN ('accepted','pending')
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
27 AND l.import_id = i.id
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
28 AND ( l.msg LIKE 'Bottleneck: %' OR l.msg LIKE 'Processing as %' )
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
29 GROUP BY i.enqueued, i.data
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
30 ),
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
31 typeinfo AS (
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
32 SELECT DISTINCT ON (date_info, msgs[1]) enqueued, date_info,
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
33 substring(msgs[1] from 13) AS bn_name,
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
34 CASE WHEN msgs[2] = 'Processing as multi beam scan.'
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
35 THEN 'multi' ELSE 'single' END AS surtype
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
36 FROM loginfo
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
37 ORDER BY date_info, msgs[1], enqueued DESC
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
38 ),
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
39 bnid_type AS (
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
40 SELECT DISTINCT t.*, bn.bottleneck_id
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
41 FROM typeinfo t, waterway.bottlenecks bn
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
42 WHERE t.bn_name = bn.objnam
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
43 )
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
44 UPDATE waterway.sounding_results AS sr
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
45 SET surtyp = bt.surtype
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
46 FROM bnid_type bt
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
47 WHERE sr.bottleneck_id = bt.bottleneck_id
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
48 AND sr.date_info = bt.date_info
901a74d866ac Added dummy db update to fill in missing surtyp for sr data.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
49 AND surtyp IS NULL ;