Mercurial > gemma

annotate schema/updates/1406/01.distinguish_bcnlat_hydro_ienc.sql @ 5030:737d7859dd86

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Store fairway dimensions as MultiPolygon This avoids storing a single invalid geometry from the data source as multiple valid geometries with duplicate attribute sets. The previous behaviour was not correctly handled in import tracking, because only the ID of the first item in a set of multiple geometries generated from a single entry from the data source was tracked.
author Tom Gottfried <tom@intevation.de>
date Wed, 18 Mar 2020 18:42:30 +0100
parents 1e47ba2a58f2
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4962
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 CREATE TABLE waterway.fairway_marks_bcnlat_new (
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 colour varchar,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 colpat varchar,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 condtn int,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 bcnshp int,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 catlam int
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 ) INHERITS (waterway.fairway_marks);
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 CREATE TABLE waterway.fairway_marks_bcnlat_hydro (
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 ) INHERITS (waterway.fairway_marks_bcnlat_new);
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 CREATE UNIQUE INDEX fairway_marks_bcnlat_hydro_distinct_rows
4963
1e47ba2a58f2 Fix database migration
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
13 ON waterway.fairway_marks_bcnlat_hydro
4962
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 ((CAST((validity, last_found, geom,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 scamin, picrep, txtdsc, sordat, sorind,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 colour, colpat, condtn, bcnshp, catlam, 0
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 ) AS waterway.fairway_marks_bcnlat_hydro)
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 ));
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 CREATE TABLE waterway.fairway_marks_bcnlat_ienc (
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 ) INHERITS (waterway.fairway_marks_bcnlat_new);
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 CREATE UNIQUE INDEX fairway_marks_bcnlat_ienc_distinct_rows
4963
1e47ba2a58f2 Fix database migration
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
25 ON waterway.fairway_marks_bcnlat_ienc
4962
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 ((CAST((validity, last_found, geom,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 scamin, picrep, txtdsc, sordat, sorind,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 colour, colpat, condtn, bcnshp, catlam, 0
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 ) AS waterway.fairway_marks_bcnlat_ienc)
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 ));
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 -- Assume all features are IENC features, since there is currently no known
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 -- data source for HYDRO features
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 INSERT INTO waterway.fairway_marks_bcnlat_ienc
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 SELECT validity, last_found, geom,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 scamin, picrep, txtdsc, sordat, sorind,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 colour, colpat, condtn, bcnshp, catlam, id
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 FROM waterway.fairway_marks_bcnlat;
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 SELECT setval(
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 pg_get_serial_sequence('waterway.fairway_marks_bcnlat_ienc', 'id'),
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 max(id))
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 FROM waterway.fairway_marks_bcnlat_ienc;
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 -- Let foreign key constraint point to new table
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 ALTER TABLE waterway.fairway_marks_bcnlat_dirimps
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 DROP CONSTRAINT fairway_marks_bcnlat_dirimps_fm_bcnlat_id_fkey,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 ADD FOREIGN KEY (fm_bcnlat_id)
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 REFERENCES waterway.fairway_marks_bcnlat_ienc;
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 -- Finally
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 DROP TABLE waterway.fairway_marks_bcnlat;
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 ALTER TABLE waterway.fairway_marks_bcnlat_new RENAME TO fairway_marks_bcnlat;
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 GRANT SELECT on ALL tables in schema waterway TO waterway_user;
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 TO waterway_admin;