Mercurial > gemma

changeset 3620:7e7a2b501595

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Show gauge data in bottlenecks view regardless of whether gauge is erased
author Tom Gottfried <tom@intevation.de>
date Wed, 05 Jun 2019 16:33:57 +0200
parents ca7821e1f720
children 30a9fdac70f0
files schema/geoserver_views.sql
diffstat 1 files changed, 26 insertions(+), 4 deletions(-) [+]
line wrap: on
line diff
--- a/schema/geoserver_views.sql	Wed Jun 05 15:52:42 2019 +0200
+++ b/schema/geoserver_views.sql	Wed Jun 05 16:33:57 2019 +0200
@@ -1,4 +1,4 @@
-CREATE OR REPLACE VIEW waterway.gauges_geoserver AS
+CREATE OR REPLACE VIEW waterway.gauges_base_view AS
     SELECT
         g.location,
         isrs_asText(g.location) AS isrs_code,
@@ -11,6 +11,7 @@
         g.geodref,
         g.date_info,
         g.source_organization,
+        g.erased,
         r.rwls AS reference_water_levels,
         wl.measure_date AS gm_measuredate,
         wl.water_level AS gm_waterlevel,
@@ -50,8 +51,29 @@
                     BETWEEN current_timestamp
                         AND current_timestamp + '3 days'::interval
                 GROUP BY location) AS fca
-            USING (location)
-    WHERE NOT g.erased;
+            USING (location);
+
+CREATE OR REPLACE VIEW waterway.gauges_geoserver AS
+    SELECT
+        location,
+        isrs_code,
+        objname,
+        geom,
+        applicability_from_km,
+        applicability_to_km,
+        validity,
+        zero_point,
+        geodref,
+        date_info,
+        source_organization,
+        reference_water_levels,
+        gm_measuredate,
+        gm_waterlevel,
+        gm_n_14d,
+        forecast_accuracy_3d,
+        forecast_accuracy_1d
+    FROM waterway.gauges_base_view
+    WHERE NOT erased;
 
 CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS
     SELECT location_code,
@@ -108,7 +130,7 @@
         g.forecast_accuracy_3d,
         g.forecast_accuracy_1d
     FROM waterway.bottlenecks b
-        LEFT JOIN waterway.gauges_geoserver g
+        LEFT JOIN waterway.gauges_base_view g
             ON b.gauge_location = g.location AND b.gauge_validity = g.validity
         LEFT JOIN fairway_availability_latest fal
             ON b.id = fal.bottleneck_id