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@7001:     range_id         NUMBER(38) REFERENCES ranges(id) ON DELETE CASCADE,
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: /