Mercurial > gemma

comparison schema/gemma.sql @ 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 d9eda49a52f4
children c04b1409a596
comparison
equal deleted inserted replaced
3388:1876b204b004 3389:45a629a3a8b8
312 location isrs NOT NULL, 312 location isrs NOT NULL,
313 validity tstzrange NOT NULL, 313 validity tstzrange NOT NULL,
314 CONSTRAINT gauge_key 314 CONSTRAINT gauge_key
315 FOREIGN KEY (location, validity) REFERENCES gauges, 315 FOREIGN KEY (location, validity) REFERENCES gauges,
316 measure_date timestamp with time zone NOT NULL, 316 measure_date timestamp with time zone NOT NULL,
317 CHECK (measure_date <@ validity),
317 country_code char(2) NOT NULL REFERENCES countries, 318 country_code char(2) NOT NULL REFERENCES countries,
318 sender varchar NOT NULL, -- "from" element from NtS response 319 sender varchar NOT NULL, -- "from" element from NtS response
319 language_code varchar NOT NULL REFERENCES language_codes, 320 language_code varchar NOT NULL REFERENCES language_codes,
320 date_issue timestamp with time zone 321 date_issue timestamp with time zone NOT NULL,
321 NOT NULL CHECK (date_issue <@ validity),
322 reference_code varchar(4) NOT NULL REFERENCES depth_references, 322 reference_code varchar(4) NOT NULL REFERENCES depth_references,
323 water_level double precision NOT NULL, 323 water_level double precision NOT NULL,
324 date_info timestamp with time zone NOT NULL, 324 date_info timestamp with time zone NOT NULL,
325 source_organization varchar NOT NULL, -- "originator" from NtS response 325 source_organization varchar NOT NULL, -- "originator" from NtS response
326 staging_done boolean NOT NULL DEFAULT false, 326 staging_done boolean NOT NULL DEFAULT false,
327 UNIQUE (location, validity, measure_date, staging_done) 327 UNIQUE (location, measure_date, staging_done)
328 ) 328 )
329 329
330 CREATE TABLE gauge_predictions ( 330 CREATE TABLE gauge_predictions (
331 location isrs NOT NULL, 331 location isrs NOT NULL,
332 validity tstzrange NOT NULL, 332 validity tstzrange NOT NULL,
333 CONSTRAINT gauge_key 333 CONSTRAINT gauge_key
334 FOREIGN KEY (location, validity) REFERENCES gauges, 334 FOREIGN KEY (location, validity) REFERENCES gauges,
335 measure_date timestamp with time zone NOT NULL, 335 measure_date timestamp with time zone NOT NULL,
336 CHECK (measure_date >= lower(validity)),
336 country_code char(2) NOT NULL REFERENCES countries, 337 country_code char(2) NOT NULL REFERENCES countries,
337 sender varchar NOT NULL, -- "from" element from NtS response 338 sender varchar NOT NULL, -- "from" element from NtS response
338 language_code varchar NOT NULL REFERENCES language_codes, 339 language_code varchar NOT NULL REFERENCES language_codes,
339 date_issue timestamp with time zone 340 date_issue timestamp with time zone NOT NULL,
340 NOT NULL CHECK (date_issue <@ validity),
341 reference_code varchar(4) NOT NULL REFERENCES depth_references, 341 reference_code varchar(4) NOT NULL REFERENCES depth_references,
342 water_level double precision NOT NULL, 342 water_level double precision NOT NULL,
343 conf_interval numrange 343 conf_interval numrange
344 CHECK (conf_interval @> CAST(water_level AS numeric)), 344 CHECK (conf_interval @> CAST(water_level AS numeric)),
345 date_info timestamp with time zone NOT NULL, 345 date_info timestamp with time zone NOT NULL,
346 source_organization varchar NOT NULL, -- "originator" from NtS response 346 source_organization varchar NOT NULL, -- "originator" from NtS response
347 PRIMARY KEY (location, validity, measure_date, date_issue) 347 PRIMARY KEY (location, measure_date, date_issue)
348 ) 348 )
349 349
350 CREATE TABLE waterway_axis ( 350 CREATE TABLE waterway_axis (
351 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 351 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
352 wtwaxs geography(LINESTRING, 4326) NOT NULL 352 wtwaxs geography(LINESTRING, 4326) NOT NULL