--- a/flys-backend/doc/schema/postgresql-migrate-dami.sql	Tue Feb 19 14:58:33 2013 +0100
+++ b/flys-backend/doc/schema/postgresql-migrate-dami.sql	Tue Feb 19 14:59:44 2013 +0100
@@ -1,4 +1,5 @@
 DROP table hws;
+DROP sequence HWS_ID_SEQ;
 -- Static lookup tables for Hochwasserschutzanlagen
 CREATE TABLE hws_kinds (
     id int PRIMARY KEY NOT NULL,
@@ -29,6 +30,32 @@
 INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
 
+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');
+
 --Hydrologie/HW-Schutzanlagen/*Linien.shp
 CREATE SEQUENCE HWS_LINES_ID_SEQ;
 CREATE TABLE hws_lines (
@@ -48,8 +75,6 @@
     description VARCHAR(256)
 );
 SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'LINESTRING', 2);
-SELECT AddGeometryColumn('hws_lines', 'geom_target', 31467, 'LINESTRING', 2); -- ?
-SELECT AddGeometryColumn('hws_lines', 'geom_rated_level', 31467, 'LINESTRING', 2); -- ?
 -- TODO: dike_km_from dike_km_to, are they geometries?
 
 ALTER TABLE hws_lines ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_LINES_ID_SEQ');
@@ -81,8 +106,12 @@
 
 ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ');
 
-ALTER TABLE hydr_boundaries_poly ADD column sectie int;
-ALTER TABLE hydr_boundaries_poly ADD column sobek int;
-ALTER TABLE hydr_boundaries ADD column sectie int;
-ALTER TABLE hydr_boundaries ADD column sobek int;
+ALTER TABLE hydr_boundaries_poly ADD COLUMN sectie INT REFERENCES sectie_kinds(id);
+ALTER TABLE hydr_boundaries_poly ADD COLUMN sobek INT REFERENCES sobek_kinds(id);
+ALTER TABLE hydr_boundaries ADD COLUMN sectie INT REFERENCES sectie_kinds(id);
+ALTER TABLE hydr_boundaries ADD COLUMN sobek INT REFERENCES sobek_kinds(id);
+ALTER TABLE hydr_boundaries ADD FOREIGN KEY (kind) REFERENCES boundary_kinds(id);
+ALTER TABLE dem ADD COLUMN srid INT NOT NULL;
 
+COMMIT;
+