Mercurial > dive4elements > river
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);