Mercurial > gemma
annotate schema/updates/1439/01.add_bn_id_to_bn_overview.sql @ 5624:29d729607ac1
Added tag v5.7 for changeset ff0af91a7f62
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Thu, 22 Dec 2022 18:42:14 +0100 |
parents | 0b051ee3f238 |
children |
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'; |