Mercurial > gemma

comparison 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
comparison
equal deleted inserted replaced
3276:75db3199f76e 3277:232fc90e6ee2
304 CREATE TABLE gauge_measurements ( 304 CREATE TABLE gauge_measurements (
305 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 305 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
306 fk_gauge_id isrs NOT NULL CONSTRAINT gauge_key REFERENCES gauges, 306 fk_gauge_id isrs NOT NULL CONSTRAINT gauge_key REFERENCES gauges,
307 measure_date timestamp with time zone NOT NULL, 307 measure_date timestamp with time zone NOT NULL,
308 country_code char(2) NOT NULL REFERENCES countries, 308 country_code char(2) NOT NULL REFERENCES countries,
309 -- TODO: add relations to stuff provided as enumerations 309 sender varchar NOT NULL, -- "from" element from NtS response
310 sender varchar NOT NULL, -- "from" attribute from DRC
311 language_code varchar NOT NULL REFERENCES language_codes, 310 language_code varchar NOT NULL REFERENCES language_codes,
312 date_issue timestamp with time zone NOT NULL, 311 date_issue timestamp with time zone NOT NULL,
313 reference_code varchar(4) NOT NULL REFERENCES depth_references, 312 reference_code varchar(4) NOT NULL REFERENCES depth_references,
314 water_level double precision NOT NULL, 313 water_level double precision NOT NULL,
315 predicted boolean NOT NULL,
316 is_waterlevel boolean NOT NULL, 314 is_waterlevel boolean NOT NULL,
317 -- XXX: "measure_code" if really only W or Q 315 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
318 -- XXX: Do we need "unit" attribute or can we normalise on import? 316 source_organization varchar NOT NULL, -- "originator" from NtS response
319 value_min double precision, -- XXX: NOT NULL if predicted? 317 staging_done boolean NOT NULL DEFAULT false,
320 value_max double precision, -- XXX: NOT NULL if predicted? 318 UNIQUE (fk_gauge_id, measure_date, staging_done)
321 --- TODO: Add a double range type for checking? 319 )
322 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 320
323 source_organization varchar NOT NULL, -- "originator" 321 CREATE TABLE gauge_predictions (
324 staging_done boolean NOT NULL DEFAULT false 322 fk_gauge_id isrs NOT NULL CONSTRAINT gauge_key REFERENCES gauges,
325 ) 323 measure_date timestamp with time zone NOT NULL,
326 -- Constraints are conditional for gauge_measurements, as they 324 country_code char(2) NOT NULL REFERENCES countries,
327 -- differ between predicted values and measured ones. PG does not 325 sender varchar NOT NULL, -- "from" element from NtS response
328 -- have real conditional unique constraints, but we can use unique 326 language_code varchar NOT NULL REFERENCES language_codes,
329 -- indeces for that. 327 date_issue timestamp with time zone NOT NULL,
330 -- 328 reference_code varchar(4) NOT NULL REFERENCES depth_references,
331 -- So we can have a staged and a non-staged 329 water_level double precision NOT NULL,
332 -- fk_gauge_id/measure_date pairs in measured values. 330 is_waterlevel boolean NOT NULL,
333 CREATE UNIQUE INDEX gm_measured_unique_constraint 331 conf_interval numrange
334 ON gauge_measurements (fk_gauge_id, measure_date, staging_done) 332 CHECK (conf_interval @> CAST(water_level AS numeric)),
335 WHERE NOT predicted 333 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
336 -- And we can have multiple predictions for one point in time 334 source_organization varchar NOT NULL, -- "originator" from NtS response
337 -- (but they are never staged). 335 PRIMARY KEY (fk_gauge_id, measure_date, date_issue)
338 CREATE UNIQUE INDEX gm_predicted_unique_constraint 336 )
339 ON gauge_measurements (fk_gauge_id, measure_date, date_issue)
340 WHERE predicted
341 337
342 CREATE TABLE waterway_axis ( 338 CREATE TABLE waterway_axis (
343 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 339 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
344 wtwaxs geography(LINESTRING, 4326) NOT NULL 340 wtwaxs geography(LINESTRING, 4326) NOT NULL
345 CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))), 341 CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))),