Mercurial > dive4elements > river
changeset 4999:929ec3ed7dc2 dami
SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
author | Andre Heinecke <aheinecke@intevation.de> |
---|---|
date | Fri, 15 Feb 2013 12:38:25 +0100 (2013-02-15) |
parents | ab5d8f73a982 |
children | fed74592a2ba |
files | flys-backend/doc/schema/postgresql-drop-spatial.sql flys-backend/doc/schema/postgresql-spatial.sql |
diffstat | 2 files changed, 35 insertions(+), 9 deletions(-) [+] |
line wrap: on
line diff
--- a/flys-backend/doc/schema/postgresql-drop-spatial.sql Fri Feb 15 12:10:44 2013 +0100 +++ b/flys-backend/doc/schema/postgresql-drop-spatial.sql Fri Feb 15 12:38:25 2013 +0100 @@ -27,9 +27,6 @@ DROP TABLE catchment; DROP SEQUENCE CATCHMENT_ID_SEQ; ---DROP TABLE hws; ---DROP SEQUENCE HWS_ID_SEQ; - DROP TABLE hws_points; DROP SEQUENCE HWS_POINTS_ID_SEQ; @@ -50,5 +47,8 @@ DROP TABLE fed_states; DROP TABLE hws_kinds; +DROP TABLE sobek_kinds; +DROP TABLE sectie_kinds; +DROP TABLE boundary_kinds; COMMIT;
--- a/flys-backend/doc/schema/postgresql-spatial.sql Fri Feb 15 12:10:44 2013 +0100 +++ b/flys-backend/doc/schema/postgresql-spatial.sql Fri Feb 15 12:38:25 2013 +0100 @@ -242,14 +242,40 @@ ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text); ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ'); +CREATE TABLE sectie_kinds ( + id int PRIMARY KEY NOT NULL, + name VARCHAR(64) NOT NULL +); +INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt'); +INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch'); +INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank'); +INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich'); + +CREATE TABLE sobek_kinds ( + id int PRIMARY KEY NOT NULL, + name VARCHAR(64) NOT NULL +); +INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt'); +INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend'); +INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd'); + +CREATE TABLE boundary_kinds ( + id int PRIMARY KEY NOT NULL, + name VARCHAR(64) NOT NULL +); +INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt'); +INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG'); +INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land'); +INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige'); + CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; CREATE TABLE hydr_boundaries ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(255), - kind int, -- 1 BfG / 2 Land / 3 Sonstige - sectie int, -- 1 stromführend / 2 stromspeichernd - sobek int, -- 1 Flussschlauch / 2 Uferbank / 3 Überflutungsbereich + kind int REFERENCES boundary_kinds(id), + sectie int REFERENCES sectie_kinds(id), + sobek int REFERENCES sobek_kinds(id), path VARCHAR(256) ); SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'LINESTRING',3); @@ -261,9 +287,9 @@ id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(255), - kind int, -- 1 BfG / 2 Land / 3 Sonstige - sectie int, -- 1 Flussschlauch / 2 Uferbank / 3 Überflutungsbereich - sobek int, -- 1 stromführend / 2 stromspeichernd + kind int REFERENCES boundary_kinds(id), + sectie int REFERENCES sectie_kinds(id), + sobek int REFERENCES sobek_kinds(id), path VARCHAR(256) ); SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'POLYGON',3);