# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1559745237 -7200
# Node ID 7e7a2b5015953ddea66c292a36ec11a27c14ef5e
# Parent  ca7821e1f7202a8bd31d3378bdab688c5b9c8dbb
Show gauge data in bottlenecks view regardless of whether gauge is erased

diff -r ca7821e1f720 -r 7e7a2b501595 schema/geoserver_views.sql
--- 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