# HG changeset patch
# User Andre Heinecke <aheinecke@intevation.de>
# Date 1359391338 -3600
# Node ID 28f992c0f9370335ced61d8a358fab6376c1db8a
# Parent  1f6e544f7a7fccf88bb03fcd5eac3d4468b3d8ce
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen

    This adds the first draft of Hochwasserschutzanlagen tables
    hws_points and hws_lines which replace the old hws table and
    also lookup tables fed_states and hws_kinds.

diff -r 1f6e544f7a7f -r 28f992c0f937 flys-backend/doc/schema/postgresql-drop-spatial.sql
--- a/flys-backend/doc/schema/postgresql-drop-spatial.sql	Mon Jan 28 12:45:41 2013 +0100
+++ b/flys-backend/doc/schema/postgresql-drop-spatial.sql	Mon Jan 28 17:42:18 2013 +0100
@@ -27,8 +27,14 @@
 DROP TABLE catchment;
 DROP SEQUENCE CATCHMENT_ID_SEQ;
 
-DROP TABLE hws;
-DROP SEQUENCE HWS_ID_SEQ;
+--DROP TABLE hws;
+--DROP SEQUENCE HWS_ID_SEQ;
+
+DROP TABLE hws_points;
+DROP SEQUENCE HWS_POINTS_ID_SEQ;
+
+DROP TABLE hws_lines;
+DROP SEQUENCE HWS_LINES_ID_SEQ;
 
 DROP TABLE floodmaps;
 DROP SEQUENCE FLOODMAPS_ID_SEQ;
@@ -42,4 +48,7 @@
 DROP TABLE gauge_location;
 DROP SEQUENCE GAUGE_LOCATION_ID_SEQ;
 
+DROP TABLE fed_states;
+DROP TABLE hws_kinds;
+
 COMMIT;
diff -r 1f6e544f7a7f -r 28f992c0f937 flys-backend/doc/schema/postgresql-spatial.sql
--- a/flys-backend/doc/schema/postgresql-spatial.sql	Mon Jan 28 12:45:41 2013 +0100
+++ b/flys-backend/doc/schema/postgresql-spatial.sql	Mon Jan 28 17:42:18 2013 +0100
@@ -132,20 +132,87 @@
 SELECT AddGeometryColumn('catchment','geom',31467,'POLYGON',2);
 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');
 
+-- Static lookup tables for Hochwasserschutzanlagen
+CREATE TABLE hws_kinds (
+    id int PRIMARY KEY NOT NULL,
+    kind VARCHAR(64) NOT NULL
+);
+INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass');
+INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm');
+INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
 
---Hydrologie/HW-Schutzanlagen/hws.shp
-CREATE SEQUENCE HWS_ID_SEQ;
-CREATE TABLE hws (
+CREATE TABLE fed_states (
     id int PRIMARY KEY NOT NULL,
+    name VARCHAR(23) NOT NULL
+);
+INSERT INTO fed_states (id, name) VALUES (1, 'Bayern');
+INSERT INTO fed_states (id, name) VALUES (2, 'Hessen');
+INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen');
+INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen');
+INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz');
+INSERT INTO fed_states (id, name) VALUES (6, 'Saarland');
+INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein');
+INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg');
+INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern');
+INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen');
+INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg');
+INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt');
+INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen');
+INSERT INTO fed_states (id, name) VALUES (14, 'Berlin');
+INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
+INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
+
+--Hydrologie/HW-Schutzanlagen/*Linien.shp
+CREATE SEQUENCE HWS_LINES_ID_SEQ;
+CREATE TABLE hws_lines (
+    id int PRIMARY KEY NOT NULL,
+    ogr_fid int,
+    kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
+    fed_state_id int REFERENCES fed_states(id),
     river_id int REFERENCES rivers(id),
-    hws_facility VARCHAR(256),
-    type VARCHAR(256),
-    name VARCHAR(64),
-    path     VARCHAR(256)
+    name VARCHAR(256),
+    path VARCHAR(256),
+    offical INT DEFAULT 0,
+    agency VARCHAR(256),
+    range VARCHAR(256),
+    shore_side INT DEFAULT 0,
+    source VARCHAR(256),
+    status_date TIMESTAMP,
+    description VARCHAR(256)
 );
-SELECT AddGeometryColumn('hws','geom',31467,'LINESTRING',2);
-ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
+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');
+
+--Hydrologie/HW-Schutzanlagen/*Punkte.shp
+CREATE SEQUENCE HWS_POINTS_ID_SEQ;
+CREATE TABLE hws_points (
+    id int PRIMARY KEY NOT NULL,
+    ogr_fid int,
+    kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
+    fed_state_id int REFERENCES fed_states(id),
+    river_id int REFERENCES rivers(id),
+    name VARCHAR,
+    path VARCHAR,
+    offical INT DEFAULT 0,
+    agency VARCHAR,
+    range VARCHAR,
+    shore_side INT DEFAULT 0,
+    source VARCHAR,
+    status_date VARCHAR,
+    description VARCHAR,
+    freeboard FLOAT8,
+    dike_km FLOAT8,
+    z FLOAT8,
+    z_target FLOAT8,
+    rated_level FLOAT8
+);
+SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2);
+
+ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ');
 
 --
 --Hydrologie/UeSG