Mercurial > gemma
view schema/updates/1400/01.add_fairway_marks.sql @ 5007:799e8248de8d
First step to move geometry creation for WFS features to loader.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 11 Mar 2020 14:23:52 +0100 |
parents | 6081cbe71b81 |
children |
line wrap: on
line source
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;