Mercurial > gemma

comparison schema/gemma.sql @ 2865:cf295096234e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
gemma.sql fixed: Index creation extracted and put after schmema creation
author Thomas Junk <thomas.junk@intevation.de>
date Fri, 29 Mar 2019 12:02:33 +0100
parents e2d362589ce6
children 1fa2c9022362
comparison
equal deleted inserted replaced
2863:e2d362589ce6 2865:cf295096234e
337 --- TODO: Add a double range type for checking? 337 --- TODO: Add a double range type for checking?
338 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 338 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
339 source_organization varchar NOT NULL, -- "originator" 339 source_organization varchar NOT NULL, -- "originator"
340 staging_done boolean NOT NULL DEFAULT false 340 staging_done boolean NOT NULL DEFAULT false
341 ) 341 )
342 -- Constraints are conditional for gauge_measurements, as they
343 -- differ between predicted values and measured ones. PG does not
344 -- have real conditional unique constraints, but we can use unique
345 -- indeces for that.
346 --
347 -- So we can have a staged and a non-staged
348 -- fk_gauge_id/measure_date pairs in measured values.
349 CREATE UNIQUE INDEX gm_measured_unique_constraint
350 ON gauge_measurements (fk_gauge_id, measure_date, staging_done)
351 WHERE NOT predicted;
352 -- And we can have multiple predictions for one point in time
353 -- (but they are never staged).
354 CREATE UNIQUE INDEX gm_predicted_unique_constraint
355 ON gauge_measurements (fk_gauge_id, measure_date, date_issue)
356 WHERE predicted;
357 342
358 CREATE TABLE waterway_axis ( 343 CREATE TABLE waterway_axis (
359 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 344 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
360 wtwaxs geography(LINESTRING, 4326) NOT NULL 345 wtwaxs geography(LINESTRING, 4326) NOT NULL
361 CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))), 346 CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))),
707 FROM gauge_measurements_waterlevel 692 FROM gauge_measurements_waterlevel
708 WHERE b.fk_g_fid=fk_gauge_id) gmw ON TRUE 693 WHERE b.fk_g_fid=fk_gauge_id) gmw ON TRUE
709 GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level; 694 GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level;
710 ; 695 ;
711 696
697 -- Constraints are conditional for gauge_measurements, as they
698 -- differ between predicted values and measured ones. PG does not
699 -- have real conditional unique constraints, but we can use unique
700 -- indeces for that.
701 --
702 -- So we can have a staged and a non-staged
703 -- fk_gauge_id/measure_date pairs in measured values.
704 CREATE UNIQUE INDEX gm_measured_unique_constraint
705 ON waterway.gauge_measurements (fk_gauge_id, measure_date, staging_done)
706 WHERE NOT predicted;
707 -- And we can have multiple predictions for one point in time
708 -- (but they are never staged).
709 CREATE UNIQUE INDEX gm_predicted_unique_constraint
710 ON waterway.gauge_measurements (fk_gauge_id, measure_date, date_issue)
711 WHERE predicted;
712
712 -- Configure primary keys for geoserver views 713 -- Configure primary keys for geoserver views
713 INSERT INTO waterway.gt_pk_metadata VALUES 714 INSERT INTO waterway.gt_pk_metadata VALUES
714 ('waterway', 'gauges_geoserver', 'location'), 715 ('waterway', 'gauges_geoserver', 'location'),
715 ('waterway', 'distance_marks_geoserver', 'location_code'), 716 ('waterway', 'distance_marks_geoserver', 'location_code'),
716 ('waterway', 'distance_marks_ashore_geoserver', 'id'), 717 ('waterway', 'distance_marks_ashore_geoserver', 'id'),