Mercurial > gemma

annotate schema/updates/1437/01.historicise_fairway_dimensions.sql @ 5144:f11b9b50fcc9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5144
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 ALTER TABLE waterway.fairway_dimensions
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 ADD validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL)
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 CHECK (NOT isempty(validity));
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 -- Assume existing entries have been valid since last accepted import
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 WITH imps AS (
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 SELECT changed, CAST(summary AS jsonb)->'fd-area' AS fd_area
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 FROM import.imports
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 WHERE kind = 'fd' AND state = 'accepted'
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 )
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 UPDATE waterway.fairway_dimensions fd SET validity = tstzrange(
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 COALESCE(
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 (SELECT max(changed)
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 FROM imps
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 WHERE fd.id IN(
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 SELECT id
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 FROM jsonb_to_recordset(fd_area)
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 AS fd_area (id bigint, lat numeric, lon numeric))),
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 current_timestamp),
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 NULL);
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 DROP TRIGGER fairway_dimensions_area_unique ON waterway.fairway_dimensions;
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 CREATE CONSTRAINT TRIGGER fairway_dimensions_area_unique
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 AFTER INSERT OR UPDATE OF area, validity, staging_done
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 ON waterway.fairway_dimensions
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals(
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 'area', 'validity WITH &&', 'staging_done');
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 CREATE INDEX fairway_dimensions_validity
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 ON waterway.fairway_dimensions USING GiST (validity);