Mercurial > gemma

diff schema/gemma.sql @ 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 4c254651d80b
children 831193935739
line wrap: on
line diff
--- a/schema/gemma.sql	Wed May 15 17:55:38 2019 +0200
+++ b/schema/gemma.sql	Wed May 15 19:08:49 2019 +0200
@@ -306,38 +306,34 @@
         fk_gauge_id isrs NOT NULL CONSTRAINT gauge_key REFERENCES gauges,
         measure_date timestamp with time zone NOT NULL,
         country_code char(2) NOT NULL REFERENCES countries,
-        -- TODO: add relations to stuff provided as enumerations
-        sender varchar NOT NULL, -- "from" attribute from DRC
+        sender varchar NOT NULL, -- "from" element from NtS response
         language_code varchar NOT NULL REFERENCES language_codes,
         date_issue timestamp with time zone NOT NULL,
         reference_code varchar(4) NOT NULL REFERENCES depth_references,
         water_level double precision NOT NULL,
-        predicted boolean NOT NULL,
         is_waterlevel boolean NOT NULL,
-        -- XXX: "measure_code" if really only W or Q
-        -- XXX: Do we need "unit" attribute or can we normalise on import?
-        value_min double precision, -- XXX: NOT NULL if predicted?
-        value_max double precision, -- XXX: NOT NULL if predicted?
-        --- TODO: Add a double range type for checking?
         date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-        source_organization varchar NOT NULL, -- "originator"
-        staging_done boolean NOT NULL DEFAULT false
+        source_organization varchar NOT NULL, -- "originator" from NtS response
+        staging_done boolean NOT NULL DEFAULT false,
+        UNIQUE (fk_gauge_id, measure_date, staging_done)
     )
-    -- Constraints are conditional for gauge_measurements, as they
-    -- differ between predicted values and measured ones.  PG does not
-    -- have real conditional unique constraints, but we can use unique
-    -- indeces for that.
-    --
-    -- So we can have a staged and a non-staged
-    -- fk_gauge_id/measure_date pairs in measured values.
-    CREATE UNIQUE INDEX gm_measured_unique_constraint
-        ON gauge_measurements (fk_gauge_id, measure_date, staging_done)
-        WHERE NOT predicted
-    -- And we can have multiple predictions for one point in time
-    -- (but they are never staged).
-    CREATE UNIQUE INDEX gm_predicted_unique_constraint
-        ON gauge_measurements (fk_gauge_id, measure_date, date_issue)
-        WHERE predicted
+
+    CREATE TABLE gauge_predictions (
+        fk_gauge_id isrs NOT NULL CONSTRAINT gauge_key REFERENCES gauges,
+        measure_date timestamp with time zone NOT NULL,
+        country_code char(2) NOT NULL REFERENCES countries,
+        sender varchar NOT NULL, -- "from" element from NtS response
+        language_code varchar NOT NULL REFERENCES language_codes,
+        date_issue timestamp with time zone NOT NULL,
+        reference_code varchar(4) NOT NULL REFERENCES depth_references,
+        water_level double precision NOT NULL,
+        is_waterlevel boolean NOT NULL,
+        conf_interval numrange
+            CHECK (conf_interval @> CAST(water_level AS numeric)),
+        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+        source_organization varchar NOT NULL, -- "originator" from NtS response
+        PRIMARY KEY (fk_gauge_id, measure_date, date_issue)
+    )
 
     CREATE TABLE waterway_axis (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,