Mercurial > gemma
annotate schema/updates/1439/01.add_bn_id_to_bn_overview.sql @ 5425:345515bc4548 marking-single-beam
Make GeoServer layer sounding_results_marking_points_geoserver working.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Fri, 09 Jul 2021 22:03:44 +0200 |
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'; |