Mercurial > gemma

changeset 3389:45a629a3a8b8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix constraints on relationship between gauges and measurements/predictions measure_date is the time of measurement, not date_issue. There can be only one measurement at a time for a gauge location, independently of gauge versions with differing validity. The 'measure_date' of a prediction can well be beyond the validity of the gauge, since a new gauge version might appear within the time range of the forecast. There can be only one prediction at a time per date of issue, independently of gauge versions with differing validity.
author Tom Gottfried <tom@intevation.de>
date Wed, 22 May 2019 16:44:13 +0200
parents 1876b204b004
children 8adf1fc51acd
files pkg/imports/agm.go schema/gemma.sql
diffstat 2 files changed, 7 insertions(+), 7 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/imports/agm.go	Wed May 22 16:30:43 2019 +0200
+++ b/pkg/imports/agm.go	Wed May 22 16:44:13 2019 +0200
@@ -75,7 +75,7 @@
   SELECT o.id AS id
   FROM waterway.gauge_measurements o
   JOIN waterway.gauge_measurements n
-    USING (location, validity, measure_date)
+    USING (location, measure_date)
     WHERE n.id IN (SELECT key FROM staged)
 	  AND o.id NOT IN (SELECT key FROM staged)
 )
--- a/schema/gemma.sql	Wed May 22 16:30:43 2019 +0200
+++ b/schema/gemma.sql	Wed May 22 16:44:13 2019 +0200
@@ -314,17 +314,17 @@
         CONSTRAINT gauge_key
             FOREIGN KEY (location, validity) REFERENCES gauges,
         measure_date timestamp with time zone NOT NULL,
+        CHECK (measure_date <@ validity),
         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 CHECK (date_issue <@ validity),
+        date_issue timestamp with time zone NOT NULL,
         reference_code varchar(4) NOT NULL REFERENCES depth_references,
         water_level double precision NOT NULL,
         date_info timestamp with time zone NOT NULL,
         source_organization varchar NOT NULL, -- "originator" from NtS response
         staging_done boolean NOT NULL DEFAULT false,
-        UNIQUE (location, validity, measure_date, staging_done)
+        UNIQUE (location, measure_date, staging_done)
     )
 
     CREATE TABLE gauge_predictions (
@@ -333,18 +333,18 @@
         CONSTRAINT gauge_key
             FOREIGN KEY (location, validity) REFERENCES gauges,
         measure_date timestamp with time zone NOT NULL,
+        CHECK (measure_date >= lower(validity)),
         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 CHECK (date_issue <@ validity),
+        date_issue timestamp with time zone NOT NULL,
         reference_code varchar(4) NOT NULL REFERENCES depth_references,
         water_level double precision NOT NULL,
         conf_interval numrange
             CHECK (conf_interval @> CAST(water_level AS numeric)),
         date_info timestamp with time zone NOT NULL,
         source_organization varchar NOT NULL, -- "originator" from NtS response
-        PRIMARY KEY (location, validity, measure_date, date_issue)
+        PRIMARY KEY (location, measure_date, date_issue)
     )
 
     CREATE TABLE waterway_axis (