Mercurial > gemma

annotate schema/updates/1439/01.add_bn_id_to_bn_overview.sql @ 5736:55892008ec96 default tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fixed a bunch of corner cases in WG import.
author Sascha Wilde <wilde@sha-bang.de>
date Wed, 29 May 2024 19:02:42 +0200
parents 0b051ee3f238
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5229
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
1 -- Add bottleneck_id column to bottleneck_overview view.
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
2
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
3 UPDATE sys_admin.published_services
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
4 SET
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
5 view_def = $$
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
6 SELECT
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
7 objnam AS name,
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
8 bn.bottleneck_id,
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
9 ST_Centroid(area) AS point,
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
10 (lower(stretch)).hectometre AS from,
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
11 (upper(stretch)).hectometre AS to,
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
12 sr.current::text,
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
13 responsible_country
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
14 FROM waterway.bottlenecks bn LEFT JOIN (
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
15 SELECT bottleneck_id, max(date_info) AS current
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
16 FROM waterway.sounding_results
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
17 GROUP BY bottleneck_id) sr
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
18 ON sr.bottleneck_id = bn.bottleneck_id
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
19 WHERE bn.validity @> current_timestamp
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
20 ORDER BY objnam
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
21 $$
0b051ee3f238 Added bottleneck_id column to bottleneck_overview view.
wilde@azure1.rgb.intevation.de
parents:
diff changeset
22 WHERE schema = 'waterway' AND name = 'bottleneck_overview';