--- a/flys-backend/doc/schema/postgresql-spatial.sql	Fri Sep 02 13:49:46 2011 +0000
+++ b/flys-backend/doc/schema/postgresql-spatial.sql	Mon Sep 12 13:20:05 2011 +0000
@@ -74,7 +74,6 @@
 
 
 -- Geodaesie/Hoehenmodelle/*
--- XXX DGM in englisch DEM, um dies zu vereinheitlichen
 CREATE SEQUENCE DEM_ID_SEQ;
 CREATE TABLE dem (
     id       int PRIMARY KEY NOT NULL,
@@ -88,25 +87,161 @@
 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');
 
 
--- TODO
--- Flussachse+km/km
-
--- TODO
--- Hydrologie/Einzugsgebiete
-
--- TODO
--- Hydrologie/HW-Schutzanlagen
+-- Hydrologie/Einzugsgebiete/EZG.shp
+   -- Hinweise zu ezg_saar.shp wird nicht importiert:
+   -- CLASS: Integer (8.0) KLAEREN: wir die benoetigt?
+   -- AREA: Real (19.8) laesst sich auch durch EZG.shp bestimmen
+   -- PERIMETER: Real (19.8) laesst sich auch durch EZG.shp bestimmen
 
--- TODO
+CREATE SEQUENCE CATCHMENT_ID_SEQ;
+CREATE TABLE catchment (
+    id int PRIMARY KEY NOT NULL,
+    river_id int REFERENCES rivers(id),
+    "area" numeric,
+    "name" VARCHAR(80)
+);
+SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2);
+ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');
+
+-- Hydrologie/Einzugsgebiete/b1-polygon.shp
+   -- Nur fuer saar vorhanden
+   -- IDEE alle Begrenzungspolygone in eine Tabelle.
+CREATE SEQUENCE BOUNDARYPOLYS_ID_SEQ;
+CREATE TABLE boundarypolys (
+    id int PRIMARY KEY NOT NULL,
+    river_id int REFERENCES rivers(id)
+);
+SELECT AddGeometryColumn('boundarypolys','geom',31466,'MULTIPOLYGON',2);
+ALTER TABLE boundarypolys ALTER COLUMN id SET DEFAULT NEXTVAL('BOUNDARYPOLYS_ID_SEQ');
+
+-- Hydrologie/HW-Schutzanlagen
+-- Wird nicht benoetigt, stattdessen verwenden wir
+-- Gewaesser/Saar/Geodaesie/Linien/rohre-und-sperren.shp
+    -- hws.shp beinhaltet die Geometrien von:
+        -- HWS-Lisdorf.shp -- hws_anlage
+        -- HWS-Mettlach.shp -- maßnahme -> hws_anlage
+        -- HWS-Rehlingen.shp -- hw -> hws_anlage
+        -- HWS_Saarburg.shp -- höhe? bauart?
+        -- HWS-Schoden-Rhl-Pf.shp  -- hws_anlage
+        -- HWS_Schoden.shp --höhe? bauart?
+        -- HWS-Serrig.shp --hws_anlage
+-- CREATE SEQUENCE HWS_EZG_ID_SEQ;
+-- CREATE TABLE hws (
+--     id int PRIMARY KEY NOT NULL,
+--     oid int,
+--     river_id int REFERENCES rivers(id),
+--     hws_facility VARCHAR(40),
+--     typ VARCHAR(254)
+-- );
+-- SELECT AddGeometryColumn('hws','geom',31466,'MULTILINESTRING',2);
+-- ALTER TABLE hw ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
+
 -- Hydrologie/Hydr. Grenzen/Linien
+   -- BfG/boeschung_*.shp 
+CREATE SEQUENCE BANKS_ID_SEQ;
+CREATE TABLE banks (
+    id int PRIMARY KEY NOT NULL,
+    river_id int REFERENCES rivers(id)
+);
+SELECT AddGeometryColumn('banks','geom',31466,'MULTILINESTRING',2);
+ALTER TABLE banks ALTER COLUMN id SET DEFAULT NEXTVAL('BANKS_ID_SEQ');
 
--- TODO
+   -- BfG/hauptoeff_*.shp
+CREATE SEQUENCE MAINSPANS_ID_SEQ;
+CREATE TABLE mainspans(
+    id int PRIMARY KEY NOT NULL,
+    river_id int REFERENCES rivers(id)
+);
+SELECT AddGeometryColumn('mainspans','geom',31466,'MULTILINESTRING',2);
+ALTER TABLE mainspans ALTER COLUMN id SET DEFAULT NEXTVAL('MAINSPANS_ID_SEQ');
+
+   -- BfG/MNQ-*.shp
+CREATE SEQUENCE MNQ_ID_SEQ;
+CREATE TABLE mnq (gid serial PRIMARY KEY,
+    id int PRIMARY KEY NOT NULL,
+    river_id int REFERENCES rivers(id),
+    haltung varchar(16)
+);
+SELECT AddGeometryColumn('mnq', 'the_geom',31466,'MULTIPOLYGON',2);
+ALTER TABLE mnq ALTER COLUMN id SET DEFAULT NEXTVAL('MNQ_ID_SEQ');
+
+   -- BfG/modellgrenze*.shp
+CREATE SEQUENCE MODELBOUNDARY_ID_SEQ;
+CREATE TABLE modelboundary (
+    id int PRIMARY KEY NOT NULL,
+    river_id int REFERENCES rivers(id)
+);
+SELECT AddGeometryColumn('modelboundary','geom',31466,'MULTILINESTRING',2);
+ALTER TABLE modelboundary ALTER COLUMN id SET DEFAULT NEXTVAL('MODELBOUNDARY_ID_SEQ');
+
+   -- TODO: Klaeren ob benoetigt, da einzel Geometrien in Tabelle vorland.
+   -- BfG/saar-sld-vorland.shp
+
+   -- BfG/uferlinie.shp
+CREATE SEQUENCE SHORELINE_ID_SEQ;
+CREATE TABLE shoreline(
+    id int PRIMARY KEY NOT NULL,
+    river_id int REFERENCES rivers(id)
+);
+SELECT AddGeometryColumn('shoreline','geom',31466,'MULTILINESTRING',2);
+ALTER TABLE shoreline ALTER COLUMN id SET DEFAULT NEXTVAL('SHORELINE_ID_SEQ');
+
+   -- BfG/vorland_*.shp
+CREATE SEQUENCE FORELAND_ID_SEQ;
+CREATE TABLE foreland(
+    id int PRIMARY KEY NOT NULL,
+    river_id int REFERENCES rivers(id)
+);
+SELECT AddGeometryColumn('foreland','geom',31466,'MULTILINESTRING',2);
+ALTER TABLE foreland ALTER COLUMN id SET DEFAULT NEXTVAL('FORELANDS_ID_SEQ');
+
+
 -- Hydrologie/Streckendaten
+    -- pegellage_saar.shp
+CREATE SEQUENCE LEVELPOSITION_ID_SEQ;
+CREATE TABLE levelposition (
+    id int PRIMARY KEY NOT NULL,
+    river_id int REFERENCES rivers(id),
+    x numeric(10,0),
+    y numeric(10,0),
+    name varchar(254)
+);
+SELECT AddGeometryColumn('levelposition','geom','31466','POINT',2);
+ALTER TABLE levelposition ALTER COLUMN id SET DEFAULT NEXTVAL('LEVELPOSITION_ID_SEQ');
 
--- TODO
 -- Hydrologie/UeSG/Berechnung
+   -- Berechnung/Aktuell/BfG
+CREATE SEQUENCE COMPUTATIONS_BFG_ID_SEQ;
+CREATE TABLE computations_bfg (
+    id int PRIMARY KEY NOT NULL,
+    river_id int REFERENCES rivers(id),
+    section varchar(254),
+    area float8,
+    perimeter float8
+);
+SELECT AddGeometryColumn('computations_bfg','geom','31466','MULTIPOLYGON',2);
+ALTER TABLE computations_bfg ALTER COLUMN id SET DEFAULT NEXTVAL('COMPUTATIONS_BFG_ID_SEQ');
 
--- TODO
+   -- Berechnung/Aktuell/Land
+CREATE SEQUENCE COMPUTATIONS_COUNTRY_ID_SEQ;
+CREATE TABLE computations_country(
+    id int PRIMARY KEY NOT NULL,
+    river_id int REFERENCES rivers(id),
+    text varchar(254)
+);
+SELECT AddGeometryColumn('computations_contry','geom','31466','MULTILINESTRING',2);
+ALTER TABLE computations_country ALTER COLUMN id SET DEFAULT NEXTVAL('COMPUTATIONS_COUNTRY_ID_SEQ');
+
+
 -- Hydrologie/UeSG/Messung
+CREATE SEQUENCE MEASUREMENTS_ID_SEQ;
+CREATE TABLE measurements (
+    id int PRIMARY KEY NOT NULL,
+    river_id int REFERENCES rivers(id),
+    year varchar(254),
+    oid varchar(40)
+);
+SELECT AddGeometryColumn('measurement','geom','31466','MULTILINESTRING',2);
+ALTER TABLE measurements ALTER COLUMN id SET DEFAULT NEXTVAL('MEASUREMENTS_ID_SEQ');
 
 END;