Mercurial > gemma

diff pkg/controllers/gauges.go @ 3277:232fc90e6ee2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Disentangle gauge measurements and predictions Representing both in one table has led to the necessity to make the distinction at many places such as statements, definitions of partial indexes and application code. At least in one place in the AGM import the distinction in application code was too late and measurements matching an approved measurement could have been missed.
author Tom Gottfried <tom@intevation.de>
date Wed, 15 May 2019 19:08:49 +0200
parents 9e087a495f41
children ec6163c6687d
line wrap: on
line diff
--- a/pkg/controllers/gauges.go	Wed May 15 17:55:38 2019 +0200
+++ b/pkg/controllers/gauges.go	Wed May 15 19:08:49 2019 +0200
@@ -42,7 +42,23 @@
   date_issue,
   predicted,
   water_level
-FROM waterway.gauge_measurements
+FROM (
+  SELECT
+    fk_gauge_id,
+    measure_date,
+    date_issue,
+    false AS predicted,
+    water_level
+  FROM waterway.gauge_measurements
+  UNION ALL
+  SELECT
+    fk_gauge_id,
+    measure_date,
+    date_issue,
+    true AS predicted,
+    water_level
+  FROM waterway.gauge_predictions
+) AS gmp
 WHERE
   fk_gauge_id = (
     $1::char(2),
@@ -62,7 +78,27 @@
   value_min,
   value_max,
   predicted
-FROM waterway.gauge_measurements
+FROM (
+  SELECT
+    fk_gauge_id,
+    measure_date,
+    date_issue,
+    water_level,
+    NULL AS value_min,
+    NULL AS value_max,
+    false AS predicted
+  FROM waterway.gauge_measurements
+  UNION ALL
+  SELECT
+    fk_gauge_id,
+    measure_date,
+    date_issue,
+    water_level,
+    lower(conf_interval) AS value_min,
+    upper(conf_interval) AS value_max,
+    true AS predicted
+  FROM waterway.gauge_predictions
+) AS gmp
 WHERE
 `
 
@@ -78,7 +114,6 @@
     $4::char(5),
     $5::int
    )::isrs
-   AND NOT predicted
    AND staging_done
 `
 
@@ -100,7 +135,6 @@
     $4::char(5),
     $5::int
    )::isrs
-   AND NOT predicted
    AND staging_done
 GROUP BY extract(day from measure_date)::varchar || ':' ||
          extract(month from measure_date)::varchar;
@@ -111,7 +145,6 @@
   water_level
 FROM waterway.gauge_measurements
 WHERE
-  NOT predicted AND
   staging_done AND
   fk_gauge_id = (
     $1::char(2),