changeset 5385:2da74705c29d

Scheme change: Building table is extended and got a lookup table for kind
author Andre Heinecke <aheinecke@intevation.de>
date Fri, 22 Mar 2013 17:30:10 +0100 (2013-03-22)
parents 5a42ca06b93e
children 6c2751c17869
files flys-backend/doc/schema/oracle-spatial.sql flys-backend/doc/schema/postgresql-spatial.sql
diffstat 2 files changed, 27 insertions(+), 2 deletions(-) [+]
line wrap: on
line diff
--- a/flys-backend/doc/schema/oracle-spatial.sql	Fri Mar 22 17:00:22 2013 +0100
+++ b/flys-backend/doc/schema/oracle-spatial.sql	Fri Mar 22 17:30:10 2013 +0100
@@ -77,13 +77,25 @@
 --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
 
 
--- Geodaesie/Bauwerke/Wehre.shp
+CREATE TABLE building_kinds(
+    id NUMBER PRIMARY KEY NOT NULL,
+    name VARCHAR(64)
+);
+INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige');
+INSERT INTO building_kinds(id, name) VALUES (1, 'Brücken');
+INSERT INTO building_kinds(id, name) VALUES (2, 'Wehre');
+INSERT INTO building_kinds(id, name) VALUES (3, 'Pegel');
+
+-- Geodaesie/Bauwerke
 CREATE SEQUENCE BUILDINGS_ID_SEQ;
 CREATE TABLE buildings(
     OGR_FID NUMBER(38),
     GEOM MDSYS.SDO_GEOMETRY,
     river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
-    name VARCHAR2(255),
+    kind_id NUMBER(38) REFERENCES building_kinds(id) NOT NULL DEFAULT 0,
+    station NUMBER(38,11) NOT NULL,
+    name VARCHAR2(255), -- The layername
+    description VARCHAR(256), -- Name taken from attributes
     path     VARCHAR(256),
     ID NUMBER PRIMARY KEY NOT NULL
 );
--- a/flys-backend/doc/schema/postgresql-spatial.sql	Fri Mar 22 17:00:22 2013 +0100
+++ b/flys-backend/doc/schema/postgresql-spatial.sql	Fri Mar 22 17:30:10 2013 +0100
@@ -56,13 +56,24 @@
 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, 'LINESTRING', 2);
 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');
 
+CREATE TABLE building_kinds(
+    id int PRIMARY KEY NOT NULL,
+    name VARCHAR(64)
+);
+INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige');
+INSERT INTO building_kinds(id, name) VALUES (1, 'Brücken');
+INSERT INTO building_kinds(id, name) VALUES (2, 'Wehre');
+INSERT INTO building_kinds(id, name) VALUES (3, 'Pegel');
 
--- Geodaesie/Bauwerke/Wehre.shp
+-- Geodaesie/Bauwerke
 CREATE SEQUENCE BUILDINGS_ID_SEQ;
 CREATE TABLE buildings (
     id       int PRIMARY KEY NOT NULL,
     river_id int REFERENCES rivers(id) ON DELETE CASCADE,
-    name     VARCHAR(256),
+    description VARCHAR(256) -- Name taken from attributes,
+    name     VARCHAR(256), -- The layername
+    station  FLOAT8,
+    kind_id  int REFERENCES building_kinds(id) NOT NULL DEFAULT 0,
     path     VARCHAR(256)
 );
 SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2);

http://dive4elements.wald.intevation.org