Mercurial > gemma

comparison schema/gemma.sql @ 4006:3dc2694557f1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Speed up time based filtering of gauge measurements and predictions
author Tom Gottfried <tom@intevation.de>
date Thu, 18 Jul 2019 19:57:10 +0200
parents a2921151b193
children 9ab7e1056360
comparison
equal deleted inserted replaced
4005:5c7e797347a3 4006:3dc2694557f1
477 date_info timestamp with time zone NOT NULL, 477 date_info timestamp with time zone NOT NULL,
478 source_organization varchar NOT NULL, -- "originator" from NtS response 478 source_organization varchar NOT NULL, -- "originator" from NtS response
479 staging_done boolean NOT NULL DEFAULT false, 479 staging_done boolean NOT NULL DEFAULT false,
480 UNIQUE (location, measure_date, staging_done) 480 UNIQUE (location, measure_date, staging_done)
481 ) 481 )
482 CREATE INDEX gauge_measurements_measure_date
483 ON waterway.gauge_measurements (measure_date)
482 484
483 CREATE TABLE gauge_predictions ( 485 CREATE TABLE gauge_predictions (
484 location isrs NOT NULL, 486 location isrs NOT NULL,
485 validity tstzrange NOT NULL, 487 validity tstzrange NOT NULL,
486 CONSTRAINT gauge_key 488 CONSTRAINT gauge_key
498 CHECK (conf_interval @> CAST(water_level AS numeric)), 500 CHECK (conf_interval @> CAST(water_level AS numeric)),
499 date_info timestamp with time zone NOT NULL, 501 date_info timestamp with time zone NOT NULL,
500 source_organization varchar NOT NULL, -- "originator" from NtS response 502 source_organization varchar NOT NULL, -- "originator" from NtS response
501 PRIMARY KEY (location, measure_date, date_issue) 503 PRIMARY KEY (location, measure_date, date_issue)
502 ) 504 )
505 CREATE INDEX gauge_predictions_measure_date
506 ON waterway.gauge_predictions (measure_date)
503 507
504 CREATE TABLE waterway_axis ( 508 CREATE TABLE waterway_axis (
505 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 509 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
506 wtwaxs geography(LINESTRING, 4326) NOT NULL 510 wtwaxs geography(LINESTRING, 4326) NOT NULL
507 CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))), 511 CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))),