Mercurial > gemma
changeset 3389:45a629a3a8b8
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 (