Mercurial > gemma

changeset 2396:6af41bb6656f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Using a table in a view before it is created is not possible The view bottlenecks_geoserver used the table fairway_availability before it was created.
author Tom Gottfried <tom@intevation.de>
date Wed, 27 Feb 2019 16:20:22 +0100
parents 975e52210474
children b95234702ee9
files schema/gemma.sql
diffstat 1 files changed, 46 insertions(+), 45 deletions(-) [+]
line wrap: on
line diff
--- a/schema/gemma.sql	Wed Feb 27 14:02:28 2019 +0100
+++ b/schema/gemma.sql	Wed Feb 27 16:20:22 2019 +0100
@@ -473,51 +473,6 @@
         PRIMARY KEY (bottleneck_id, riverbed)
     )
 
-    -- Published view for GeoServer
-    CREATE VIEW bottlenecks_geoserver AS
-    WITH fairway_availability_latest AS (
-        SELECT DISTINCT ON (bottleneck_id) bottleneck_id,date_info,critical
-        FROM fairway_availability
-        ORDER BY bottleneck_id, date_info DESC NULLS LAST),
-    gauge_measurements_waterlevel AS (
-        SELECT DISTINCT ON (fk_gauge_id) fk_gauge_id,measure_date,predicted,water_level
-        FROM gauge_measurements WHERE predicted ='false'
-        ORDER BY fk_gauge_id, measure_date DESC NULLS LAST)
-    SELECT
-        b.id,
-        b.bottleneck_id,
-        b.objnam,
-        b.nobjnm,
-        b.stretch,
-        b.area,
-        b.rb,
-        b.lb,
-        b.responsible_country,
-        b.revisiting_time,
-        b.limiting,
-        b.date_info,
-        b.source_organization,
-        g.location AS gauge_isrs_code,
-        g.objname AS gauge_objname,
-        json_object_agg(r.depth_reference, r.value) AS reference_water_levels,
-        fal.date_info AS fa_date_info,
-        fal.critical AS fa_critical,
-        gmw.water_level as gm_waterlevel
-    FROM bottlenecks b LEFT JOIN gauges g ON b.fk_g_fid = g.location
-        LEFT JOIN LATERAL (
-            SELECT gauge_id,depth_reference,value
-            FROM gauges_reference_water_levels
-            ) r ON r.gauge_id = b.fk_g_fid
-        LEFT JOIN LATERAL (
-            SELECT bottleneck_id,date_info,critical
-            FROM  fairway_availability_latest
-            WHERE b.id=bottleneck_id) fal ON TRUE
-        LEFT JOIN LATERAL (
-            SELECT water_level
-            FROM  gauge_measurements_waterlevel
-            WHERE b.fk_g_fid=fk_gauge_id) gmw ON TRUE
-    GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level;
-
     CREATE TABLE sounding_results (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
         bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
@@ -625,6 +580,52 @@
       SELECT bottleneck_id, max(date_info) AS current FROM sounding_results
       GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id
     ORDER BY objnam
+
+    -- Published view for GeoServer
+    CREATE VIEW bottlenecks_geoserver AS
+    WITH fairway_availability_latest AS (
+        SELECT DISTINCT ON (bottleneck_id) bottleneck_id,date_info,critical
+        FROM fairway_availability
+        ORDER BY bottleneck_id, date_info DESC NULLS LAST),
+    gauge_measurements_waterlevel AS (
+        SELECT DISTINCT ON (fk_gauge_id)
+            fk_gauge_id, measure_date, predicted, water_level
+        FROM gauge_measurements WHERE predicted ='false'
+        ORDER BY fk_gauge_id, measure_date DESC NULLS LAST)
+    SELECT
+        b.id,
+        b.bottleneck_id,
+        b.objnam,
+        b.nobjnm,
+        b.stretch,
+        b.area,
+        b.rb,
+        b.lb,
+        b.responsible_country,
+        b.revisiting_time,
+        b.limiting,
+        b.date_info,
+        b.source_organization,
+        g.location AS gauge_isrs_code,
+        g.objname AS gauge_objname,
+        json_object_agg(r.depth_reference, r.value) AS reference_water_levels,
+        fal.date_info AS fa_date_info,
+        fal.critical AS fa_critical,
+        gmw.water_level as gm_waterlevel
+    FROM bottlenecks b LEFT JOIN gauges g ON b.fk_g_fid = g.location
+        LEFT JOIN LATERAL (
+            SELECT gauge_id,depth_reference,value
+            FROM gauges_reference_water_levels
+            ) r ON r.gauge_id = b.fk_g_fid
+        LEFT JOIN LATERAL (
+            SELECT bottleneck_id,date_info,critical
+            FROM  fairway_availability_latest
+            WHERE b.id=bottleneck_id) fal ON TRUE
+        LEFT JOIN LATERAL (
+            SELECT water_level
+            FROM  gauge_measurements_waterlevel
+            WHERE b.fk_g_fid=fk_gauge_id) gmw ON TRUE
+    GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level;
 ;
 
 -- Configure primary keys for geoserver views