Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial.sql @ 5038:57a845da72bd dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix some oracle syntax problems and move indicies to _idx.sql
author Andre Heinecke <aheinecke@intevation.de>
date Wed, 20 Feb 2013 10:44:04 +0100
parents a5f438895a38
children 0fd7720e5c7f
comparison
equal deleted inserted replaced
5037:2837b11cdb13 5038:57a845da72bd
1 WHENEVER SQLERROR EXIT SQL.ERRORCODE ROLLBACK; 1 WHENEVER SQLERROR EXIT;
2 -- Geodaesie/Flussachse+km/achse 2 -- Geodaesie/Flussachse+km/achse
3 CREATE SEQUENCE RIVER_AXES_ID_SEQ; 3 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
4 CREATE TABLE river_axes( 4 CREATE TABLE river_axes(
5 OGR_FID NUMBER(38), 5 OGR_FID NUMBER(38),
6 GEOM MDSYS.SDO_GEOMETRY, 6 GEOM MDSYS.SDO_GEOMETRY,
198 source VARCHAR(256), 198 source VARCHAR(256),
199 status_date TIMESTAMP, 199 status_date TIMESTAMP,
200 description VARCHAR(256) 200 description VARCHAR(256)
201 ); 201 );
202 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws_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); 202 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws_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);
203 CREATE INDEX hws_lines_spatial_idx ON hws_lines(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
204
205 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW 203 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW
206 BEGIN 204 BEGIN
207 SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual; 205 SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual;
208 END; 206 END;
209 207 /
210 -- HWS Points lookup tables 208 -- HWS Points lookup tables
211 CREATE TABLE sectie_kinds ( 209 CREATE TABLE sectie_kinds (
212 id NUMBER PRIMARY KEY NOT NULL, 210 id NUMBER PRIMARY KEY NOT NULL,
213 name VARCHAR(64) NOT NULL 211 name VARCHAR(64) NOT NULL
214 ); 212 );
231 ); 229 );
232 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt'); 230 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
233 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG'); 231 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
234 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land'); 232 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
235 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige'); 233 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
236
237 234
238 -- HWS Points 235 -- HWS Points
239 CREATE SEQUENCE HWS_POINTS_ID_SEQ; 236 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
240 CREATE TABLE hws_points ( 237 CREATE TABLE hws_points (
241 id NUMBER PRIMARY KEY NOT NULL, 238 id NUMBER PRIMARY KEY NOT NULL,
259 z_target NUMBER(19,5), 256 z_target NUMBER(19,5),
260 rated_level NUMBER(19,5) 257 rated_level NUMBER(19,5)
261 ); 258 );
262 259
263 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws_points', '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); 260 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws_points', '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 CREATE INDEX hws_lines_spatial_idx ON hws_pints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT'); 261
265 262 CREATE OR REPLACE TRIGGER hws_points_trigger BEFORE INSERT ON hws_points FOR each ROW
266 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW 263 BEGIN
267 BEGIN 264 SELECT HWS_POINTS_ID_SEQ.nextval INTO :new.id FROM dual;
268 SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual; 265 END;
269 END; 266 /
270
271 267
272 --Hydrologie/UeSG 268 --Hydrologie/UeSG
273 CREATE SEQUENCE FLOODMAPS_ID_SEQ; 269 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
274 CREATE TABLE floodmaps ( 270 CREATE TABLE floodmaps (
275 OGR_FID NUMBER(38), 271 OGR_FID NUMBER(38),
287 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); 283 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);
288 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW 284 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW
289 BEGIN 285 BEGIN
290 SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual; 286 SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual;
291 END; 287 END;
292 288 /
293 CREATE INDEX floodmaps_spatial_idx ON floodmaps(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON');
294
295 289
296 --Hydrologie/Hydr.Grenzen/Linien 290 --Hydrologie/Hydr.Grenzen/Linien
297 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; 291 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
298 CREATE TABLE hydr_boundaries ( 292 CREATE TABLE hydr_boundaries (
299 OGR_FID NUMBER(38), 293 OGR_FID NUMBER(38),
307 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); 301 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);
308 CREATE OR REPLACE TRIGGER hydr_boundaries_trigger BEFORE INSERT ON hydr_boundaries FOR each ROW 302 CREATE OR REPLACE TRIGGER hydr_boundaries_trigger BEFORE INSERT ON hydr_boundaries FOR each ROW
309 BEGIN 303 BEGIN
310 SELECT HYDR_BOUNDARIES_ID_SEQ.nextval INTO :new.id FROM dual; 304 SELECT HYDR_BOUNDARIES_ID_SEQ.nextval INTO :new.id FROM dual;
311 END; 305 END;
312 306 /
313 CREATE INDEX hydr_boundaries_idx ON hydr_boundaries(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
314 307
315 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; 308 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
316 CREATE TABLE hydr_boundaries_poly ( 309 CREATE TABLE hydr_boundaries_poly (
317 OGR_FID NUMBER(38), 310 OGR_FID NUMBER(38),
318 GEOM MDSYS.SDO_GEOMETRY, 311 GEOM MDSYS.SDO_GEOMETRY,
325 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); 318 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);
326 CREATE OR REPLACE TRIGGER hydr_boundaries_poly_trigger BEFORE INSERT ON hydr_boundaries_poly FOR each ROW 319 CREATE OR REPLACE TRIGGER hydr_boundaries_poly_trigger BEFORE INSERT ON hydr_boundaries_poly FOR each ROW
327 BEGIN 320 BEGIN
328 SELECT HYDR_BOUNDARIES_POLY_ID_SEQ.nextval INTO :new.id FROM dual; 321 SELECT HYDR_BOUNDARIES_POLY_ID_SEQ.nextval INTO :new.id FROM dual;
329 END; 322 END;
330 323 /
331 CREATE INDEX hydr_boundaries_poly_idx ON hydr_boundaries_poly(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON');
332
333 324
334 -- Hydrologie/Streckendaten/ 325 -- Hydrologie/Streckendaten/
335 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ; 326 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
336 CREATE TABLE gauge_location ( 327 CREATE TABLE gauge_location (
337 OGR_FID NUMBER(38), 328 OGR_FID NUMBER(38),
344 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); 335 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);
345 CREATE OR REPLACE TRIGGER gauge_location_trigger BEFORE INSERT ON gauge_location FOR EACH ROW 336 CREATE OR REPLACE TRIGGER gauge_location_trigger BEFORE INSERT ON gauge_location FOR EACH ROW
346 BEGIN 337 BEGIN
347 SELECT GAUGE_LOCATION_ID_SEQ.nextval INTO :new.id FROM dual; 338 SELECT GAUGE_LOCATION_ID_SEQ.nextval INTO :new.id FROM dual;
348 END; 339 END;
349 340 /
350 CREATE INDEX gauge_location_idx ON gauge_location(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
http://dive4elements.wald.intevation.org