Mercurial > gemma
comparison schema/updates/1437/01.historicise_fairway_dimensions.sql @ 5144:f11b9b50fcc9
Keep historic data of fairway dimensions
... and accordingly configure the respective layer as WMS-T.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Tue, 31 Mar 2020 18:59:28 +0200 |
| parents | |
| children | 1cb5fca140e2 |
comparison
equal
deleted
inserted
replaced
| 5143:733f7136a30e | 5144:f11b9b50fcc9 |
|---|---|
| 1 ALTER TABLE waterway.fairway_dimensions | |
| 2 ADD validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL) | |
| 3 CHECK (NOT isempty(validity)); | |
| 4 | |
| 5 -- Assume existing entries have been valid since last accepted import | |
| 6 WITH imps AS ( | |
| 7 SELECT changed, CAST(summary AS jsonb)->'fd-area' AS fd_area | |
| 8 FROM import.imports | |
| 9 WHERE kind = 'fd' AND state = 'accepted' | |
| 10 ) | |
| 11 UPDATE waterway.fairway_dimensions fd SET validity = tstzrange( | |
| 12 COALESCE( | |
| 13 (SELECT max(changed) | |
| 14 FROM imps | |
| 15 WHERE fd.id IN( | |
| 16 SELECT id | |
| 17 FROM jsonb_to_recordset(fd_area) | |
| 18 AS fd_area (id bigint, lat numeric, lon numeric))), | |
| 19 current_timestamp), | |
| 20 NULL); | |
| 21 | |
| 22 DROP TRIGGER fairway_dimensions_area_unique ON waterway.fairway_dimensions; | |
| 23 CREATE CONSTRAINT TRIGGER fairway_dimensions_area_unique | |
| 24 AFTER INSERT OR UPDATE OF area, validity, staging_done | |
| 25 ON waterway.fairway_dimensions | |
| 26 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals( | |
| 27 'area', 'validity WITH &&', 'staging_done'); | |
| 28 | |
| 29 CREATE INDEX fairway_dimensions_validity | |
| 30 ON waterway.fairway_dimensions USING GiST (validity); |