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); |