Mercurial > gemma
changeset 4927:6081cbe71b81 fairway-marks-import
Add initial schema update script for fairway marks
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Fri, 14 Feb 2020 15:49:30 +0100 |
| parents | 271616eff8e3 |
| children | be3815792bc4 |
| files | schema/updates/1400/01.add_fairway_marks.sql schema/version.sql |
| diffstat | 2 files changed, 82 insertions(+), 1 deletions(-) [+] |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1400/01.add_fairway_marks.sql Fri Feb 14 15:49:30 2020 +0100 @@ -0,0 +1,81 @@ +CREATE TABLE waterway.fairway_marks ( + geom geography(POINT, 4326) NOT NULL, + datsta varchar, + datend varchar, + persta varchar, + perend varchar, + objnam varchar, + nobjnm varchar, + inform varchar, + ninfom varchar, + scamin int, + picrep varchar, + txtdsc varchar, + sordat varchar, + sorind varchar +); + +CREATE TABLE waterway.fairway_marks_bcnlat ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + condtn int, + bcnshp int, + catlam int +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_bcnlat_distinct_rows + ON waterway.fairway_marks_bcnlat + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, condtn, bcnshp, catlam + ) AS waterway.fairway_marks_bcnlat) + )); + +CREATE TABLE waterway.fairway_marks_bcnlat_dirimps ( + fm_bcnlat_id int REFERENCES waterway.fairway_marks_bcnlat, + dirimp smallint REFERENCES dirimps, + PRIMARY KEY (fm_bcnlat_id, dirimp) +); + + +CREATE TABLE waterway.fairway_marks_boycar ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + conrad int, + marsys int, + boyshp int, + catcam int +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_boycar_distinct_rows + ON waterway.fairway_marks_boycar + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp, catcam + ) AS waterway.fairway_marks_boycar) + )); + +CREATE TABLE waterway.fairway_marks_boylat ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + conrad int, + marsys int, + boyshp int, + catlam int +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_boylat_distinct_rows + ON waterway.fairway_marks_boylat + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp, catlam + ) AS waterway.fairway_marks_boylat) + )); + + +GRANT SELECT on ALL tables in schema waterway TO waterway_user ; +GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway + TO waterway_admin;