--- a/flys-backend/doc/schema/postgresql-spatial.sql	Thu Feb 28 11:06:20 2013 +0100
+++ b/flys-backend/doc/schema/postgresql-spatial.sql	Thu Feb 28 11:48:17 2013 +0100
@@ -4,7 +4,7 @@
 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
 CREATE TABLE river_axes (
     id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
     kind     int             NOT NULL DEFAULT 0,
     name     VARCHAR(64),
     path     VARCHAR(256)
@@ -18,8 +18,8 @@
 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
 CREATE TABLE river_axes_km (
     id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
-    km       NUMERIC NOT NULL,
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
+    km       FLOAT8 NOT NULL,
     name     VARCHAR(64),
     path     VARCHAR(256)
 );
@@ -31,9 +31,9 @@
 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
 CREATE TABLE cross_section_tracks (
     id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
-    km       NUMERIC NOT NULL,
-    z        NUMERIC NOT NULL DEFAULT 0,
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
+    km       FLOAT8 NOT NULL,
+    z        FLOAT8 NOT NULL DEFAULT 0,
     name     VARCHAR(64),
     path     VARCHAR(256)
 );
@@ -41,28 +41,11 @@
 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');
 
 
--- Geodaesie/Linien/rohre-und-spreen
-CREATE SEQUENCE LINES_ID_SEQ;
-CREATE TABLE lines (
-    id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
-    kind     VARCHAR(16) NOT NULL,
-    z        NUMERIC DEFAULT 0,
-    name     VARCHAR(64),
-    path     VARCHAR(256)
-);
-SELECT AddGeometryColumn('lines', 'geom', 31467, 'LINESTRING', 3);
-ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ');
--- 'kind':
--- 0: ROHR1
--- 1: DAMM
-
-
 -- Geodaesie/Bauwerke/Wehre.shp
 CREATE SEQUENCE BUILDINGS_ID_SEQ;
 CREATE TABLE buildings (
     id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
     name     VARCHAR(256),
     path     VARCHAR(256)
 );
@@ -74,10 +57,10 @@
 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
 CREATE TABLE fixpoints (
     id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
-    x        int,
-    y        int,
-    km       NUMERIC NOT NULL,
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
+    x        FLOAT8,
+    y        FLOAT8,
+    km       FLOAT8 NOT NULL,
     HPGP     VARCHAR(2),
     name     VARCHAR(64),
     path     VARCHAR(256)
@@ -90,7 +73,7 @@
 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
 CREATE TABLE floodplain (
     id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
     name     VARCHAR(64),
     path     VARCHAR(256)
 );
@@ -102,50 +85,104 @@
 CREATE SEQUENCE DEM_ID_SEQ;
 CREATE TABLE dem (
     id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
+    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
     -- XXX Should we use the ranges table instead?
     name            VARCHAR(64),
-    lower           NUMERIC,
-    upper           NUMERIC,
-    year_from       VARCHAR(32) NOT NULL,
-    year_to         VARCHAR(32) NOT NULL,
-    projection      VARCHAR(32) NOT NULL,
+    lower           FLOAT8,
+    upper           FLOAT8,
+    year_from       VARCHAR(32),
+    year_to         VARCHAR(32),
+    projection      VARCHAR(32),
+    srid	    int NOT NULL,
     elevation_state VARCHAR(32),
     format          VARCHAR(32),
     border_break    BOOLEAN NOT NULL DEFAULT FALSE,
     resolution      VARCHAR(16),
     description     VARCHAR(256),
-    path            VARCHAR(256)
+    path            VARCHAR(256) NOT NULL
 );
 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');
 
 
--- Hydrologie/Einzugsgebiete/EZG.shp
-CREATE SEQUENCE CATCHMENT_ID_SEQ;
-CREATE TABLE catchment (
+-- Static lookup tables for Hochwasserschutzanlagen
+CREATE TABLE hws_kinds (
     id int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id),
-    area NUMERIC,
-    name VARCHAR(256),
-    path     VARCHAR(256)
+    kind VARCHAR(64) NOT NULL
 );
-SELECT AddGeometryColumn('catchment','geom',31467,'POLYGON',2);
-ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');
-
+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,
-    river_id int REFERENCES rivers(id),
-    hws_facility VARCHAR(256),
-    type VARCHAR(256),
-    name VARCHAR(64),
-    path     VARCHAR(256)
+    name VARCHAR(23) NOT NULL
 );
-SELECT AddGeometryColumn('hws','geom',31467,'LINESTRING',2);
-ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
+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) ON DELETE CASCADE,
+    name VARCHAR(256),
+    path VARCHAR(256),
+    official 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_lines', 'geom', 31467, 'LINESTRING', 3);
+-- 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) ON DELETE CASCADE,
+    name VARCHAR,
+    path VARCHAR,
+    official 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
@@ -160,13 +197,13 @@
 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
 CREATE TABLE floodmaps (
     id         int PRIMARY KEY NOT NULL,
-    river_id   int REFERENCES rivers(id),
+    river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
     name       varchar(64) NOT NULL,
     kind       int NOT NULL,
-    diff       real,
+    diff       FLOAT8,
     count      int,
-    area       real,
-    perimeter  real,
+    area       FLOAT8,
+    perimeter  FLOAT8,
     path     VARCHAR(256)
 );
 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2);
@@ -174,13 +211,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),
+    river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
     name       VARCHAR(255),
-    kind       int,
+    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);
@@ -190,9 +254,11 @@
 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
 CREATE TABLE hydr_boundaries_poly (
     id         int PRIMARY KEY NOT NULL,
-    river_id   int REFERENCES rivers(id),
+    river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
     name       VARCHAR(255),
-    kind       int,
+    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);
@@ -202,7 +268,7 @@
 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
 CREATE TABLE gauge_location (
     id         int PRIMARY KEY NOT NULL,
-    river_id   int REFERENCES rivers(id),
+    river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
     name       VARCHAR(255),
     path       VARCHAR(256)
 );