# HG changeset patch
# User Andre Heinecke <aheinecke@intevation.de>
# Date 1359124985 -3600
# Node ID 186dd186d5bd606090bc3ed38aa54d02ee4c4446
# Parent  e9880b224c2fc2593b2dd2300ec2617ec82554fd
Importer: Add new HWS schema as a patch for now.
This might be subject to further change so its only a patch for now

diff -r e9880b224c2f -r 186dd186d5bd flys-backend/contrib/hws_schema.diff
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/flys-backend/contrib/hws_schema.diff	Fri Jan 25 15:43:05 2013 +0100
@@ -0,0 +1,128 @@
+diff -r 0bb0525bca52 flys-backend/doc/schema/postgresql-drop-spatial.sql
+--- a/flys-backend/doc/schema/postgresql-drop-spatial.sql	Fri Jan 25 15:38:34 2013 +0100
++++ b/flys-backend/doc/schema/postgresql-drop-spatial.sql	Fri Jan 25 15:42:05 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 0bb0525bca52 flys-backend/doc/schema/postgresql-spatial.sql
+--- a/flys-backend/doc/schema/postgresql-spatial.sql	Fri Jan 25 15:38:34 2013 +0100
++++ b/flys-backend/doc/schema/postgresql-spatial.sql	Fri Jan 25 15:42:05 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