# HG changeset patch
# User Ingo Weinzierl <ingo.weinzierl@intevation.de>
# Date 1331034881 0
# Node ID f3b4e32b5db65bb800f906ecd0f0278fd95a3fe3
# Parent  c41bb1293acb3f372eae325f54779660bb85cff2
Improved the db schema for storing existing floodmaps.

flys-backend/trunk@4125 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r c41bb1293acb -r f3b4e32b5db6 flys-backend/ChangeLog
--- a/flys-backend/ChangeLog	Tue Mar 06 07:45:30 2012 +0000
+++ b/flys-backend/ChangeLog	Tue Mar 06 11:54:41 2012 +0000
@@ -1,3 +1,9 @@
+2012-03-06  Ingo Weinzierl <ingo@intevation.de>
+
+	* doc/schema/postgresql-spatial.sql,
+	  doc/schema/oracle-spatial.sql: Added new relations for existing
+	  floodmaps (currently tested for PostgreSQL only!).
+
 2012-03-06  Ingo Weinzierl <ingo@intevation.de>
 
 	* src/main/java/de/intevation/flys/model/Wst.java: Adapted method call of
diff -r c41bb1293acb -r f3b4e32b5db6 flys-backend/doc/schema/oracle-spatial.sql
--- a/flys-backend/doc/schema/oracle-spatial.sql	Tue Mar 06 07:45:30 2012 +0000
+++ b/flys-backend/doc/schema/oracle-spatial.sql	Tue Mar 06 11:54:41 2012 +0000
@@ -190,3 +190,48 @@
     END;
 /
 --CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
+
+
+--Hydrologie/UeSG
+CREATE SEQUENCE FLOODMAPS_ID_SEQ;
+CREATE TABLE floodmaps (
+    id NUMBER PRIMARY KEY NOT NULL,
+    river_id NUMBER(38),
+    kind NUMBER(38),
+    diff NUMBER(19,5),
+    count NUMBER(38),
+    area NUMBER(19,5),
+    perimeter NUMBER(19,5)
+);
+CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW
+    BEGIN
+        SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+
+CREATE SEQUENCE FLOODMAP_GEOMS_SEQ;
+CREATE TABLE floodmap_geoms (
+    id           NUMBER(38) PRIMARY KEY NOT NULL,
+    floodmaps_id NUMBER(38) NOT NULL,
+    polygons_id  NUMBER(38) NOT NULL
+);
+CREATE OR REPLACE TRIGGER floodmap_geoms_trigger BEFORE INSERT ON floodmap_geoms FOR each ROW
+    BEGIN
+        SELECT FLOODMAP_GEOMS_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+
+
+CREATE SEQUENCE POLYGONS_SEQ;
+CREATE TABLE polygons (
+    OGR_FID NUMBER(38),
+    GEOM MDSYS.SDO_GEOMETRY,
+    id NUMBER(38) PRIMARY KEY NOT NULL
+);
+INSERT INTO USER_SDO_GEOM_METADATA VALUES ('polygons', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
+CREATE OR REPLACE TRIGGER polygons_trigger BEFORE INSERT ON polygons FOR each ROW
+    BEGIN
+        SELECT POLYGONS_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
diff -r c41bb1293acb -r f3b4e32b5db6 flys-backend/doc/schema/postgresql-spatial.sql
--- a/flys-backend/doc/schema/postgresql-spatial.sql	Tue Mar 06 07:45:30 2012 +0000
+++ b/flys-backend/doc/schema/postgresql-spatial.sql	Tue Mar 06 11:54:41 2012 +0000
@@ -123,4 +123,34 @@
 ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
 
 
+--Hydrologie/UeSG
+CREATE SEQUENCE FLOODMAPS_ID_SEQ;
+CREATE TABLE floodmaps (
+    id         int PRIMARY KEY NOT NULL,
+    river_id   int REFERENCES rivers(id),
+    kind       int NOT NULL,
+    diff       NUMERIC,
+    count      int,
+    area       NUMERIC,
+    perimeter  NUMERIC
+);
+ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ');
+
+
+CREATE SEQUENCE POLYGONS_SEQ;
+CREATE TABLE polygons (
+    id int PRIMARY KEY NOT NULL
+);
+SELECT AddGeometryColumn('polygons', 'geom', 31466, 'POLYGON', 2);
+ALTER TABLE polygons ALTER COLUMN id SET DEFAULT NEXTVAL('POLYGONS_SEQ');
+
+
+CREATE SEQUENCE FLOODMAP_GEOMS_SEQ;
+CREATE TABLE floodmap_geoms (
+    id           int PRIMARY KEY NOT NULL,
+    floodmaps_id int NOT NULL REFERENCES floodmaps(id),
+    polygons_id  int NOT NULL REFERENCES polygons(id)
+);
+ALTER TABLE floodmap_geoms ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAP_GEOMS_SEQ');
+
 END;