tom@6632: --WHENEVER SQLERROR EXIT; aheinecke@5146: aheinecke@5146: CREATE TABLE axis_kinds( aheinecke@5146: id NUMBER PRIMARY KEY NOT NULL, aheinecke@5146: name VARCHAR(64) aheinecke@5146: ); teichmann@5162: INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt'); aheinecke@5495: INSERT INTO axis_kinds(id, name) VALUES (1, 'aktuelle Achse'); teichmann@5162: INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige'); aheinecke@5146: bjoern@2339: -- Geodaesie/Flussachse+km/achse bjoern@2339: CREATE SEQUENCE RIVER_AXES_ID_SEQ; bjoern@2339: CREATE TABLE river_axes( bjoern@2339: OGR_FID NUMBER(38), bjoern@2339: GEOM MDSYS.SDO_GEOMETRY, aheinecke@5025: river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, tom@6632: kind_id NUMBER(38) DEFAULT 0 NOT NULL REFERENCES axis_kinds(id), ingo@2861: name VARCHAR(64), ingo@3677: path VARCHAR(256), bjoern@2339: ID NUMBER PRIMARY KEY NOT NULL bjoern@2339: ); bjoern@2370: INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes', '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); bjoern@2339: CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW bjoern@2339: BEGIN bjoern@2339: SELECT RIVER_AXES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2339: END; bjoern@2339: / bjoern@2339: --CREATE INDEX river_axes_spatial_idx ON river_axes(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); bjoern@2339: bjoern@2339: bjoern@2339: -- Geodaesie/Flussachse+km/km.shp bjoern@2339: CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; bjoern@2339: CREATE TABLE river_axes_km( bjoern@2339: OGR_FID NUMBER(38), bjoern@2339: GEOM MDSYS.SDO_GEOMETRY, aheinecke@5025: river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, tom@5783: km NUMBER(7,3) NOT NULL, tom@5886: fedstate_km NUMBER(7,3), ingo@2861: name VARCHAR(64), ingo@3677: path VARCHAR(256), bjoern@2339: ID NUMBER PRIMARY KEY NOT NULL bjoern@2339: ); aheinecke@5358: INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes_km', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001)), 31467); bjoern@2339: CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW bjoern@2339: BEGIN bjoern@2339: SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2339: END; bjoern@2339: / bjoern@2339: --CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point'); bjoern@2339: bjoern@2339: bjoern@2339: --Geodaesie/Querprofile/QP-Spuren/qps.shp tom@5214: CREATE TABLE cross_section_track_kinds( tom@5214: id NUMBER PRIMARY KEY NOT NULL, tom@5214: name VARCHAR(64) tom@5214: ); tom@5214: INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige'); aheinecke@5495: INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'aktuelle Querprofilspuren'); tom@5214: bjoern@2339: CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; bjoern@2339: CREATE TABLE cross_section_tracks ( bjoern@2339: OGR_FID NUMBER(38), bjoern@2339: GEOM MDSYS.SDO_GEOMETRY, aheinecke@5025: river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, tom@6632: kind_id NUMBER(38) DEFAULT 0 NOT NULL REFERENCES cross_section_track_kinds(id), bjoern@2339: km NUMBER(38,12) NOT NULL, bjoern@2339: z NUMBER(38,12) DEFAULT 0 NOT NULL, ingo@2861: name VARCHAR(64), ingo@3677: path VARCHAR(256), bjoern@2339: ID NUMBER PRIMARY KEY NOT NULL bjoern@2339: ); bjoern@2370: INSERT INTO USER_SDO_GEOM_METADATA VALUES ('cross_section_tracks', '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); bjoern@2339: CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW bjoern@2339: BEGIN bjoern@2339: SELECT CROSS_SECTION_TRACKS_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2339: END; bjoern@2339: / bjoern@2339: --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); bjoern@2339: bjoern@2339: aheinecke@5385: CREATE TABLE building_kinds( aheinecke@5385: id NUMBER PRIMARY KEY NOT NULL, aheinecke@5385: name VARCHAR(64) aheinecke@5385: ); aheinecke@5385: INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige'); aheinecke@5385: INSERT INTO building_kinds(id, name) VALUES (1, 'Brücken'); aheinecke@5385: INSERT INTO building_kinds(id, name) VALUES (2, 'Wehre'); aheinecke@5385: INSERT INTO building_kinds(id, name) VALUES (3, 'Pegel'); aheinecke@5385: aheinecke@5385: -- Geodaesie/Bauwerke bjoern@2339: CREATE SEQUENCE BUILDINGS_ID_SEQ; bjoern@2339: CREATE TABLE buildings( bjoern@2339: OGR_FID NUMBER(38), bjoern@2339: GEOM MDSYS.SDO_GEOMETRY, aheinecke@5025: river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, aheinecke@5408: kind_id NUMBER(38) DEFAULT 0 NOT NULL REFERENCES building_kinds(id), aheinecke@5408: km NUMBER(38,11), aheinecke@5385: name VARCHAR2(255), -- The layername aheinecke@5385: description VARCHAR(256), -- Name taken from attributes ingo@3677: path VARCHAR(256), bjoern@2339: ID NUMBER PRIMARY KEY NOT NULL bjoern@2339: ); bjoern@2370: INSERT INTO USER_SDO_GEOM_METADATA VALUES ('buildings', '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); bjoern@2339: CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW bjoern@2339: BEGIN bjoern@2339: SELECT BUILDINGS_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2339: END; bjoern@2339: / bjoern@2339: --CREATE INDEX buildings_spatial_idx ON buildings(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); bjoern@2339: bjoern@2339: bjoern@2339: -- Geodaesie/Festpunkte/Festpunkte.shp bjoern@2339: CREATE SEQUENCE FIXPOINTS_ID_SEQ; bjoern@2339: CREATE TABLE fixpoints ( bjoern@2339: OGR_FID NUMBER(38), bjoern@2339: GEOM MDSYS.SDO_GEOMETRY, aheinecke@5025: river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, bjoern@2339: x NUMBER(38,11), bjoern@2339: y NUMBER(38,11), bjoern@2339: km NUMBER(38,11) NOT NULL, tom@5623: HPGP VARCHAR(64), ingo@2861: name VARCHAR(64), ingo@3677: path VARCHAR(256), bjoern@2339: ID NUMBER PRIMARY KEY NOT NULL bjoern@2339: ); aheinecke@5358: INSERT INTO USER_SDO_GEOM_METADATA VALUES ('fixpoints', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001)), 31467); bjoern@2339: CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW bjoern@2339: BEGIN bjoern@2339: SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2339: END; bjoern@2339: / bjoern@2339: --CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT'); bjoern@2339: bjoern@2339: bjoern@2339: -- Hydrologie/Hydr. Grenzen/talaue.shp tom@5214: CREATE TABLE floodplain_kinds( tom@5214: id NUMBER PRIMARY KEY NOT NULL, tom@5214: name VARCHAR(64) tom@5214: ); tom@5214: INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige'); aheinecke@5495: INSERT INTO floodplain_kinds(id, name) VALUES (1, 'aktuelle Talaue'); tom@5214: bjoern@2339: CREATE SEQUENCE FLOODPLAIN_ID_SEQ; bjoern@2339: CREATE TABLE floodplain( bjoern@2339: OGR_FID NUMBER(38), bjoern@2339: GEOM MDSYS.SDO_GEOMETRY, aheinecke@5025: river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, tom@6632: kind_id NUMBER(38) DEFAULT 0 NOT NULL REFERENCES floodplain_kinds(id), ingo@2861: name VARCHAR(64), ingo@3677: path VARCHAR(256), bjoern@2339: ID NUMBER PRIMARY KEY NOT NULL bjoern@2339: ); bjoern@2370: INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodplain', '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); bjoern@2339: CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW bjoern@2339: BEGIN bjoern@2339: SELECT FLOODPLAIN_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2339: END; bjoern@2339: / bjoern@2339: --CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON'); bjoern@2339: bjoern@2339: bjoern@2339: -- Geodaesie/Hoehenmodelle/* bjoern@2340: CREATE SEQUENCE DEM_ID_SEQ; bjoern@2340: CREATE TABLE dem ( aheinecke@5210: ID NUMBER PRIMARY KEY NOT NULL, aheinecke@5210: river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, aheinecke@5210: name VARCHAR(64), tom@5260: range_id NUMBER(38) REFERENCES ranges(id), aheinecke@5210: time_interval_id NUMBER(38) REFERENCES time_intervals(id), aheinecke@5210: projection VARCHAR(32), aheinecke@5210: elevation_state VARCHAR(32), aheinecke@5210: srid NUMBER NOT NULL, aheinecke@5210: format VARCHAR(32), aheinecke@5210: border_break NUMBER(1) DEFAULT 0 NOT NULL, aheinecke@5210: resolution VARCHAR(16), aheinecke@5210: description VARCHAR(256), tom@5315: path VARCHAR(256) NOT NULL bjoern@2340: ); bjoern@2340: CREATE OR REPLACE TRIGGER dem_trigger BEFORE INSERT ON dem FOR each ROW bjoern@2340: BEGIN bjoern@2340: SELECT DEM_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@2340: END; bjoern@2340: / bjoern@2339: aheinecke@5025: --Static lookup tables for Hochwasserschutzanlagen aheinecke@5025: CREATE TABLE hws_kinds ( aheinecke@5025: id NUMBER PRIMARY KEY NOT NULL, aheinecke@5025: kind VARCHAR(64) NOT NULL aheinecke@5025: ); aheinecke@5025: INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass'); aheinecke@5025: INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm'); aheinecke@5025: INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben'); aheinecke@5025: aheinecke@5025: CREATE TABLE fed_states ( aheinecke@5025: id NUMBER PRIMARY KEY NOT NULL, aheinecke@5025: name VARCHAR(23) NOT NULL aheinecke@5025: ); aheinecke@5025: INSERT INTO fed_states (id, name) VALUES (1, 'Bayern'); aheinecke@5025: INSERT INTO fed_states (id, name) VALUES (2, 'Hessen'); aheinecke@5025: INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen'); aheinecke@5025: INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen'); aheinecke@5025: INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz'); aheinecke@5025: INSERT INTO fed_states (id, name) VALUES (6, 'Saarland'); aheinecke@5025: INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein'); aheinecke@5025: INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg'); aheinecke@5025: INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern'); aheinecke@5025: INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen'); aheinecke@5025: INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg'); aheinecke@5025: INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt'); aheinecke@5025: INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen'); aheinecke@5025: INSERT INTO fed_states (id, name) VALUES (14, 'Berlin'); aheinecke@5025: INSERT INTO fed_states (id, name) VALUES (15, 'Bremen'); aheinecke@5025: INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg'); aheinecke@5025: bjoern@1250: --Hydrologie/HW-Schutzanlagen/hws.shp aheinecke@5025: -- HWS-Lines aheinecke@5025: CREATE SEQUENCE HWS_LINES_ID_SEQ; aheinecke@5025: CREATE TABLE hws_lines ( aheinecke@5122: OGR_FID NUMBER(38), bjoern@1250: GEOM MDSYS.SDO_GEOMETRY, aheinecke@5025: kind_id NUMBER(2) DEFAULT 2 REFERENCES hws_kinds(id), aheinecke@5025: fed_state_id NUMBER(2) REFERENCES fed_states(id), tom@5577: river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, aheinecke@5025: name VARCHAR(256), aheinecke@5025: path VARCHAR(256), aheinecke@5025: official NUMBER DEFAULT 0, aheinecke@5025: agency VARCHAR(256), aheinecke@5025: range VARCHAR(256), aheinecke@5025: shore_side NUMBER DEFAULT 0, aheinecke@5025: source VARCHAR(256), aheinecke@5025: status_date TIMESTAMP, aheinecke@5122: description VARCHAR(256), aheinecke@5122: id NUMBER PRIMARY KEY NOT NULL bjoern@1250: ); aheinecke@5025: 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); aheinecke@5025: CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW bjoern@1250: BEGIN aheinecke@5025: SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual; bjoern@1250: END; aheinecke@5038: / tom@5748: aheinecke@5025: CREATE TABLE sectie_kinds ( aheinecke@5025: id NUMBER PRIMARY KEY NOT NULL, aheinecke@5025: name VARCHAR(64) NOT NULL aheinecke@5025: ); tom@5748: INSERT INTO sectie_kinds (id, name) VALUES (0, 'nicht berücksichtigt'); tom@5748: INSERT INTO sectie_kinds (id, name) VALUES (1, 'Hauptgerinne'); tom@5748: INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbereich'); tom@5748: INSERT INTO sectie_kinds (id, name) VALUES (3, 'Vorland'); aheinecke@5025: aheinecke@5025: CREATE TABLE sobek_kinds ( aheinecke@5025: id NUMBER PRIMARY KEY NOT NULL, aheinecke@5025: name VARCHAR(64) NOT NULL aheinecke@5025: ); tom@5748: INSERT INTO sobek_kinds (id, name) VALUES (0, 'nicht berücksichtigt'); tom@5748: INSERT INTO sobek_kinds (id, name) VALUES (1, 'durchströmt'); tom@5748: INSERT INTO sobek_kinds (id, name) VALUES (2, 'nicht durchströmt'); aheinecke@5025: aheinecke@5025: CREATE TABLE boundary_kinds ( aheinecke@5025: id NUMBER PRIMARY KEY NOT NULL, aheinecke@5025: name VARCHAR(64) NOT NULL aheinecke@5025: ); aheinecke@5025: INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt'); aheinecke@5025: INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG'); aheinecke@5025: INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land'); aheinecke@5025: INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige'); aheinecke@5025: aheinecke@5025: -- HWS Points aheinecke@5025: CREATE SEQUENCE HWS_POINTS_ID_SEQ; aheinecke@5025: CREATE TABLE hws_points ( aheinecke@5122: OGR_FID NUMBER(38), aheinecke@5025: GEOM MDSYS.SDO_GEOMETRY, aheinecke@5025: kind_id NUMBER DEFAULT 2 REFERENCES hws_kinds(id), aheinecke@5025: fed_state_id NUMBER REFERENCES fed_states(id), aheinecke@5025: river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, aheinecke@5025: name VARCHAR(256), aheinecke@5025: path VARCHAR(256), aheinecke@5025: official NUMBER DEFAULT 0, aheinecke@5025: agency VARCHAR(256), aheinecke@5025: range VARCHAR(256), aheinecke@5025: shore_side NUMBER DEFAULT 0, aheinecke@5025: source VARCHAR(256), aheinecke@5025: status_date VARCHAR(256), aheinecke@5025: description VARCHAR(256), aheinecke@5025: freeboard NUMBER(19,5), aheinecke@5025: dike_km NUMBER(19,5), aheinecke@5025: z NUMBER(19,5), aheinecke@5025: z_target NUMBER(19,5), aheinecke@5122: rated_level NUMBER(19,5), aheinecke@5122: id NUMBER PRIMARY KEY NOT NULL aheinecke@5025: ); aheinecke@5025: aheinecke@5025: 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); aheinecke@5025: aheinecke@5038: CREATE OR REPLACE TRIGGER hws_points_trigger BEFORE INSERT ON hws_points FOR each ROW aheinecke@5025: BEGIN aheinecke@5038: SELECT HWS_POINTS_ID_SEQ.nextval INTO :new.id FROM dual; aheinecke@5025: END; aheinecke@5038: / ingo@2796: ingo@2796: --Hydrologie/UeSG tom@5276: CREATE TABLE floodmap_kinds ( tom@5276: id NUMBER PRIMARY KEY NOT NULL, tom@5276: name varchar(64) NOT NULL tom@5276: ); tom@5276: INSERT INTO floodmap_kinds VALUES (200, 'Messung'); tom@5276: INSERT INTO floodmap_kinds VALUES (111, 'Berechnung-Aktuell-BfG'); tom@5276: INSERT INTO floodmap_kinds VALUES (112, 'Berechnung-Aktuell-Bundesländer'); tom@5276: INSERT INTO floodmap_kinds VALUES (121, 'Berechnung-Potenziell-BfG'); tom@5276: INSERT INTO floodmap_kinds VALUES (122, 'Berechnung-Potenziell-Bundesländer'); tom@5276: ingo@2796: CREATE SEQUENCE FLOODMAPS_ID_SEQ; ingo@2796: CREATE TABLE floodmaps ( ingo@2800: OGR_FID NUMBER(38), ingo@2800: GEOM MDSYS.SDO_GEOMETRY, aheinecke@5025: river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, tom@5783: name VARCHAR(255) NOT NULL, tom@5276: kind NUMBER NOT NULL REFERENCES floodmap_kinds(id), ingo@2796: diff NUMBER(19,5), ingo@2796: count NUMBER(38), ingo@2796: area NUMBER(19,5), ingo@2800: perimeter NUMBER(19,5), tom@5854: waterbody VARCHAR(64), ingo@3677: path VARCHAR(256), tom@5276: source varchar(64), ingo@2800: id NUMBER PRIMARY KEY NOT NULL ingo@2796: ); ingo@2800: INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodmaps', '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); ingo@2796: CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW ingo@2796: BEGIN ingo@2796: SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual; ingo@2796: END; aheinecke@5038: / ingo@2861: ingo@2861: --Hydrologie/Hydr.Grenzen/Linien ingo@2861: CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; ingo@2861: CREATE TABLE hydr_boundaries ( ingo@2861: OGR_FID NUMBER(38), ingo@2861: GEOM MDSYS.SDO_GEOMETRY, aheinecke@5025: river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, ingo@2861: name VARCHAR(255), aheinecke@5535: kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id), tom@5748: sectie NUMBER(38) REFERENCES sectie_kinds(id), tom@5748: sobek NUMBER(38) REFERENCES sobek_kinds(id), ingo@3677: path VARCHAR(256), ingo@2861: id NUMBER PRIMARY KEY NOT NULL ingo@2861: ); ingo@2861: INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hydr_boundaries', '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); ingo@2861: CREATE OR REPLACE TRIGGER hydr_boundaries_trigger BEFORE INSERT ON hydr_boundaries FOR each ROW ingo@2861: BEGIN ingo@2861: SELECT HYDR_BOUNDARIES_ID_SEQ.nextval INTO :new.id FROM dual; ingo@2861: END; aheinecke@5038: / ingo@2861: ingo@2861: CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; ingo@2861: CREATE TABLE hydr_boundaries_poly ( ingo@2861: OGR_FID NUMBER(38), ingo@2861: GEOM MDSYS.SDO_GEOMETRY, aheinecke@5025: river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, ingo@2861: name VARCHAR(255), aheinecke@5535: kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id), tom@5748: sectie NUMBER(38) REFERENCES sectie_kinds(id), tom@5748: sobek NUMBER(38) REFERENCES sobek_kinds(id), ingo@3677: path VARCHAR(256), ingo@2861: id NUMBER PRIMARY KEY NOT NULL ingo@2861: ); ingo@2861: INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hydr_boundaries_poly', '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); ingo@2861: CREATE OR REPLACE TRIGGER hydr_boundaries_poly_trigger BEFORE INSERT ON hydr_boundaries_poly FOR each ROW ingo@2861: BEGIN ingo@2861: SELECT HYDR_BOUNDARIES_POLY_ID_SEQ.nextval INTO :new.id FROM dual; ingo@2861: END; aheinecke@5038: / ingo@2862: ingo@2862: -- Hydrologie/Streckendaten/ ingo@2862: CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ; ingo@2862: CREATE TABLE gauge_location ( ingo@2862: OGR_FID NUMBER(38), ingo@2862: GEOM MDSYS.SDO_GEOMETRY, ingo@2862: river_id NUMBER(38), ingo@2862: name VARCHAR(64), ingo@3677: path VARCHAR(256), ingo@2862: id NUMBER PRIMARY KEY NOT NULL ingo@2862: ); ingo@2862: INSERT INTO USER_SDO_GEOM_METADATA VALUES ('gauge_location', '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); ingo@2862: CREATE OR REPLACE TRIGGER gauge_location_trigger BEFORE INSERT ON gauge_location FOR EACH ROW ingo@2862: BEGIN ingo@2862: SELECT GAUGE_LOCATION_ID_SEQ.nextval INTO :new.id FROM dual; ingo@2862: END; aheinecke@5038: / tom@5291: tom@5291: tom@5291: CREATE TABLE jetty_kinds( tom@5291: id NUMBER PRIMARY KEY NOT NULL, tom@5291: name VARCHAR(64) tom@5291: ); tom@5291: INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf'); tom@5291: INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß'); tom@5291: INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel'); tom@5291: tom@5291: CREATE SEQUENCE JETTIES_ID_SEQ; tom@5291: CREATE TABLE jetties ( tom@5291: OGR_FID NUMBER(38), tom@5291: GEOM MDSYS.SDO_GEOMETRY, tom@5291: id NUMBER PRIMARY KEY NOT NULL, tom@5291: river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, tom@5291: path VARCHAR(256), tom@5291: kind_id NUMBER(38) REFERENCES jetty_kinds(id), tom@5291: km NUMBER(7,3), tom@5291: z NUMBER(38,12) tom@5291: ); aheinecke@5354: INSERT INTO USER_SDO_GEOM_METADATA VALUES ('jetties', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001)), 31467); tom@5291: CREATE OR REPLACE TRIGGER jetties_trigger BEFORE INSERT ON jetties FOR EACH ROW tom@5291: BEGIN tom@5291: SELECT JETTIES_ID_SEQ.nextval INTO :new.id FROM dual; tom@5291: END; tom@5291: / tom@5409: tom@5409: CREATE SEQUENCE FLOOD_MARKS_ID_SEQ; tom@5409: CREATE TABLE flood_marks ( tom@5409: OGR_FID NUMBER(38), tom@5409: GEOM MDSYS.SDO_GEOMETRY, tom@5409: id NUMBER PRIMARY KEY NOT NULL, tom@5409: river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, tom@5409: path VARCHAR(256), tom@5409: km NUMBER(7,3), tom@5409: z NUMBER(38,12), tom@5409: location VARCHAR(64), tom@5409: year NUMBER(38,0) tom@5409: ); tom@5409: INSERT INTO USER_SDO_GEOM_METADATA VALUES ('flood_marks', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001)), 31467); tom@5409: CREATE OR REPLACE TRIGGER flood_marks_trigger BEFORE INSERT ON flood_marks FOR EACH ROW tom@5409: BEGIN tom@5409: SELECT FLOOD_MARKS_ID_SEQ.nextval INTO :new.id FROM dual; tom@5409: END; tom@5409: /