diff flys-backend/doc/schema/postgresql-spatial.sql @ 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 908848b74d7e
children 170f514a4f29
line wrap: on
line diff
--- 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