aheinecke@5026: DROP TRIGGER hws_trigger;
aheinecke@5026: DROP TABLE hws;
aheinecke@5026: DROP SEQUENCE HWS_ID_SEQ;
aheinecke@5026: 
aheinecke@5026: --Static lookup tables for Hochwasserschutzanlagen
aheinecke@5026: CREATE TABLE hws_kinds (
aheinecke@5026:     id NUMBER PRIMARY KEY NOT NULL,
aheinecke@5026:     kind VARCHAR(64) NOT NULL
aheinecke@5026: );
aheinecke@5026: INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass');
aheinecke@5026: INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm');
aheinecke@5026: INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
aheinecke@5026: 
aheinecke@5026: CREATE TABLE fed_states (
aheinecke@5026:     id NUMBER PRIMARY KEY NOT NULL,
aheinecke@5026:     name VARCHAR(23) NOT NULL
aheinecke@5026: );
aheinecke@5026: INSERT INTO fed_states (id, name) VALUES (1, 'Bayern');
aheinecke@5026: INSERT INTO fed_states (id, name) VALUES (2, 'Hessen');
aheinecke@5026: INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen');
aheinecke@5026: INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen');
aheinecke@5026: INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz');
aheinecke@5026: INSERT INTO fed_states (id, name) VALUES (6, 'Saarland');
aheinecke@5026: INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein');
aheinecke@5026: INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg');
aheinecke@5026: INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern');
aheinecke@5026: INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen');
aheinecke@5026: INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg');
aheinecke@5026: INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt');
aheinecke@5026: INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen');
aheinecke@5026: INSERT INTO fed_states (id, name) VALUES (14, 'Berlin');
aheinecke@5026: INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
aheinecke@5026: INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
aheinecke@5026: 
aheinecke@5026: -- HWS-Lines
aheinecke@5026: CREATE SEQUENCE HWS_LINES_ID_SEQ;
aheinecke@5026: CREATE TABLE hws_lines (
aheinecke@5122:     OGR_FID NUMBER(38),
aheinecke@5026:     GEOM MDSYS.SDO_GEOMETRY,
aheinecke@5122:     kind_id NUMBER(2) DEFAULT 2 REFERENCES hws_kinds(id),
aheinecke@5122:     fed_state_id NUMBER(2) REFERENCES fed_states(id),
aheinecke@5122:     river_id NUMBER(38) REFERENCES rivers(id),
aheinecke@5026:     name VARCHAR(256),
aheinecke@5026:     path VARCHAR(256),
aheinecke@5026:     official NUMBER DEFAULT 0,
aheinecke@5026:     agency VARCHAR(256),
aheinecke@5026:     range VARCHAR(256),
aheinecke@5026:     shore_side NUMBER DEFAULT 0,
aheinecke@5026:     source VARCHAR(256),
aheinecke@5026:     status_date TIMESTAMP,
aheinecke@5122:     description VARCHAR(256),
aheinecke@5122:     id NUMBER PRIMARY KEY NOT NULL
aheinecke@5026: );
aheinecke@5026: 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@5026: CREATE INDEX hws_lines_spatial_idx ON hws_lines(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
aheinecke@5026: 
aheinecke@5026: CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW
aheinecke@5026:     BEGIN
aheinecke@5026:         SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual;
aheinecke@5026:     END;
aheinecke@5026: 
aheinecke@5026: -- HWS Points lookup tables
aheinecke@5026: CREATE TABLE sectie_kinds (
aheinecke@5026:     id NUMBER PRIMARY KEY NOT NULL,
aheinecke@5026:     name VARCHAR(64) NOT NULL
aheinecke@5026: );
aheinecke@5026: INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt');
aheinecke@5026: INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch');
aheinecke@5026: INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank');
aheinecke@5026: INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich');
aheinecke@5026: 
aheinecke@5026: CREATE TABLE sobek_kinds (
aheinecke@5026:     id NUMBER PRIMARY KEY NOT NULL,
aheinecke@5026:     name VARCHAR(64) NOT NULL
aheinecke@5026: );
aheinecke@5026: INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt');
aheinecke@5026: INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend');
aheinecke@5026: INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd');
aheinecke@5026: 
aheinecke@5026: CREATE TABLE boundary_kinds (
aheinecke@5026:     id NUMBER PRIMARY KEY NOT NULL,
aheinecke@5026:     name VARCHAR(64) NOT NULL
aheinecke@5026: );
aheinecke@5026: INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
aheinecke@5026: INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
aheinecke@5026: INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
aheinecke@5026: INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
aheinecke@5026: 
aheinecke@5026: -- HWS Points
aheinecke@5026: CREATE SEQUENCE HWS_POINTS_ID_SEQ;
aheinecke@5026: CREATE TABLE hws_points (
aheinecke@5122:     OGR_FID NUMBER(38),
aheinecke@5026:     GEOM MDSYS.SDO_GEOMETRY,
aheinecke@5122:     ogr_fid NUMBER,
aheinecke@5122:     kind_id NUMBER DEFAULT 2 REFERENCES hws_kinds(id),
aheinecke@5122:     fed_state_id NUMBER REFERENCES fed_states(id),
aheinecke@5026:     river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
aheinecke@5026:     name VARCHAR(256),
aheinecke@5026:     path VARCHAR(256),
aheinecke@5026:     official NUMBER DEFAULT 0,
aheinecke@5026:     agency VARCHAR(256),
aheinecke@5026:     range VARCHAR(256),
aheinecke@5026:     shore_side NUMBER DEFAULT 0,
aheinecke@5026:     source VARCHAR(256),
aheinecke@5026:     status_date VARCHAR(256),
aheinecke@5026:     description VARCHAR(256),
aheinecke@5026:     freeboard NUMBER(19,5),
aheinecke@5026:     dike_km NUMBER(19,5),
aheinecke@5026:     z NUMBER(19,5),
aheinecke@5026:     z_target NUMBER(19,5),
aheinecke@5122:     rated_level NUMBER(19,5),
aheinecke@5122:     id NUMBER PRIMARY KEY NOT NULL
aheinecke@5026: );
aheinecke@5026: 
aheinecke@5026: -- Altrications
aheinecke@5078: ALTER TABLE dem ADD srid NUMBER NOT NULL;
aheinecke@5078: ALTER TABLE hydr_boundaries_poly ADD sectie NUMBER REFERENCES sectie_kinds(id);
aheinecke@5078: ALTER TABLE hydr_boundaries_poly ADD sobek NUMBER REFERENCES sobek_kinds(id);
aheinecke@5078: ALTER TABLE hydr_boundaries ADD sectie NUMBER REFERENCES sectie_kinds(id);
aheinecke@5078: ALTER TABLE hydr_boundaries ADD sobek NUMBER REFERENCES sobek_kinds(id);
aheinecke@5078: ALTER TABLE hydr_boundaries ADD kind NUMBER REFERENCES boundary_kinds(id);
aheinecke@5078: ALTER TABLE hydr_boundaries_poly ADD kind NUMBER REFERENCES boundary_kinds(id);