changeset 5214:b46df0609276

add lookup tables for floodplain and cross section tracks
author Tom Gottfried <tom@intevation.de>
date Mon, 11 Mar 2013 11:43:44 +0100
parents 76818dc2c937
children c8e79cff622a
files flys-backend/doc/schema/oracle-spatial.sql flys-backend/doc/schema/postgresql-spatial.sql
diffstat 2 files changed, 32 insertions(+), 0 deletions(-) [+]
line wrap: on
line diff
--- a/flys-backend/doc/schema/oracle-spatial.sql	Mon Mar 11 11:20:32 2013 +0100
+++ b/flys-backend/doc/schema/oracle-spatial.sql	Mon Mar 11 11:43:44 2013 +0100
@@ -49,11 +49,19 @@
 
 
 --Geodaesie/Querprofile/QP-Spuren/qps.shp
+CREATE TABLE cross_section_track_kinds(
+    id NUMBER PRIMARY KEY NOT NULL,
+    name VARCHAR(64)
+);
+INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige');
+INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'Aktuell');
+
 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
 CREATE TABLE cross_section_tracks (
     OGR_FID NUMBER(38),
     GEOM MDSYS.SDO_GEOMETRY,
     river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
+    kind_id  NUMBER(38) REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0,
     km       NUMBER(38,12) NOT NULL,
     z        NUMBER(38,12) DEFAULT 0 NOT NULL,
     name     VARCHAR(64),
@@ -112,11 +120,19 @@
 
 
 -- Hydrologie/Hydr. Grenzen/talaue.shp
+CREATE TABLE floodplain_kinds(
+    id NUMBER PRIMARY KEY NOT NULL,
+    name VARCHAR(64)
+);
+INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige');
+INSERT INTO floodplain_kinds(id, name) VALUES (1, 'Aktuell');
+
 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
 CREATE TABLE floodplain(
     OGR_FID NUMBER(38),
     GEOM MDSYS.SDO_GEOMETRY,
     river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
+    kind_id  NUMBER(38) REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0,
     name     VARCHAR(64),
     path     VARCHAR(256),
     ID NUMBER PRIMARY KEY NOT NULL
--- a/flys-backend/doc/schema/postgresql-spatial.sql	Mon Mar 11 11:20:32 2013 +0100
+++ b/flys-backend/doc/schema/postgresql-spatial.sql	Mon Mar 11 11:43:44 2013 +0100
@@ -36,10 +36,18 @@
 
 
 --Geodaesie/Querprofile/QP-Spuren/qps.shp
+CREATE TABLE cross_section_track_kinds(
+    id int PRIMARY KEY NOT NULL,
+    name VARCHAR(64)
+);
+INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige');
+INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'Aktuell');
+
 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
 CREATE TABLE cross_section_tracks (
     id       int PRIMARY KEY NOT NULL,
     river_id int REFERENCES rivers(id) ON DELETE CASCADE,
+    kind_id  int REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0,
     km       FLOAT8 NOT NULL,
     z        FLOAT8 NOT NULL DEFAULT 0,
     name     VARCHAR(64),
@@ -78,10 +86,18 @@
 
 
 -- Hydrologie/Hydr. Grenzen/talaue.shp
+CREATE TABLE floodplain_kinds(
+    id int PRIMARY KEY NOT NULL,
+    name VARCHAR(64)
+);
+INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige');
+INSERT INTO floodplain_kinds(id, name) VALUES (1, 'Aktuell');
+
 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
 CREATE TABLE floodplain (
     id       int PRIMARY KEY NOT NULL,
     river_id int REFERENCES rivers(id) ON DELETE CASCADE,
+    kind_id  int REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0,
     name     VARCHAR(64),
     path     VARCHAR(256)
 );

http://dive4elements.wald.intevation.org