view flys-backend/doc/schema/oracle-spatial.sql @ 4269:0c766c475805

Add Panel and Tree UI classes for dispayling the measurement station info The new ui classes are using the new extracted base class InfoPanel and InfoTree which are in common with the gauge info.
author Björn Ricks <bjoern.ricks@intevation.de>
date Fri, 26 Oct 2012 12:22:06 +0200
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');

http://dive4elements.wald.intevation.org