Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial.sql @ 3677:c37effda3655

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature. flys-backend/trunk@5309 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Thu, 30 Aug 2012 09:51:04 +0000
parents 998f1a7dcfde
children 89ada0b9083f
comparison
equal deleted inserted replaced
3676:ae16bd95879e 3677:c37effda3655
4 OGR_FID NUMBER(38), 4 OGR_FID NUMBER(38),
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 name VARCHAR(64), 8 name VARCHAR(64),
9 path VARCHAR(256),
9 ID NUMBER PRIMARY KEY NOT NULL 10 ID NUMBER PRIMARY KEY NOT NULL
10 ); 11 );
11 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); 12 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);
12 CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW 13 CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW
13 BEGIN 14 BEGIN
23 OGR_FID NUMBER(38), 24 OGR_FID NUMBER(38),
24 GEOM MDSYS.SDO_GEOMETRY, 25 GEOM MDSYS.SDO_GEOMETRY,
25 river_id NUMBER(38), 26 river_id NUMBER(38),
26 km NUMBER(6,3), 27 km NUMBER(6,3),
27 name VARCHAR(64), 28 name VARCHAR(64),
29 path VARCHAR(256),
28 ID NUMBER PRIMARY KEY NOT NULL 30 ID NUMBER PRIMARY KEY NOT NULL
29 ); 31 );
30 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); 32 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);
31 CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW 33 CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW
32 BEGIN 34 BEGIN
43 GEOM MDSYS.SDO_GEOMETRY, 45 GEOM MDSYS.SDO_GEOMETRY,
44 river_id NUMBER(38), 46 river_id NUMBER(38),
45 km NUMBER(38,12) NOT NULL, 47 km NUMBER(38,12) NOT NULL,
46 z NUMBER(38,12) DEFAULT 0 NOT NULL, 48 z NUMBER(38,12) DEFAULT 0 NOT NULL,
47 name VARCHAR(64), 49 name VARCHAR(64),
50 path VARCHAR(256),
48 ID NUMBER PRIMARY KEY NOT NULL 51 ID NUMBER PRIMARY KEY NOT NULL
49 ); 52 );
50 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); 53 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);
51 CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW 54 CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW
52 BEGIN 55 BEGIN
64 GEOM MDSYS.SDO_GEOMETRY, 67 GEOM MDSYS.SDO_GEOMETRY,
65 river_id NUMBER(38), 68 river_id NUMBER(38),
66 kind VARCHAR2(16) NOT NULL, 69 kind VARCHAR2(16) NOT NULL,
67 z NUMBER(38,12) DEFAULT 0, 70 z NUMBER(38,12) DEFAULT 0,
68 name VARCHAR(64), 71 name VARCHAR(64),
72 path VARCHAR(256),
69 ID NUMBER PRIMARY KEY NOT NULL 73 ID NUMBER PRIMARY KEY NOT NULL
70 ); 74 );
71 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); 75 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);
72 CREATE OR REPLACE TRIGGER lines_trigger BEFORE INSERT ON lines FOR each ROW 76 CREATE OR REPLACE TRIGGER lines_trigger BEFORE INSERT ON lines FOR each ROW
73 BEGIN 77 BEGIN
87 CREATE TABLE buildings( 91 CREATE TABLE buildings(
88 OGR_FID NUMBER(38), 92 OGR_FID NUMBER(38),
89 GEOM MDSYS.SDO_GEOMETRY, 93 GEOM MDSYS.SDO_GEOMETRY,
90 river_id NUMBER(38), 94 river_id NUMBER(38),
91 name VARCHAR2(255), 95 name VARCHAR2(255),
96 path VARCHAR(256),
92 ID NUMBER PRIMARY KEY NOT NULL 97 ID NUMBER PRIMARY KEY NOT NULL
93 ); 98 );
94 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); 99 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);
95 CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW 100 CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW
96 BEGIN 101 BEGIN
109 x NUMBER(38,11), 114 x NUMBER(38,11),
110 y NUMBER(38,11), 115 y NUMBER(38,11),
111 km NUMBER(38,11) NOT NULL, 116 km NUMBER(38,11) NOT NULL,
112 HPGP VARCHAR2(255), 117 HPGP VARCHAR2(255),
113 name VARCHAR(64), 118 name VARCHAR(64),
119 path VARCHAR(256),
114 ID NUMBER PRIMARY KEY NOT NULL 120 ID NUMBER PRIMARY KEY NOT NULL
115 ); 121 );
116 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); 122 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);
117 CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW 123 CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW
118 BEGIN 124 BEGIN
127 CREATE TABLE floodplain( 133 CREATE TABLE floodplain(
128 OGR_FID NUMBER(38), 134 OGR_FID NUMBER(38),
129 GEOM MDSYS.SDO_GEOMETRY, 135 GEOM MDSYS.SDO_GEOMETRY,
130 river_id NUMBER(38), 136 river_id NUMBER(38),
131 name VARCHAR(64), 137 name VARCHAR(64),
138 path VARCHAR(256),
132 ID NUMBER PRIMARY KEY NOT NULL 139 ID NUMBER PRIMARY KEY NOT NULL
133 ); 140 );
134 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); 141 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);
135 CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW 142 CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW
136 BEGIN 143 BEGIN
166 OGR_FID NUMBER(38), 173 OGR_FID NUMBER(38),
167 GEOM MDSYS.SDO_GEOMETRY, 174 GEOM MDSYS.SDO_GEOMETRY,
168 river_id NUMBER(38), 175 river_id NUMBER(38),
169 area NUMBER(19,5), 176 area NUMBER(19,5),
170 name VARCHAR2(255), 177 name VARCHAR2(255),
178 path VARCHAR(256),
171 ID NUMBER PRIMARY KEY NOT NULL 179 ID NUMBER PRIMARY KEY NOT NULL
172 ); 180 );
173 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); 181 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);
174 182
175 CREATE TRIGGER catchment_trigger BEFORE INSERT ON catchment FOR each ROW 183 CREATE TRIGGER catchment_trigger BEFORE INSERT ON catchment FOR each ROW
186 GEOM MDSYS.SDO_GEOMETRY, 194 GEOM MDSYS.SDO_GEOMETRY,
187 river_id NUMBER(38), 195 river_id NUMBER(38),
188 hws_facility VARCHAR2(255), 196 hws_facility VARCHAR2(255),
189 type VARCHAR2(255), 197 type VARCHAR2(255),
190 name VARCHAR(64), 198 name VARCHAR(64),
199 path VARCHAR(256),
191 ID NUMBER PRIMARY KEY NOT NULL 200 ID NUMBER PRIMARY KEY NOT NULL
192 ); 201 );
193 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); 202 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);
194 CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW 203 CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW
195 BEGIN 204 BEGIN
209 kind NUMBER(38), 218 kind NUMBER(38),
210 diff NUMBER(19,5), 219 diff NUMBER(19,5),
211 count NUMBER(38), 220 count NUMBER(38),
212 area NUMBER(19,5), 221 area NUMBER(19,5),
213 perimeter NUMBER(19,5), 222 perimeter NUMBER(19,5),
223 path VARCHAR(256),
214 id NUMBER PRIMARY KEY NOT NULL 224 id NUMBER PRIMARY KEY NOT NULL
215 ); 225 );
216 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); 226 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);
217 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW 227 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW
218 BEGIN 228 BEGIN
228 OGR_FID NUMBER(38), 238 OGR_FID NUMBER(38),
229 GEOM MDSYS.SDO_GEOMETRY, 239 GEOM MDSYS.SDO_GEOMETRY,
230 river_id NUMBER(38), 240 river_id NUMBER(38),
231 name VARCHAR(255), 241 name VARCHAR(255),
232 kind NUMBER(38), 242 kind NUMBER(38),
243 path VARCHAR(256),
233 id NUMBER PRIMARY KEY NOT NULL 244 id NUMBER PRIMARY KEY NOT NULL
234 ); 245 );
235 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); 246 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);
236 CREATE OR REPLACE TRIGGER hydr_boundaries_trigger BEFORE INSERT ON hydr_boundaries FOR each ROW 247 CREATE OR REPLACE TRIGGER hydr_boundaries_trigger BEFORE INSERT ON hydr_boundaries FOR each ROW
237 BEGIN 248 BEGIN
245 OGR_FID NUMBER(38), 256 OGR_FID NUMBER(38),
246 GEOM MDSYS.SDO_GEOMETRY, 257 GEOM MDSYS.SDO_GEOMETRY,
247 river_id NUMBER(38), 258 river_id NUMBER(38),
248 name VARCHAR(255), 259 name VARCHAR(255),
249 kind NUMBER(38), 260 kind NUMBER(38),
261 path VARCHAR(256),
250 id NUMBER PRIMARY KEY NOT NULL 262 id NUMBER PRIMARY KEY NOT NULL
251 ); 263 );
252 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); 264 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);
253 CREATE OR REPLACE TRIGGER hydr_boundaries_poly_trigger BEFORE INSERT ON hydr_boundaries_poly FOR each ROW 265 CREATE OR REPLACE TRIGGER hydr_boundaries_poly_trigger BEFORE INSERT ON hydr_boundaries_poly FOR each ROW
254 BEGIN 266 BEGIN
263 CREATE TABLE gauge_location ( 275 CREATE TABLE gauge_location (
264 OGR_FID NUMBER(38), 276 OGR_FID NUMBER(38),
265 GEOM MDSYS.SDO_GEOMETRY, 277 GEOM MDSYS.SDO_GEOMETRY,
266 river_id NUMBER(38), 278 river_id NUMBER(38),
267 name VARCHAR(64), 279 name VARCHAR(64),
280 path VARCHAR(256),
268 id NUMBER PRIMARY KEY NOT NULL 281 id NUMBER PRIMARY KEY NOT NULL
269 ); 282 );
270 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); 283 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);
271 CREATE OR REPLACE TRIGGER gauge_location_trigger BEFORE INSERT ON gauge_location FOR EACH ROW 284 CREATE OR REPLACE TRIGGER gauge_location_trigger BEFORE INSERT ON gauge_location FOR EACH ROW
272 BEGIN 285 BEGIN
http://dive4elements.wald.intevation.org