# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1560277945 -7200
# Node ID 89a39783c20a0798811baccf5940358fac1a667c
# Parent  6d6577a8796321976513ec5a434ad1f3d243dbb6
Add forecast and measurement quality parameters to sections and stretches

diff -r 6d6577a87963 -r 89a39783c20a client/src/components/identify/formatter.js
--- a/client/src/components/identify/formatter.js	Tue Jun 11 20:28:38 2019 +0200
+++ b/client/src/components/identify/formatter.js	Tue Jun 11 20:32:25 2019 +0200
@@ -58,10 +58,22 @@
     label: "Waterway Profile"
   },
   stretches_geoserver: {
-    label: "Stretch"
+    label: "Stretch",
+    props: p => {
+      if (p.key === "gm_measuredate") p.key = "Min. Gauge Waterlevel Date";
+      if (p.key === "gm_n_14d") p.key = "Min. G.W. Count in Last 14 Days";
+
+      return p;
+    }
   },
   sections_geoserver: {
-    label: "Section"
+    label: "Section",
+    props: p => {
+      if (p.key === "gm_measuredate") p.key = "Min. Gauge Waterlevel Date";
+      if (p.key === "gm_n_14d") p.key = "Min. G.W. Count in Last 14 Days";
+
+      return p;
+    }
   },
   gauges_geoserver: {
     label: "Gauge",
diff -r 6d6577a87963 -r 89a39783c20a schema/geoserver_views.sql
--- a/schema/geoserver_views.sql	Tue Jun 11 20:28:38 2019 +0200
+++ b/schema/geoserver_views.sql	Tue Jun 11 20:32:25 2019 +0200
@@ -139,34 +139,46 @@
 
 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS
     SELECT
-        id,
-        name,
-        (stretch).lower::varchar as lower,
-        (stretch).upper::varchar as upper,
-        area::Geometry(MULTIPOLYGON, 4326),
-        objnam,
-        nobjnam,
-        date_info,
-        source_organization,
+        s.id,
+        s.name,
+        (s.stretch).lower::varchar as lower,
+        (s.stretch).upper::varchar as upper,
+        s.area::Geometry(MULTIPOLYGON, 4326),
+        s.objnam,
+        s.nobjnam,
+        s.date_info,
+        s.source_organization,
         (SELECT string_agg(country_code, ', ')
             FROM waterway.stretch_countries
-            WHERE stretches_id = id) AS countries,
-        staging_done
-    FROM waterway.stretches;
+            WHERE stretches_id = s.id) AS countries,
+        s.staging_done,
+        min(g.gm_measuredate) AS gm_measuredate,
+        min(g.gm_n_14d) AS gm_n_14d,
+        max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
+        max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
+    FROM waterway.stretches s
+        LEFT JOIN waterway.gauges_geoserver g ON g.location <@ s.stretch
+    GROUP BY s.id;
 
 CREATE OR REPLACE VIEW waterway.sections_geoserver AS
     SELECT
-        id,
-        name,
-        (section).lower::varchar as lower,
-        (section).upper::varchar as upper,
-        area::Geometry(MULTIPOLYGON, 4326),
-        objnam,
-        nobjnam,
-        date_info,
-        source_organization,
-        staging_done
-    FROM waterway.sections;
+        s.id,
+        s.name,
+        (s.section).lower::varchar as lower,
+        (s.section).upper::varchar as upper,
+        s.area::Geometry(MULTIPOLYGON, 4326),
+        s.objnam,
+        s.nobjnam,
+        s.date_info,
+        s.source_organization,
+        s.staging_done,
+        min(g.gm_measuredate) AS gm_measuredate,
+        min(g.gm_n_14d) AS gm_n_14d,
+        max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
+        max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
+    FROM waterway.sections s
+        LEFT JOIN waterway.gauges_geoserver g ON g.location <@ s.section
+    GROUP BY s.id;
 
 CREATE OR REPLACE VIEW waterway.sounding_results_contour_lines_geoserver AS
     SELECT bottleneck_id,