Mercurial > gemma

comparison schema/gemma.sql @ 3302:ec6163c6687d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
'Historicise' gauges on import Gauge data sets will be updated or a new version will be inserted depending on temporal validity and a timestamp marking the last update in the RIS-Index of a data set. The trigger on date_info is removed because the value is actually an attribut coming from the RIS-Index. Gauge measurements and predictions are associated to the version with matching temporal validity. Bottlenecks are always associated to the actual version of the gauge, although this might change as soon as bottlenecks are 'historicised', too.
author Tom Gottfried <tom@intevation.de>
date Thu, 16 May 2019 18:41:43 +0200
parents 831193935739
children b90b17d0b5a9
comparison
equal deleted inserted replaced
3301:6514b943654e 3302:ec6163c6687d
271 AFTER INSERT OR UPDATE OF area ON waterway_area 271 AFTER INSERT OR UPDATE OF area ON waterway_area
272 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('area') 272 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('area')
273 273
274 274
275 CREATE TABLE gauges ( 275 CREATE TABLE gauges (
276 location isrs PRIMARY KEY CHECK( 276 location isrs CHECK(
277 (location).orc SIMILAR TO 'G[[:digit:]]{4}' 277 (location).orc SIMILAR TO 'G[[:digit:]]{4}'
278 AND CAST(substring((location).orc from 2 for 4) AS int) < 2048), 278 AND CAST(substring((location).orc from 2 for 4) AS int) < 2048),
279 objname varchar NOT NULL, 279 objname varchar NOT NULL,
280 geom geography(POINT, 4326) NOT NULL, 280 geom geography(POINT, 4326) NOT NULL,
281 applicability_from_km int8, 281 applicability_from_km int8,
282 applicability_to_km int8, 282 applicability_to_km int8,
283 validity tstzrange, 283 validity tstzrange,
284 -- pasted text from a more general specification is given
285 -- (a gauge is not a berth!)
286 -- TODO: Ranges need a joint exclusion constaint to prevent overlaps?
287 zero_point double precision NOT NULL, 284 zero_point double precision NOT NULL,
288 geodref varchar, 285 geodref varchar,
289 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 286 date_info timestamp with time zone NOT NULL,
290 source_organization varchar 287 source_organization varchar,
291 ) 288 lastupdate timestamp with time zone NOT NULL,
292 CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges 289 -- entry removed from external data source (RIS-Index)/historicised:
293 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 290 erased boolean NOT NULL DEFAULT false,
291 PRIMARY KEY (location, validity),
292 EXCLUDE USING GiST (isrs_astext(location) WITH =, validity WITH &&)
293 )
294 -- Allow only one non-erased entry per location
295 CREATE UNIQUE INDEX gauges_erased_unique_constraint
296 ON gauges (location)
297 WHERE NOT erased
294 298
295 CREATE TABLE gauges_reference_water_levels ( 299 CREATE TABLE gauges_reference_water_levels (
296 gauge_id isrs NOT NULL REFERENCES gauges, 300 location isrs NOT NULL,
301 validity tstzrange NOT NULL,
302 FOREIGN KEY (location, validity) REFERENCES gauges,
297 -- Omit foreign key constraint to be able to store not NtS-compliant 303 -- Omit foreign key constraint to be able to store not NtS-compliant
298 -- names, too: 304 -- names, too:
299 depth_reference varchar NOT NULL, -- REFERENCES depth_references, 305 depth_reference varchar NOT NULL, -- REFERENCES depth_references,
300 PRIMARY KEY (gauge_id, depth_reference), 306 PRIMARY KEY (location, validity, depth_reference),
301 value int NOT NULL 307 value int NOT NULL
302 ) 308 )
303 309
304 CREATE TABLE gauge_measurements ( 310 CREATE TABLE gauge_measurements (
305 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 311 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
306 fk_gauge_id isrs NOT NULL CONSTRAINT gauge_key REFERENCES gauges, 312 location isrs NOT NULL,
313 validity tstzrange NOT NULL,
314 CONSTRAINT gauge_key
315 FOREIGN KEY (location, validity) REFERENCES gauges,
307 measure_date timestamp with time zone NOT NULL, 316 measure_date timestamp with time zone NOT NULL,
308 country_code char(2) NOT NULL REFERENCES countries, 317 country_code char(2) NOT NULL REFERENCES countries,
309 sender varchar NOT NULL, -- "from" element from NtS response 318 sender varchar NOT NULL, -- "from" element from NtS response
310 language_code varchar NOT NULL REFERENCES language_codes, 319 language_code varchar NOT NULL REFERENCES language_codes,
311 date_issue timestamp with time zone NOT NULL, 320 date_issue timestamp with time zone
321 NOT NULL CHECK (date_issue <@ validity),
312 reference_code varchar(4) NOT NULL REFERENCES depth_references, 322 reference_code varchar(4) NOT NULL REFERENCES depth_references,
313 water_level double precision NOT NULL, 323 water_level double precision NOT NULL,
314 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 324 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
315 source_organization varchar NOT NULL, -- "originator" from NtS response 325 source_organization varchar NOT NULL, -- "originator" from NtS response
316 staging_done boolean NOT NULL DEFAULT false, 326 staging_done boolean NOT NULL DEFAULT false,
317 UNIQUE (fk_gauge_id, measure_date, staging_done) 327 UNIQUE (location, validity, measure_date, staging_done)
318 ) 328 )
319 329
320 CREATE TABLE gauge_predictions ( 330 CREATE TABLE gauge_predictions (
321 fk_gauge_id isrs NOT NULL CONSTRAINT gauge_key REFERENCES gauges, 331 location isrs NOT NULL,
332 validity tstzrange NOT NULL,
333 CONSTRAINT gauge_key
334 FOREIGN KEY (location, validity) REFERENCES gauges,
322 measure_date timestamp with time zone NOT NULL, 335 measure_date timestamp with time zone NOT NULL,
323 country_code char(2) NOT NULL REFERENCES countries, 336 country_code char(2) NOT NULL REFERENCES countries,
324 sender varchar NOT NULL, -- "from" element from NtS response 337 sender varchar NOT NULL, -- "from" element from NtS response
325 language_code varchar NOT NULL REFERENCES language_codes, 338 language_code varchar NOT NULL REFERENCES language_codes,
326 date_issue timestamp with time zone NOT NULL, 339 date_issue timestamp with time zone
340 NOT NULL CHECK (date_issue <@ validity),
327 reference_code varchar(4) NOT NULL REFERENCES depth_references, 341 reference_code varchar(4) NOT NULL REFERENCES depth_references,
328 water_level double precision NOT NULL, 342 water_level double precision NOT NULL,
329 conf_interval numrange 343 conf_interval numrange
330 CHECK (conf_interval @> CAST(water_level AS numeric)), 344 CHECK (conf_interval @> CAST(water_level AS numeric)),
331 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 345 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
332 source_organization varchar NOT NULL, -- "originator" from NtS response 346 source_organization varchar NOT NULL, -- "originator" from NtS response
333 PRIMARY KEY (fk_gauge_id, measure_date, date_issue) 347 PRIMARY KEY (location, validity, measure_date, date_issue)
334 ) 348 )
335 349
336 CREATE TABLE waterway_axis ( 350 CREATE TABLE waterway_axis (
337 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 351 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
338 wtwaxs geography(LINESTRING, 4326) NOT NULL 352 wtwaxs geography(LINESTRING, 4326) NOT NULL
472 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and 486 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and
473 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL) 487 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL)
474 CREATE TABLE bottlenecks ( 488 CREATE TABLE bottlenecks (
475 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 489 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
476 bottleneck_id varchar UNIQUE NOT NULL, 490 bottleneck_id varchar UNIQUE NOT NULL,
477 fk_g_fid isrs NOT NULL REFERENCES gauges, 491 gauge_location isrs NOT NULL,
492 gauge_validity tstzrange NOT NULL,
493 FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges,
478 -- XXX: DRC references "ch. 3.1.1", which does not exist in document. 494 -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
479 objnam varchar, 495 objnam varchar,
480 nobjnm varchar, 496 nobjnm varchar,
481 stretch isrsrange NOT NULL, 497 stretch isrsrange NOT NULL,
482 area geography(MULTIPOLYGON, 4326) NOT NULL 498 area geography(MULTIPOLYGON, 4326) NOT NULL