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,
bjoern@2339:     river_id NUMBER(38),
bjoern@2339:     kind     NUMBER(38) DEFAULT 0 NOT NULL,
ingo@2861:     name     VARCHAR(64),
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,
bjoern@2339:     river_id NUMBER(38),
bjoern@2339:     km NUMBER(6,3),
ingo@2861:     name     VARCHAR(64),
bjoern@2339:     ID NUMBER PRIMARY KEY NOT NULL
bjoern@2339: );
bjoern@2370: 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),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 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
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,
bjoern@2339:     river_id NUMBER(38),
bjoern@2339:     km       NUMBER(38,12) NOT NULL,
bjoern@2339:     z        NUMBER(38,12) DEFAULT 0 NOT NULL,
ingo@2861:     name     VARCHAR(64),
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: 
bjoern@2340: -- TODO: TestMe. Fix Importer-Script. Fix oracle_spatial_idx.sql script.
bjoern@2340: -- Geodaesie/Linien/rohre-und-speeren
bjoern@2340: CREATE SEQUENCE LINES_ID_SEQ;
bjoern@2340: CREATE TABLE lines (
bjoern@2340:     OGR_FID NUMBER(38),
bjoern@2340:     GEOM MDSYS.SDO_GEOMETRY,
bjoern@2340:     river_id NUMBER(38),
ingo@2365:     kind     VARCHAR2(16) NOT NULL,
bjoern@2340:     z        NUMBER(38,12) DEFAULT 0,
ingo@2861:     name     VARCHAR(64),
bjoern@2340:     ID NUMBER PRIMARY KEY NOT NULL
bjoern@2340: );
bjoern@2370: INSERT INTO USER_SDO_GEOM_METADATA VALUES ('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);
bjoern@2340: CREATE OR REPLACE TRIGGER lines_trigger BEFORE INSERT ON lines FOR each ROW
bjoern@2340:     BEGIN
bjoern@2340:         SELECT LINES_ID_SEQ.nextval INTO :new.id FROM dual;
bjoern@2340:     END;
bjoern@2340: /
bjoern@2340: -- NOTE: Should lines should be 3D.
bjoern@2340: -- TODO: Test index. 
bjoern@2340: --CREATE INDEX lines_idx ON lines(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
bjoern@2340: -- 'kind':
bjoern@2340: -- 0: ROHR1
bjoern@2340: -- 1: DAMM
bjoern@2339: 
bjoern@2339: 
bjoern@2339: -- Geodaesie/Bauwerke/Wehre.shp
bjoern@2339: CREATE SEQUENCE BUILDINGS_ID_SEQ;
bjoern@2339: CREATE TABLE buildings(
bjoern@2339:     OGR_FID NUMBER(38),
bjoern@2339:     GEOM MDSYS.SDO_GEOMETRY,
bjoern@2339:     river_id NUMBER(38),
bjoern@2339:     name VARCHAR2(255),
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,
bjoern@2339:     river_id NUMBER(38),
bjoern@2339:     x NUMBER(38,11),
bjoern@2339:     y NUMBER(38,11),
bjoern@2339:     km NUMBER(38,11) NOT NULL,
bjoern@2339:     HPGP VARCHAR2(255),
ingo@2861:     name VARCHAR(64),
bjoern@2339:     ID NUMBER PRIMARY KEY NOT NULL
bjoern@2339: );
bjoern@2370: 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),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 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
bjoern@2339: CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
bjoern@2339: CREATE TABLE floodplain(
bjoern@2339:     OGR_FID NUMBER(38),
bjoern@2339:     GEOM MDSYS.SDO_GEOMETRY,
bjoern@2339:     river_id NUMBER(38),
ingo@2861:     name     VARCHAR(64),
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@2340: -- TODO: Test-Me. Fix Importer-Skript.
bjoern@2340: -- NOTE: It's not a spatial schema!
bjoern@2339: -- Geodaesie/Hoehenmodelle/*
bjoern@2340: CREATE SEQUENCE DEM_ID_SEQ;
bjoern@2340: CREATE TABLE dem (
bjoern@2340:     ID NUMBER PRIMARY KEY NOT NULL,
bjoern@2340:     river_id NUMBER(38),
bjoern@2340:     -- XXX Should we use the ranges table instead?
ingo@2803:     lower    NUMBER(19,5),
ingo@2803:     upper    NUMBER(19,5),
bjoern@2340:     path     VARCHAR(256),
bjoern@2340:     UNIQUE (river_id, lower, upper)
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: 
bjoern@2339: 
bjoern@1244: -- Hydrologie/Einzugsgebiete/EZG.shp
bjoern@1244: CREATE SEQUENCE CATCHMENT_ID_SEQ;
bjoern@1244: CREATE TABLE catchment(
bjoern@1244:     OGR_FID NUMBER(38),
bjoern@1244:     GEOM MDSYS.SDO_GEOMETRY,
bjoern@1244:     river_id NUMBER(38),
bjoern@1244:     area NUMBER(19,5),
bjoern@2335:     name VARCHAR2(255),
bjoern@1244:     ID NUMBER PRIMARY KEY NOT NULL
bjoern@1244: );
bjoern@2370: INSERT INTO USER_SDO_GEOM_METADATA VALUES ('CATCHMENT', '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@1244: 
bjoern@1244: CREATE TRIGGER catchment_trigger BEFORE INSERT ON catchment FOR each ROW
bjoern@1244:     BEGIN
bjoern@1244:         SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual;
bjoern@1244:     END;
bjoern@1244: /
bjoern@1253: --CREATE INDEX catchment_spatial_idx ON catchment(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=polygon');
bjoern@1244: 
bjoern@1250: --Hydrologie/HW-Schutzanlagen/hws.shp
bjoern@1250: CREATE SEQUENCE HWS_ID_SEQ;
bjoern@1250: CREATE TABLE hws(
bjoern@1250:     OGR_FID NUMBER(38),
bjoern@1250:     GEOM MDSYS.SDO_GEOMETRY,
bjoern@1250:     river_id NUMBER(38),
bjoern@2335:     hws_facility VARCHAR2(255),
bjoern@2335:     type VARCHAR2(255),
ingo@2861:     name VARCHAR(64),
bjoern@1250:     ID NUMBER PRIMARY KEY NOT NULL
bjoern@1250: );
bjoern@2370: 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);
bjoern@1250: CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW
bjoern@1250:     BEGIN
bjoern@1250:         SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual;
bjoern@1250:     END;
bjoern@1250: /
bjoern@1253: --CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
ingo@2796: 
ingo@2796: 
ingo@2796: --Hydrologie/UeSG
ingo@2796: CREATE SEQUENCE FLOODMAPS_ID_SEQ;
ingo@2796: CREATE TABLE floodmaps (
ingo@2800:     OGR_FID NUMBER(38),
ingo@2800:     GEOM MDSYS.SDO_GEOMETRY,
ingo@2796:     river_id NUMBER(38),
ingo@2800:     name VARCHAR(255),
ingo@2796:     kind NUMBER(38),
ingo@2796:     diff NUMBER(19,5),
ingo@2796:     count NUMBER(38),
ingo@2796:     area NUMBER(19,5),
ingo@2800:     perimeter NUMBER(19,5),
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;
ingo@2852: /
ingo@2800: CREATE INDEX floodmaps_spatial_idx ON floodmaps(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON');
ingo@2861: 
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,
ingo@2861:     river_id NUMBER(38),
ingo@2861:     name VARCHAR(255),
ingo@2861:     kind NUMBER(38),
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;
ingo@2861: /
ingo@2861: CREATE INDEX hydr_boundaries_idx ON hydr_boundaries(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
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,
ingo@2861:     river_id NUMBER(38),
ingo@2861:     name VARCHAR(255),
ingo@2861:     kind NUMBER(38),
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;
ingo@2861: /
ingo@2861: CREATE INDEX hydr_boundaries_poly_idx ON hydr_boundaries_poly(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON');