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), 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, bjoern@2339: river_id NUMBER(38), bjoern@2339: km NUMBER(6,3), 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_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), 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: 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), ingo@3677: path VARCHAR(256), 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), 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, 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), ingo@3677: path VARCHAR(256), 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), 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@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@3951: name VARCHAR(64), ingo@3951: lower NUMBER(19,5), ingo@3951: upper NUMBER(19,5), ingo@3961: year_from VARCHAR(32) NOT NULL, ingo@3961: year_to VARCHAR(32) NOT NULL, ingo@3961: projection VARCHAR(32) NOT NULL, ingo@3951: elevation_state VARCHAR(32), ingo@3951: format VARCHAR(32), ingo@3951: border_break BOOLEAN NOT NULL DEFAULT FALSE, ingo@3951: resolution VARCHAR(16), ingo@3951: description VARCHAR(256), ingo@3951: path VARCHAR(256) 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), ingo@3677: path VARCHAR(256), 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), ingo@3677: path VARCHAR(256), 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@3677: path VARCHAR(256), 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@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; 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@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; ingo@2861: / ingo@2861: CREATE INDEX hydr_boundaries_poly_idx ON hydr_boundaries_poly(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON'); ingo@2862: 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; ingo@2862: / ingo@2862: CREATE INDEX gauge_location_idx ON gauge_location(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');