dive4elements/river: flys-backend/doc/schema/oracle-spatial.sql annotate

annotate flys-backend/doc/schema/oracle-spatial.sql @ 2338:e69ef36290ae

Harmonized Extent for Saar and Mosel. Some cleanups. flys-backend/trunk@2809 c6561f87-3c4e-4783-a992-168aeb5c3f6f

author	Bjoern Schilberg <bjoern@intevation.de>
date	Thu, 22 Sep 2011 12:10:08 +0000
parents	e1fb4a1e0b4a
children	b28ab244a77d

rev	line source
1244 281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	1 -- Hydrologie/Einzugsgebiete/EZG.shp
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	2 CREATE SEQUENCE CATCHMENT_ID_SEQ;
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	3 CREATE TABLE catchment(
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	4 OGR_FID NUMBER(38),
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	5 GEOM MDSYS.SDO_GEOMETRY,
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	6 river_id NUMBER(38),
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	7 area NUMBER(19,5),
2335 e1fb4a1e0b4a Generalized oracle spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1253 diff changeset	8 name VARCHAR2(255),
1244 281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	9 ID NUMBER PRIMARY KEY NOT NULL
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	10 );
2338 e69ef36290ae Harmonized Extent for Saar and Mosel. Some cleanups. Bjoern Schilberg <bjoern@intevation.de> parents: 2335 diff changeset	11 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('CATCHMENT', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
1244 281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	12
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	13 CREATE TRIGGER catchment_trigger BEFORE INSERT ON catchment FOR each ROW
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	14 BEGIN
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	15 SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual;
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	16 END;
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	17 /
1253 a75e7d922942 Deactivated spatial indexes. Bjoern Schilberg <bjoern@intevation.de> parents: 1251 diff changeset	18 --CREATE INDEX catchment_spatial_idx ON catchment(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=polygon');
1244 281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	19
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	20 -- Geodaesie/Flussachse+km/km.shp
1245 0414181b0859 Fixed table river_axes_km. Bjoern Schilberg <bjoern@intevation.de> parents: 1244 diff changeset	21 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
0414181b0859 Fixed table river_axes_km. Bjoern Schilberg <bjoern@intevation.de> parents: 1244 diff changeset	22 CREATE TABLE river_axes_km(
1244 281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	23 OGR_FID NUMBER(38),
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	24 GEOM MDSYS.SDO_GEOMETRY,
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	25 river_id NUMBER(38),
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	26 km NUMBER(6,3),
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	27 ID NUMBER PRIMARY KEY NOT NULL
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	28 );
2338 e69ef36290ae Harmonized Extent for Saar and Mosel. Some cleanups. Bjoern Schilberg <bjoern@intevation.de> parents: 2335 diff changeset	29 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes_km', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
1245 0414181b0859 Fixed table river_axes_km. Bjoern Schilberg <bjoern@intevation.de> parents: 1244 diff changeset	30 CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW
1244 281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	31 BEGIN
1245 0414181b0859 Fixed table river_axes_km. Bjoern Schilberg <bjoern@intevation.de> parents: 1244 diff changeset	32 SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual;
1244 281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	33 END;
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	34 /
1253 a75e7d922942 Deactivated spatial indexes. Bjoern Schilberg <bjoern@intevation.de> parents: 1251 diff changeset	35 --CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point');
1244 281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	36
1246 e334314e8eb2 Added schema buildings. Bjoern Schilberg <bjoern@intevation.de> parents: 1245 diff changeset	37 -- Geodaesie/Bauwerke/Wehre.shp
e334314e8eb2 Added schema buildings. Bjoern Schilberg <bjoern@intevation.de> parents: 1245 diff changeset	38 CREATE SEQUENCE BUILDINGS_ID_SEQ;
e334314e8eb2 Added schema buildings. Bjoern Schilberg <bjoern@intevation.de> parents: 1245 diff changeset	39 CREATE TABLE buildings(
e334314e8eb2 Added schema buildings. Bjoern Schilberg <bjoern@intevation.de> parents: 1245 diff changeset	40 OGR_FID NUMBER(38),
e334314e8eb2 Added schema buildings. Bjoern Schilberg <bjoern@intevation.de> parents: 1245 diff changeset	41 GEOM MDSYS.SDO_GEOMETRY,
e334314e8eb2 Added schema buildings. Bjoern Schilberg <bjoern@intevation.de> parents: 1245 diff changeset	42 river_id NUMBER(38),
2335 e1fb4a1e0b4a Generalized oracle spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1253 diff changeset	43 name VARCHAR2(255),
1246 e334314e8eb2 Added schema buildings. Bjoern Schilberg <bjoern@intevation.de> parents: 1245 diff changeset	44 ID NUMBER PRIMARY KEY NOT NULL
e334314e8eb2 Added schema buildings. Bjoern Schilberg <bjoern@intevation.de> parents: 1245 diff changeset	45 );
2338 e69ef36290ae Harmonized Extent for Saar and Mosel. Some cleanups. Bjoern Schilberg <bjoern@intevation.de> parents: 2335 diff changeset	46 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('buildings', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
1246 e334314e8eb2 Added schema buildings. Bjoern Schilberg <bjoern@intevation.de> parents: 1245 diff changeset	47 CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW
e334314e8eb2 Added schema buildings. Bjoern Schilberg <bjoern@intevation.de> parents: 1245 diff changeset	48 BEGIN
e334314e8eb2 Added schema buildings. Bjoern Schilberg <bjoern@intevation.de> parents: 1245 diff changeset	49 SELECT BUILDINGS_ID_SEQ.nextval INTO :new.id FROM dual;
e334314e8eb2 Added schema buildings. Bjoern Schilberg <bjoern@intevation.de> parents: 1245 diff changeset	50 END;
e334314e8eb2 Added schema buildings. Bjoern Schilberg <bjoern@intevation.de> parents: 1245 diff changeset	51 /
1253 a75e7d922942 Deactivated spatial indexes. Bjoern Schilberg <bjoern@intevation.de> parents: 1251 diff changeset	52 --CREATE INDEX buildings_spatial_idx ON buildings(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
1246 e334314e8eb2 Added schema buildings. Bjoern Schilberg <bjoern@intevation.de> parents: 1245 diff changeset	53
1247 6c6c5500d72d Added schema fixpoints. Bjoern Schilberg <bjoern@intevation.de> parents: 1246 diff changeset	54 -- Geodaesie/Festpunkte/Festpunkte.shp
6c6c5500d72d Added schema fixpoints. Bjoern Schilberg <bjoern@intevation.de> parents: 1246 diff changeset	55 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
6c6c5500d72d Added schema fixpoints. Bjoern Schilberg <bjoern@intevation.de> parents: 1246 diff changeset	56 CREATE TABLE fixpoints (
6c6c5500d72d Added schema fixpoints. Bjoern Schilberg <bjoern@intevation.de> parents: 1246 diff changeset	57 OGR_FID NUMBER(38),
6c6c5500d72d Added schema fixpoints. Bjoern Schilberg <bjoern@intevation.de> parents: 1246 diff changeset	58 GEOM MDSYS.SDO_GEOMETRY,
6c6c5500d72d Added schema fixpoints. Bjoern Schilberg <bjoern@intevation.de> parents: 1246 diff changeset	59 river_id NUMBER(38),
2335 e1fb4a1e0b4a Generalized oracle spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1253 diff changeset	60 x NUMBER(38,11),
e1fb4a1e0b4a Generalized oracle spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1253 diff changeset	61 y NUMBER(38,11),
e1fb4a1e0b4a Generalized oracle spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1253 diff changeset	62 km NUMBER(38,11) NOT NULL,
e1fb4a1e0b4a Generalized oracle spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1253 diff changeset	63 HPGP VARCHAR2(255),
1247 6c6c5500d72d Added schema fixpoints. Bjoern Schilberg <bjoern@intevation.de> parents: 1246 diff changeset	64 ID NUMBER PRIMARY KEY NOT NULL
6c6c5500d72d Added schema fixpoints. Bjoern Schilberg <bjoern@intevation.de> parents: 1246 diff changeset	65 );
6c6c5500d72d Added schema fixpoints. Bjoern Schilberg <bjoern@intevation.de> parents: 1246 diff changeset	66 --Extent: (2539388.036000, 5450896.688000) - (2575586.296000, 5507370.606000)
2338 e69ef36290ae Harmonized Extent for Saar and Mosel. Some cleanups. Bjoern Schilberg <bjoern@intevation.de> parents: 2335 diff changeset	67 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('fixpoints', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
1247 6c6c5500d72d Added schema fixpoints. Bjoern Schilberg <bjoern@intevation.de> parents: 1246 diff changeset	68 CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW
6c6c5500d72d Added schema fixpoints. Bjoern Schilberg <bjoern@intevation.de> parents: 1246 diff changeset	69 BEGIN
6c6c5500d72d Added schema fixpoints. Bjoern Schilberg <bjoern@intevation.de> parents: 1246 diff changeset	70 SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual;
6c6c5500d72d Added schema fixpoints. Bjoern Schilberg <bjoern@intevation.de> parents: 1246 diff changeset	71 END;
6c6c5500d72d Added schema fixpoints. Bjoern Schilberg <bjoern@intevation.de> parents: 1246 diff changeset	72 /
1253 a75e7d922942 Deactivated spatial indexes. Bjoern Schilberg <bjoern@intevation.de> parents: 1251 diff changeset	73 --CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
1247 6c6c5500d72d Added schema fixpoints. Bjoern Schilberg <bjoern@intevation.de> parents: 1246 diff changeset	74
1248 f36ed659003c Added schema river_axes. Bjoern Schilberg <bjoern@intevation.de> parents: 1247 diff changeset	75 -- Geodaesie/Flussachse+km/achse
f36ed659003c Added schema river_axes. Bjoern Schilberg <bjoern@intevation.de> parents: 1247 diff changeset	76 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
f36ed659003c Added schema river_axes. Bjoern Schilberg <bjoern@intevation.de> parents: 1247 diff changeset	77 CREATE TABLE river_axes(
f36ed659003c Added schema river_axes. Bjoern Schilberg <bjoern@intevation.de> parents: 1247 diff changeset	78 OGR_FID NUMBER(38),
f36ed659003c Added schema river_axes. Bjoern Schilberg <bjoern@intevation.de> parents: 1247 diff changeset	79 GEOM MDSYS.SDO_GEOMETRY,
f36ed659003c Added schema river_axes. Bjoern Schilberg <bjoern@intevation.de> parents: 1247 diff changeset	80 river_id NUMBER(38),
f36ed659003c Added schema river_axes. Bjoern Schilberg <bjoern@intevation.de> parents: 1247 diff changeset	81 kind NUMBER(38) DEFAULT 0 NOT NULL,
f36ed659003c Added schema river_axes. Bjoern Schilberg <bjoern@intevation.de> parents: 1247 diff changeset	82 ID NUMBER PRIMARY KEY NOT NULL
f36ed659003c Added schema river_axes. Bjoern Schilberg <bjoern@intevation.de> parents: 1247 diff changeset	83 );
2338 e69ef36290ae Harmonized Extent for Saar and Mosel. Some cleanups. Bjoern Schilberg <bjoern@intevation.de> parents: 2335 diff changeset	84 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
1248 f36ed659003c Added schema river_axes. Bjoern Schilberg <bjoern@intevation.de> parents: 1247 diff changeset	85 CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW
f36ed659003c Added schema river_axes. Bjoern Schilberg <bjoern@intevation.de> parents: 1247 diff changeset	86 BEGIN
f36ed659003c Added schema river_axes. Bjoern Schilberg <bjoern@intevation.de> parents: 1247 diff changeset	87 SELECT RIVER_AXES_ID_SEQ.nextval INTO :new.id FROM dual;
f36ed659003c Added schema river_axes. Bjoern Schilberg <bjoern@intevation.de> parents: 1247 diff changeset	88 END;
f36ed659003c Added schema river_axes. Bjoern Schilberg <bjoern@intevation.de> parents: 1247 diff changeset	89 /
1253 a75e7d922942 Deactivated spatial indexes. Bjoern Schilberg <bjoern@intevation.de> parents: 1251 diff changeset	90 --CREATE INDEX river_axes_spatial_idx ON river_axes(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
1246 e334314e8eb2 Added schema buildings. Bjoern Schilberg <bjoern@intevation.de> parents: 1245 diff changeset	91
1249 735717961e0b Added schema cross_section_tracks. Bjoern Schilberg <bjoern@intevation.de> parents: 1248 diff changeset	92 --Geodaesie/Querprofile/QP-Spuren/qps.shp
735717961e0b Added schema cross_section_tracks. Bjoern Schilberg <bjoern@intevation.de> parents: 1248 diff changeset	93 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
735717961e0b Added schema cross_section_tracks. Bjoern Schilberg <bjoern@intevation.de> parents: 1248 diff changeset	94 CREATE TABLE cross_section_tracks (
735717961e0b Added schema cross_section_tracks. Bjoern Schilberg <bjoern@intevation.de> parents: 1248 diff changeset	95 OGR_FID NUMBER(38),
735717961e0b Added schema cross_section_tracks. Bjoern Schilberg <bjoern@intevation.de> parents: 1248 diff changeset	96 GEOM MDSYS.SDO_GEOMETRY,
735717961e0b Added schema cross_section_tracks. Bjoern Schilberg <bjoern@intevation.de> parents: 1248 diff changeset	97 river_id NUMBER(38),
2335 e1fb4a1e0b4a Generalized oracle spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1253 diff changeset	98 km NUMBER(38,12) NOT NULL,
2338 e69ef36290ae Harmonized Extent for Saar and Mosel. Some cleanups. Bjoern Schilberg <bjoern@intevation.de> parents: 2335 diff changeset	99 z NUMBER(38,12) DEFAULT 0 NOT NULL,
1249 735717961e0b Added schema cross_section_tracks. Bjoern Schilberg <bjoern@intevation.de> parents: 1248 diff changeset	100 ID NUMBER PRIMARY KEY NOT NULL
735717961e0b Added schema cross_section_tracks. Bjoern Schilberg <bjoern@intevation.de> parents: 1248 diff changeset	101 );
2338 e69ef36290ae Harmonized Extent for Saar and Mosel. Some cleanups. Bjoern Schilberg <bjoern@intevation.de> parents: 2335 diff changeset	102 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('cross_section_tracks', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
1249 735717961e0b Added schema cross_section_tracks. Bjoern Schilberg <bjoern@intevation.de> parents: 1248 diff changeset	103 CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW
735717961e0b Added schema cross_section_tracks. Bjoern Schilberg <bjoern@intevation.de> parents: 1248 diff changeset	104 BEGIN
735717961e0b Added schema cross_section_tracks. Bjoern Schilberg <bjoern@intevation.de> parents: 1248 diff changeset	105 SELECT CROSS_SECTION_TRACKS_ID_SEQ.nextval INTO :new.id FROM dual;
735717961e0b Added schema cross_section_tracks. Bjoern Schilberg <bjoern@intevation.de> parents: 1248 diff changeset	106 END;
735717961e0b Added schema cross_section_tracks. Bjoern Schilberg <bjoern@intevation.de> parents: 1248 diff changeset	107 /
1253 a75e7d922942 Deactivated spatial indexes. Bjoern Schilberg <bjoern@intevation.de> parents: 1251 diff changeset	108 --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
1249 735717961e0b Added schema cross_section_tracks. Bjoern Schilberg <bjoern@intevation.de> parents: 1248 diff changeset	109
1250 660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	110 --Hydrologie/HW-Schutzanlagen/hws.shp
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	111 CREATE SEQUENCE HWS_ID_SEQ;
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	112 CREATE TABLE hws(
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	113 OGR_FID NUMBER(38),
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	114 GEOM MDSYS.SDO_GEOMETRY,
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	115 river_id NUMBER(38),
2335 e1fb4a1e0b4a Generalized oracle spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1253 diff changeset	116 hws_facility VARCHAR2(255),
e1fb4a1e0b4a Generalized oracle spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1253 diff changeset	117 type VARCHAR2(255),
1250 660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	118 ID NUMBER PRIMARY KEY NOT NULL
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	119 );
2338 e69ef36290ae Harmonized Extent for Saar and Mosel. Some cleanups. Bjoern Schilberg <bjoern@intevation.de> parents: 2335 diff changeset	120 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
1250 660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	121 CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	122 BEGIN
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	123 SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual;
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	124 END;
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	125 /
1253 a75e7d922942 Deactivated spatial indexes. Bjoern Schilberg <bjoern@intevation.de> parents: 1251 diff changeset	126 --CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
1250 660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	127
1251 f124c99077b5 Added schema floodplain. Bjoern Schilberg <bjoern@intevation.de> parents: 1250 diff changeset	128 -- Hydrologie/Hydr. Grenzen/talaue.shp
f124c99077b5 Added schema floodplain. Bjoern Schilberg <bjoern@intevation.de> parents: 1250 diff changeset	129 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
f124c99077b5 Added schema floodplain. Bjoern Schilberg <bjoern@intevation.de> parents: 1250 diff changeset	130 CREATE TABLE floodplain(
f124c99077b5 Added schema floodplain. Bjoern Schilberg <bjoern@intevation.de> parents: 1250 diff changeset	131 OGR_FID NUMBER(38),
f124c99077b5 Added schema floodplain. Bjoern Schilberg <bjoern@intevation.de> parents: 1250 diff changeset	132 GEOM MDSYS.SDO_GEOMETRY,
f124c99077b5 Added schema floodplain. Bjoern Schilberg <bjoern@intevation.de> parents: 1250 diff changeset	133 river_id NUMBER(38),
f124c99077b5 Added schema floodplain. Bjoern Schilberg <bjoern@intevation.de> parents: 1250 diff changeset	134 ID NUMBER PRIMARY KEY NOT NULL
f124c99077b5 Added schema floodplain. Bjoern Schilberg <bjoern@intevation.de> parents: 1250 diff changeset	135 );
2338 e69ef36290ae Harmonized Extent for Saar and Mosel. Some cleanups. Bjoern Schilberg <bjoern@intevation.de> parents: 2335 diff changeset	136 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodplain', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
1251 f124c99077b5 Added schema floodplain. Bjoern Schilberg <bjoern@intevation.de> parents: 1250 diff changeset	137 CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW
f124c99077b5 Added schema floodplain. Bjoern Schilberg <bjoern@intevation.de> parents: 1250 diff changeset	138 BEGIN
f124c99077b5 Added schema floodplain. Bjoern Schilberg <bjoern@intevation.de> parents: 1250 diff changeset	139 SELECT FLOODPLAIN_ID_SEQ.nextval INTO :new.id FROM dual;
f124c99077b5 Added schema floodplain. Bjoern Schilberg <bjoern@intevation.de> parents: 1250 diff changeset	140 END;
f124c99077b5 Added schema floodplain. Bjoern Schilberg <bjoern@intevation.de> parents: 1250 diff changeset	141 /
1253 a75e7d922942 Deactivated spatial indexes. Bjoern Schilberg <bjoern@intevation.de> parents: 1251 diff changeset	142 --CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON');

Mercurial > dive4elements > river

annotate flys-backend/doc/schema/oracle-spatial.sql @ 2338:e69ef36290ae