Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial.sql @ 2370:7d378970d764

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
doc/schema/oracle-spatial.sql: Adjust extent of germany to EPSG:31467 (GK3) coordinates. flys-backend/trunk@3342 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Bjoern Schilberg <bjoern@intevation.de>
date Wed, 30 Nov 2011 12:14:41 +0000
parents a3c02addd1fa
children f3b4e32b5db6
comparison
equal deleted inserted replaced
2369:a3c02addd1fa 2370:7d378970d764
5 GEOM MDSYS.SDO_GEOMETRY, 5 GEOM MDSYS.SDO_GEOMETRY,
6 river_id NUMBER(38), 6 river_id NUMBER(38),
7 kind NUMBER(38) DEFAULT 0 NOT NULL, 7 kind NUMBER(38) DEFAULT 0 NOT NULL,
8 ID NUMBER PRIMARY KEY NOT NULL 8 ID NUMBER PRIMARY KEY NOT NULL
9 ); 9 );
10 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2500000,5200000,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5200000,6100000,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); 10 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);
11 CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW 11 CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW
12 BEGIN 12 BEGIN
13 SELECT RIVER_AXES_ID_SEQ.nextval INTO :new.id FROM dual; 13 SELECT RIVER_AXES_ID_SEQ.nextval INTO :new.id FROM dual;
14 END; 14 END;
15 / 15 /
23 GEOM MDSYS.SDO_GEOMETRY, 23 GEOM MDSYS.SDO_GEOMETRY,
24 river_id NUMBER(38), 24 river_id NUMBER(38),
25 km NUMBER(6,3), 25 km NUMBER(6,3),
26 ID NUMBER PRIMARY KEY NOT NULL 26 ID NUMBER PRIMARY KEY NOT NULL
27 ); 27 );
28 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes_km', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2500000,5200000,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5200000,6100000,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); 28 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);
29 CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW 29 CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW
30 BEGIN 30 BEGIN
31 SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual; 31 SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual;
32 END; 32 END;
33 / 33 /
42 river_id NUMBER(38), 42 river_id NUMBER(38),
43 km NUMBER(38,12) NOT NULL, 43 km NUMBER(38,12) NOT NULL,
44 z NUMBER(38,12) DEFAULT 0 NOT NULL, 44 z NUMBER(38,12) DEFAULT 0 NOT NULL,
45 ID NUMBER PRIMARY KEY NOT NULL 45 ID NUMBER PRIMARY KEY NOT NULL
46 ); 46 );
47 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('cross_section_tracks', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2500000,5200000,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5200000,6100000,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); 47 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);
48 CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW 48 CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW
49 BEGIN 49 BEGIN
50 SELECT CROSS_SECTION_TRACKS_ID_SEQ.nextval INTO :new.id FROM dual; 50 SELECT CROSS_SECTION_TRACKS_ID_SEQ.nextval INTO :new.id FROM dual;
51 END; 51 END;
52 / 52 /
62 river_id NUMBER(38), 62 river_id NUMBER(38),
63 kind VARCHAR2(16) NOT NULL, 63 kind VARCHAR2(16) NOT NULL,
64 z NUMBER(38,12) DEFAULT 0, 64 z NUMBER(38,12) DEFAULT 0,
65 ID NUMBER PRIMARY KEY NOT NULL 65 ID NUMBER PRIMARY KEY NOT NULL
66 ); 66 );
67 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('lines', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2500000,5200000,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5200000,6100000,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); 67 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);
68 CREATE OR REPLACE TRIGGER lines_trigger BEFORE INSERT ON lines FOR each ROW 68 CREATE OR REPLACE TRIGGER lines_trigger BEFORE INSERT ON lines FOR each ROW
69 BEGIN 69 BEGIN
70 SELECT LINES_ID_SEQ.nextval INTO :new.id FROM dual; 70 SELECT LINES_ID_SEQ.nextval INTO :new.id FROM dual;
71 END; 71 END;
72 / 72 /
85 GEOM MDSYS.SDO_GEOMETRY, 85 GEOM MDSYS.SDO_GEOMETRY,
86 river_id NUMBER(38), 86 river_id NUMBER(38),
87 name VARCHAR2(255), 87 name VARCHAR2(255),
88 ID NUMBER PRIMARY KEY NOT NULL 88 ID NUMBER PRIMARY KEY NOT NULL
89 ); 89 );
90 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('buildings', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2500000,5200000,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5200000,6100000,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); 90 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);
91 CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW 91 CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW
92 BEGIN 92 BEGIN
93 SELECT BUILDINGS_ID_SEQ.nextval INTO :new.id FROM dual; 93 SELECT BUILDINGS_ID_SEQ.nextval INTO :new.id FROM dual;
94 END; 94 END;
95 / 95 /
106 y NUMBER(38,11), 106 y NUMBER(38,11),
107 km NUMBER(38,11) NOT NULL, 107 km NUMBER(38,11) NOT NULL,
108 HPGP VARCHAR2(255), 108 HPGP VARCHAR2(255),
109 ID NUMBER PRIMARY KEY NOT NULL 109 ID NUMBER PRIMARY KEY NOT NULL
110 ); 110 );
111 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('fixpoints', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2500000,5200000,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5200000,6100000,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); 111 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);
112 CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW 112 CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW
113 BEGIN 113 BEGIN
114 SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual; 114 SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual;
115 END; 115 END;
116 / 116 /
123 OGR_FID NUMBER(38), 123 OGR_FID NUMBER(38),
124 GEOM MDSYS.SDO_GEOMETRY, 124 GEOM MDSYS.SDO_GEOMETRY,
125 river_id NUMBER(38), 125 river_id NUMBER(38),
126 ID NUMBER PRIMARY KEY NOT NULL 126 ID NUMBER PRIMARY KEY NOT NULL
127 ); 127 );
128 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodplain', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2500000,5200000,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5200000,6100000,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); 128 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);
129 CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW 129 CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW
130 BEGIN 130 BEGIN
131 SELECT FLOODPLAIN_ID_SEQ.nextval INTO :new.id FROM dual; 131 SELECT FLOODPLAIN_ID_SEQ.nextval INTO :new.id FROM dual;
132 END; 132 END;
133 / 133 /
162 river_id NUMBER(38), 162 river_id NUMBER(38),
163 area NUMBER(19,5), 163 area NUMBER(19,5),
164 name VARCHAR2(255), 164 name VARCHAR2(255),
165 ID NUMBER PRIMARY KEY NOT NULL 165 ID NUMBER PRIMARY KEY NOT NULL
166 ); 166 );
167 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('CATCHMENT', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2500000,5200000,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5200000,6100000,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); 167 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);
168 168
169 CREATE TRIGGER catchment_trigger BEFORE INSERT ON catchment FOR each ROW 169 CREATE TRIGGER catchment_trigger BEFORE INSERT ON catchment FOR each ROW
170 BEGIN 170 BEGIN
171 SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual; 171 SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual;
172 END; 172 END;
181 river_id NUMBER(38), 181 river_id NUMBER(38),
182 hws_facility VARCHAR2(255), 182 hws_facility VARCHAR2(255),
183 type VARCHAR2(255), 183 type VARCHAR2(255),
184 ID NUMBER PRIMARY KEY NOT NULL 184 ID NUMBER PRIMARY KEY NOT NULL
185 ); 185 );
186 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2500000,5200000,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5200000,6100000,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); 186 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);
187 CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW 187 CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW
188 BEGIN 188 BEGIN
189 SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual; 189 SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual;
190 END; 190 END;
191 / 191 /
http://dive4elements.wald.intevation.org