# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1363196368 -3600
# Node ID 170f514a4f290cc1d30d6918dc2821e2148255c9
# Parent  911f6291775371157224e9b22d1da79522517e12
SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names

diff -r 911f62917753 -r 170f514a4f29 flys-backend/contrib/shpimporter/uesg.py
--- a/flys-backend/contrib/shpimporter/uesg.py	Wed Mar 13 17:06:59 2013 +0100
+++ b/flys-backend/contrib/shpimporter/uesg.py	Wed Mar 13 18:39:28 2013 +0100
@@ -7,7 +7,7 @@
 
 
 TABLE_NAME="floodmaps"
-PATH="Hydrologie/UeSG/Berechnung"
+PATH="Hydrologie/UeSG"
 NAME="UESG"
 
 
@@ -42,7 +42,7 @@
             else:
                 kind = kind + 20
 
-            if path.find("Land") > 0:
+            if path.find("Bundesl") > 0:
                 kind = kind + 2
             else:
                 kind = kind + 1
diff -r 911f62917753 -r 170f514a4f29 flys-backend/doc/schema/oracle-spatial.sql
--- a/flys-backend/doc/schema/oracle-spatial.sql	Wed Mar 13 17:06:59 2013 +0100
+++ b/flys-backend/doc/schema/oracle-spatial.sql	Wed Mar 13 18:39:28 2013 +0100
@@ -286,18 +286,29 @@
 /
 
 --Hydrologie/UeSG
+CREATE TABLE floodmap_kinds (
+    id 	     NUMBER PRIMARY KEY NOT NULL,
+    name     varchar(64) NOT NULL
+);
+INSERT INTO floodmap_kinds VALUES (200, 'Messung');
+INSERT INTO floodmap_kinds VALUES (111, 'Berechnung-Aktuell-BfG');
+INSERT INTO floodmap_kinds VALUES (112, 'Berechnung-Aktuell-Bundesländer');
+INSERT INTO floodmap_kinds VALUES (121, 'Berechnung-Potenziell-BfG');
+INSERT INTO floodmap_kinds VALUES (122, 'Berechnung-Potenziell-Bundesländer');
+
 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
 CREATE TABLE floodmaps (
     OGR_FID NUMBER(38),
     GEOM MDSYS.SDO_GEOMETRY,
     river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
     name VARCHAR(255),
-    kind NUMBER(38),
+    kind NUMBER NOT NULL REFERENCES floodmap_kinds(id),
     diff NUMBER(19,5),
     count NUMBER(38),
     area NUMBER(19,5),
     perimeter NUMBER(19,5),
     path     VARCHAR(256),
+    source   varchar(64),
     id NUMBER PRIMARY KEY NOT NULL
 );
 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodmaps', '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);
diff -r 911f62917753 -r 170f514a4f29 flys-backend/doc/schema/postgresql-minfo.sql
--- a/flys-backend/doc/schema/postgresql-minfo.sql	Wed Mar 13 17:06:59 2013 +0100
+++ b/flys-backend/doc/schema/postgresql-minfo.sql	Wed Mar 13 18:39:28 2013 +0100
@@ -27,7 +27,7 @@
     name        VARCHAR(64) NOT NULL,
     PRIMARY KEY(id)
 );
-INSERT INTO bed_height_type VALUES (1, 'Querprofil');
+INSERT INTO bed_height_type VALUES (1, 'Querprofile');
 INSERT INTO bed_height_type VALUES (2, 'Flächenpeilung');
 INSERT INTO bed_height_type VALUES (3, 'Flächen- u. Querprofilpeilungen');
 INSERT INTO bed_height_type VALUES (4, 'DGM');
diff -r 911f62917753 -r 170f514a4f29 flys-backend/doc/schema/postgresql-spatial.sql
--- a/flys-backend/doc/schema/postgresql-spatial.sql	Wed Mar 13 17:06:59 2013 +0100
+++ b/flys-backend/doc/schema/postgresql-spatial.sql	Wed Mar 13 18:39:28 2013 +0100
@@ -208,25 +208,28 @@
 
 --
 --Hydrologie/UeSG
---
--- 'kind' can be one of:
--- 200 = Messung
--- 111 = Berechnung->Aktuell->BfG
--- 112 = Berechnung->Aktuell->Land
--- 121 = Berechnung->Potenziell->BfG
--- 122 = Berechnung->Potenziell->Land
---
+CREATE TABLE floodmap_kinds (
+    id 	     int PRIMARY KEY NOT NULL,
+    name     varchar(64) NOT NULL
+);
+INSERT INTO floodmap_kinds VALUES (200, 'Messung');
+INSERT INTO floodmap_kinds VALUES (111, 'Berechnung-Aktuell-BfG');
+INSERT INTO floodmap_kinds VALUES (112, 'Berechnung-Aktuell-Bundesländer');
+INSERT INTO floodmap_kinds VALUES (121, 'Berechnung-Potenziell-BfG');
+INSERT INTO floodmap_kinds VALUES (122, 'Berechnung-Potenziell-Bundesländer');
+
 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
 CREATE TABLE floodmaps (
     id         int PRIMARY KEY NOT NULL,
     river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
     name       varchar(64) NOT NULL,
-    kind       int NOT NULL,
+    kind       int NOT NULL REFERENCES floodmap_kinds(id),
     diff       FLOAT8,
     count      int,
     area       FLOAT8,
     perimeter  FLOAT8,
-    path     VARCHAR(256)
+    path     VARCHAR(256),
+    source   varchar(64)
 );
 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2);
 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom;