# HG changeset patch
# User Ingo Weinzierl <ingo.weinzierl@intevation.de>
# Date 1331152040 0
# Node ID e41d03bf98076358881418b97e5d1626ed8c86ba
# Parent  f3b4e32b5db65bb800f906ecd0f0278fd95a3fe3
Adapted the PostgreSQL schema for floodmaps.

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

diff -r f3b4e32b5db6 -r e41d03bf9807 flys-backend/ChangeLog
--- a/flys-backend/ChangeLog	Tue Mar 06 11:54:41 2012 +0000
+++ b/flys-backend/ChangeLog	Wed Mar 07 20:27:20 2012 +0000
@@ -1,3 +1,8 @@
+2012-03-06  Ingo Weinzierl <ingo@intevation.de>
+
+	* doc/schema/postgresql-spatial.sql: Adapted the PostgreSQL schema for
+	  floodmaps.
+
 2012-03-06  Ingo Weinzierl <ingo@intevation.de>
 
 	* doc/schema/postgresql-spatial.sql,
diff -r f3b4e32b5db6 -r e41d03bf9807 flys-backend/doc/schema/postgresql-spatial.sql
--- a/flys-backend/doc/schema/postgresql-spatial.sql	Tue Mar 06 11:54:41 2012 +0000
+++ b/flys-backend/doc/schema/postgresql-spatial.sql	Wed Mar 07 20:27:20 2012 +0000
@@ -123,34 +123,39 @@
 ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
 
 
+--
 --Hydrologie/UeSG
-CREATE SEQUENCE FLOODMAPS_ID_SEQ;
+--
+-- 'kind' can be one of:
+-- 200 = Messung
+-- 111 = Berechnung->Aktuell->BfG
+-- 112 = Berechnung->Aktuell->Land
+-- 121 = Berechnung->Potenziell->BfG
+-- 122 = Berechnung->Potenziell->Land
+--
+CREATE SEQUENCE FLOODMAPS_SEQ;
+CREATE FUNCTION floodmaps_id_func() RETURNS trigger AS $floodmaps_id_func$
+    BEGIN
+        NEW.id := nextval('floodmaps_seq');
+        RETURN NEW;
+    END;
+$floodmaps_id_func$ LANGUAGE plpgsql;
+
 CREATE TABLE floodmaps (
     id         int PRIMARY KEY NOT NULL,
     river_id   int REFERENCES rivers(id),
+    name       varchar(64) NOT NULL,
     kind       int NOT NULL,
-    diff       NUMERIC,
+    diff       real,
     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
+    area       real,
+    perimeter  real
 );
-SELECT AddGeometryColumn('polygons', 'geom', 31466, 'POLYGON', 2);
-ALTER TABLE polygons ALTER COLUMN id SET DEFAULT NEXTVAL('POLYGONS_SEQ');
-
+SELECT AddGeometryColumn('floodmaps', 'geom', 31466, 'MULTIPOLYGON', 2);
+ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom;
+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_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');
-
+CREATE TRIGGER floodmaps_id_trigger BEFORE INSERT OR UPDATE ON floodmaps
+    FOR EACH ROW EXECUTE PROCEDURE floodmaps_id_func();
 END;