# HG changeset patch # User Andre Heinecke # Date 1361282313 -3600 # Node ID 9ef49f3b030465f55d674cb7212ca496881864ad # Parent 808718c6de976c24054fc9f1e00bf746a34f7c6a Sync Posgres and Oracle spatial schema diff -r 808718c6de97 -r 9ef49f3b0304 flys-backend/doc/schema/oracle-spatial.sql --- a/flys-backend/doc/schema/oracle-spatial.sql Tue Feb 19 14:57:13 2013 +0100 +++ b/flys-backend/doc/schema/oracle-spatial.sql Tue Feb 19 14:58:33 2013 +0100 @@ -1,9 +1,10 @@ +WHENEVER SQLERROR EXIT SQL.ERRORCODE ROLLBACK; -- Geodaesie/Flussachse+km/achse CREATE SEQUENCE RIVER_AXES_ID_SEQ; CREATE TABLE river_axes( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, - river_id NUMBER(38), + river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, kind NUMBER(38) DEFAULT 0 NOT NULL, name VARCHAR(64), path VARCHAR(256), @@ -23,7 +24,7 @@ CREATE TABLE river_axes_km( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, - river_id NUMBER(38), + river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, km NUMBER(6,3), name VARCHAR(64), path VARCHAR(256), @@ -43,7 +44,7 @@ CREATE TABLE cross_section_tracks ( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, - river_id NUMBER(38), + river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, km NUMBER(38,12) NOT NULL, z NUMBER(38,12) DEFAULT 0 NOT NULL, name VARCHAR(64), @@ -64,7 +65,7 @@ CREATE TABLE buildings( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, - river_id NUMBER(38), + river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR2(255), path VARCHAR(256), ID NUMBER PRIMARY KEY NOT NULL @@ -83,7 +84,7 @@ CREATE TABLE fixpoints ( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, - river_id NUMBER(38), + river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, x NUMBER(38,11), y NUMBER(38,11), km NUMBER(38,11) NOT NULL, @@ -106,7 +107,7 @@ CREATE TABLE floodplain( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, - river_id NUMBER(38), + river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(64), path VARCHAR(256), ID NUMBER PRIMARY KEY NOT NULL @@ -126,7 +127,7 @@ CREATE SEQUENCE DEM_ID_SEQ; CREATE TABLE dem ( ID NUMBER PRIMARY KEY NOT NULL, - river_id NUMBER(38), + river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, -- XXX Should we use the ranges table instead? name VARCHAR(64), lower NUMBER(19,5), @@ -135,8 +136,9 @@ year_to VARCHAR(32) NOT NULL, projection VARCHAR(32) NOT NULL, elevation_state VARCHAR(32), + srid NUMBER NOT NULL, format VARCHAR(32), - border_break BOOLEAN NOT NULL DEFAULT FALSE, + border_break NUMBER(1) DEFAULT 0 NOT NULL, resolution VARCHAR(16), description VARCHAR(256), path VARCHAR(256) @@ -153,7 +155,7 @@ CREATE TABLE catchment( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, - river_id NUMBER(38), + river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, area NUMBER(19,5), name VARCHAR2(255), path VARCHAR(256), @@ -168,25 +170,124 @@ / --CREATE INDEX catchment_spatial_idx ON catchment(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=polygon'); +--Static lookup tables for Hochwasserschutzanlagen +CREATE TABLE hws_kinds ( + id NUMBER PRIMARY KEY NOT NULL, + kind VARCHAR(64) NOT NULL +); +INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass'); +INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm'); +INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben'); + +CREATE TABLE fed_states ( + id NUMBER PRIMARY KEY NOT NULL, + name VARCHAR(23) NOT NULL +); +INSERT INTO fed_states (id, name) VALUES (1, 'Bayern'); +INSERT INTO fed_states (id, name) VALUES (2, 'Hessen'); +INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen'); +INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen'); +INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz'); +INSERT INTO fed_states (id, name) VALUES (6, 'Saarland'); +INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein'); +INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg'); +INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern'); +INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen'); +INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg'); +INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt'); +INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen'); +INSERT INTO fed_states (id, name) VALUES (14, 'Berlin'); +INSERT INTO fed_states (id, name) VALUES (15, 'Bremen'); +INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg'); + --Hydrologie/HW-Schutzanlagen/hws.shp -CREATE SEQUENCE HWS_ID_SEQ; -CREATE TABLE hws( - OGR_FID NUMBER(38), +-- HWS-Lines +CREATE SEQUENCE HWS_LINES_ID_SEQ; +CREATE TABLE hws_lines ( + id NUMBER PRIMARY KEY NOT NULL, GEOM MDSYS.SDO_GEOMETRY, - river_id NUMBER(38), - hws_facility VARCHAR2(255), - type VARCHAR2(255), - name VARCHAR(64), - path VARCHAR(256), - ID NUMBER PRIMARY KEY NOT NULL + ogr_fid NUMBER, + kind_id NUMBER(2) DEFAULT 2 REFERENCES hws_kinds(id), + fed_state_id NUMBER(2) REFERENCES fed_states(id), + river_id NUMBER(38) REFERENCES rivers(id), + name VARCHAR(256), + path VARCHAR(256), + official NUMBER DEFAULT 0, + agency VARCHAR(256), + range VARCHAR(256), + shore_side NUMBER DEFAULT 0, + source VARCHAR(256), + status_date TIMESTAMP, + description VARCHAR(256) ); -INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws', '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); -CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW +INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws_lines', '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); +CREATE INDEX hws_lines_spatial_idx ON hws_lines(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); + +CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW BEGIN - SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual; + SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual; END; -/ ---CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); + +-- HWS Points lookup tables +CREATE TABLE sectie_kinds ( + id NUMBER PRIMARY KEY NOT NULL, + name VARCHAR(64) NOT NULL +); +INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt'); +INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch'); +INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank'); +INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich'); + +CREATE TABLE sobek_kinds ( + id NUMBER PRIMARY KEY NOT NULL, + name VARCHAR(64) NOT NULL +); +INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt'); +INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend'); +INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd'); + +CREATE TABLE boundary_kinds ( + id NUMBER PRIMARY KEY NOT NULL, + name VARCHAR(64) NOT NULL +); +INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt'); +INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG'); +INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land'); +INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige'); + + +-- HWS Points +CREATE SEQUENCE HWS_POINTS_ID_SEQ; +CREATE TABLE hws_points ( + id NUMBER PRIMARY KEY NOT NULL, + GEOM MDSYS.SDO_GEOMETRY, + ogr_fid NUMBER, + kind_id NUMBER DEFAULT 2 REFERENCES hws_kinds(id), + fed_state_id NUMBER REFERENCES fed_states(id), + river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, + name VARCHAR(256), + path VARCHAR(256), + official NUMBER DEFAULT 0, + agency VARCHAR(256), + range VARCHAR(256), + shore_side NUMBER DEFAULT 0, + source VARCHAR(256), + status_date VARCHAR(256), + description VARCHAR(256), + freeboard NUMBER(19,5), + dike_km NUMBER(19,5), + z NUMBER(19,5), + z_target NUMBER(19,5), + rated_level NUMBER(19,5) +); + +INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws_points', '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); +CREATE INDEX hws_lines_spatial_idx ON hws_pints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT'); + +CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW + BEGIN + SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual; + END; --Hydrologie/UeSG @@ -194,7 +295,7 @@ CREATE TABLE floodmaps ( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, - river_id NUMBER(38), + river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(255), kind NUMBER(38), diff NUMBER(19,5), @@ -209,7 +310,7 @@ BEGIN SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual; END; -/ + CREATE INDEX floodmaps_spatial_idx ON floodmaps(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON'); @@ -218,7 +319,7 @@ CREATE TABLE hydr_boundaries ( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, - river_id NUMBER(38), + river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(255), kind NUMBER(38), path VARCHAR(256), @@ -229,14 +330,14 @@ BEGIN SELECT HYDR_BOUNDARIES_ID_SEQ.nextval INTO :new.id FROM dual; END; -/ + CREATE INDEX hydr_boundaries_idx ON hydr_boundaries(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; CREATE TABLE hydr_boundaries_poly ( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, - river_id NUMBER(38), + river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(255), kind NUMBER(38), path VARCHAR(256), @@ -247,7 +348,7 @@ BEGIN SELECT HYDR_BOUNDARIES_POLY_ID_SEQ.nextval INTO :new.id FROM dual; END; -/ + CREATE INDEX hydr_boundaries_poly_idx ON hydr_boundaries_poly(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON'); @@ -266,5 +367,5 @@ BEGIN SELECT GAUGE_LOCATION_ID_SEQ.nextval INTO :new.id FROM dual; END; -/ + CREATE INDEX gauge_location_idx ON gauge_location(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');