Mercurial > dive4elements > river
view flys-backend/doc/schema/oracle-spatial.sql @ 4695:139cf1f9a22c
Use isEmpty for list check
author | Björn Ricks <bjoern.ricks@intevation.de> |
---|---|
date | Mon, 17 Dec 2012 16:16:49 +0100 |
parents | f5912365619c |
children | eecd1ebba128 |
line wrap: on
line source
-- Geodaesie/Flussachse+km/achse CREATE SEQUENCE RIVER_AXES_ID_SEQ; CREATE TABLE river_axes( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, river_id NUMBER(38), kind NUMBER(38) DEFAULT 0 NOT NULL, name VARCHAR(64), path VARCHAR(256), ID NUMBER PRIMARY KEY NOT NULL ); 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); CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW BEGIN SELECT RIVER_AXES_ID_SEQ.nextval INTO :new.id FROM dual; END; / --CREATE INDEX river_axes_spatial_idx ON river_axes(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); -- Geodaesie/Flussachse+km/km.shp CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; CREATE TABLE river_axes_km( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, river_id NUMBER(38), km NUMBER(6,3), name VARCHAR(64), path VARCHAR(256), ID NUMBER PRIMARY KEY NOT NULL ); 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); CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW BEGIN SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual; END; / --CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point'); --Geodaesie/Querprofile/QP-Spuren/qps.shp CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; CREATE TABLE cross_section_tracks ( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, river_id NUMBER(38), km NUMBER(38,12) NOT NULL, z NUMBER(38,12) DEFAULT 0 NOT NULL, name VARCHAR(64), path VARCHAR(256), ID NUMBER PRIMARY KEY NOT NULL ); 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); CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW BEGIN SELECT CROSS_SECTION_TRACKS_ID_SEQ.nextval INTO :new.id FROM dual; END; / --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); -- TODO: TestMe. Fix Importer-Script. Fix oracle_spatial_idx.sql script. -- Geodaesie/Linien/rohre-und-speeren CREATE SEQUENCE LINES_ID_SEQ; CREATE TABLE lines ( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, river_id NUMBER(38), kind VARCHAR2(16) NOT NULL, z NUMBER(38,12) DEFAULT 0, name VARCHAR(64), path VARCHAR(256), ID NUMBER PRIMARY KEY NOT NULL ); 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); CREATE OR REPLACE TRIGGER lines_trigger BEFORE INSERT ON lines FOR each ROW BEGIN SELECT LINES_ID_SEQ.nextval INTO :new.id FROM dual; END; / -- NOTE: Should lines should be 3D. -- TODO: Test index. --CREATE INDEX lines_idx ON lines(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); -- 'kind': -- 0: ROHR1 -- 1: DAMM -- Geodaesie/Bauwerke/Wehre.shp CREATE SEQUENCE BUILDINGS_ID_SEQ; CREATE TABLE buildings( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, river_id NUMBER(38), name VARCHAR2(255), path VARCHAR(256), ID NUMBER PRIMARY KEY NOT NULL ); 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); CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW BEGIN SELECT BUILDINGS_ID_SEQ.nextval INTO :new.id FROM dual; END; / --CREATE INDEX buildings_spatial_idx ON buildings(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); -- Geodaesie/Festpunkte/Festpunkte.shp CREATE SEQUENCE FIXPOINTS_ID_SEQ; CREATE TABLE fixpoints ( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, river_id NUMBER(38), x NUMBER(38,11), y NUMBER(38,11), km NUMBER(38,11) NOT NULL, HPGP VARCHAR2(255), name VARCHAR(64), path VARCHAR(256), ID NUMBER PRIMARY KEY NOT NULL ); 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); CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW BEGIN SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual; END; / --CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT'); -- Hydrologie/Hydr. Grenzen/talaue.shp CREATE SEQUENCE FLOODPLAIN_ID_SEQ; CREATE TABLE floodplain( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, river_id NUMBER(38), name VARCHAR(64), path VARCHAR(256), ID NUMBER PRIMARY KEY NOT NULL ); 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); CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW BEGIN SELECT FLOODPLAIN_ID_SEQ.nextval INTO :new.id FROM dual; END; / --CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON'); -- TODO: Test-Me. Fix Importer-Skript. -- NOTE: It's not a spatial schema! -- Geodaesie/Hoehenmodelle/* CREATE SEQUENCE DEM_ID_SEQ; CREATE TABLE dem ( ID NUMBER PRIMARY KEY NOT NULL, river_id NUMBER(38), -- XXX Should we use the ranges table instead? name VARCHAR(64), lower NUMBER(19,5), upper NUMBER(19,5), year_from VARCHAR(32) NOT NULL, year_to VARCHAR(32) NOT NULL, projection VARCHAR(32) NOT NULL, elevation_state VARCHAR(32), format VARCHAR(32), border_break BOOLEAN NOT NULL DEFAULT FALSE, resolution VARCHAR(16), description VARCHAR(256), path VARCHAR(256) ); CREATE OR REPLACE TRIGGER dem_trigger BEFORE INSERT ON dem FOR each ROW BEGIN SELECT DEM_ID_SEQ.nextval INTO :new.id FROM dual; END; / -- Hydrologie/Einzugsgebiete/EZG.shp CREATE SEQUENCE CATCHMENT_ID_SEQ; CREATE TABLE catchment( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, river_id NUMBER(38), area NUMBER(19,5), name VARCHAR2(255), path VARCHAR(256), ID NUMBER PRIMARY KEY NOT NULL ); 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); CREATE TRIGGER catchment_trigger BEFORE INSERT ON catchment FOR each ROW BEGIN SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual; END; / --CREATE INDEX catchment_spatial_idx ON catchment(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=polygon'); --Hydrologie/HW-Schutzanlagen/hws.shp CREATE SEQUENCE HWS_ID_SEQ; CREATE TABLE hws( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, river_id NUMBER(38), hws_facility VARCHAR2(255), type VARCHAR2(255), name VARCHAR(64), path VARCHAR(256), ID NUMBER PRIMARY KEY NOT NULL ); 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); CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW BEGIN SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual; END; / --CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); --Hydrologie/UeSG CREATE SEQUENCE FLOODMAPS_ID_SEQ; CREATE TABLE floodmaps ( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, river_id NUMBER(38), name VARCHAR(255), kind NUMBER(38), diff NUMBER(19,5), count NUMBER(38), area NUMBER(19,5), perimeter NUMBER(19,5), path VARCHAR(256), id NUMBER PRIMARY KEY NOT NULL ); 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); CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW BEGIN SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual; END; / CREATE INDEX floodmaps_spatial_idx ON floodmaps(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON'); --Hydrologie/Hydr.Grenzen/Linien CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; CREATE TABLE hydr_boundaries ( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, river_id NUMBER(38), name VARCHAR(255), kind NUMBER(38), path VARCHAR(256), id NUMBER PRIMARY KEY NOT NULL ); 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); CREATE OR REPLACE TRIGGER hydr_boundaries_trigger BEFORE INSERT ON hydr_boundaries FOR each ROW BEGIN SELECT HYDR_BOUNDARIES_ID_SEQ.nextval INTO :new.id FROM dual; END; / CREATE INDEX hydr_boundaries_idx ON hydr_boundaries(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; CREATE TABLE hydr_boundaries_poly ( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, river_id NUMBER(38), name VARCHAR(255), kind NUMBER(38), path VARCHAR(256), id NUMBER PRIMARY KEY NOT NULL ); 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); CREATE OR REPLACE TRIGGER hydr_boundaries_poly_trigger BEFORE INSERT ON hydr_boundaries_poly FOR each ROW BEGIN SELECT HYDR_BOUNDARIES_POLY_ID_SEQ.nextval INTO :new.id FROM dual; END; / CREATE INDEX hydr_boundaries_poly_idx ON hydr_boundaries_poly(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON'); -- Hydrologie/Streckendaten/ CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ; CREATE TABLE gauge_location ( OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, river_id NUMBER(38), name VARCHAR(64), path VARCHAR(256), id NUMBER PRIMARY KEY NOT NULL ); 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); CREATE OR REPLACE TRIGGER gauge_location_trigger BEFORE INSERT ON gauge_location FOR EACH ROW BEGIN SELECT GAUGE_LOCATION_ID_SEQ.nextval INTO :new.id FROM dual; END; / CREATE INDEX gauge_location_idx ON gauge_location(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');