Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial.sql @ 5748:b5553164fabf

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
author Tom Gottfried <tom.gottfried@intevation.de>
date Wed, 17 Apr 2013 19:30:10 +0200
parents 0aaed46a88be
children 153456f84602
comparison
equal deleted inserted replaced
5747:09465f534c86 5748:b5553164fabf
235 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW 235 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW
236 BEGIN 236 BEGIN
237 SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual; 237 SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual;
238 END; 238 END;
239 / 239 /
240 -- HWS Points lookup tables 240
241 CREATE TABLE sectie_kinds ( 241 CREATE TABLE sectie_kinds (
242 id NUMBER PRIMARY KEY NOT NULL, 242 id NUMBER PRIMARY KEY NOT NULL,
243 name VARCHAR(64) NOT NULL 243 name VARCHAR(64) NOT NULL
244 ); 244 );
245 INSERT INTO sectie_kinds (id, name) VALUES (0, 'SECTIE Unbekannt'); 245 INSERT INTO sectie_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
246 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch'); 246 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Hauptgerinne');
247 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank'); 247 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbereich');
248 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich'); 248 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Vorland');
249 249
250 CREATE TABLE sobek_kinds ( 250 CREATE TABLE sobek_kinds (
251 id NUMBER PRIMARY KEY NOT NULL, 251 id NUMBER PRIMARY KEY NOT NULL,
252 name VARCHAR(64) NOT NULL 252 name VARCHAR(64) NOT NULL
253 ); 253 );
254 INSERT INTO sobek_kinds (id, name) VALUES (0, 'SOBEK Unbekannt'); 254 INSERT INTO sobek_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
255 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend'); 255 INSERT INTO sobek_kinds (id, name) VALUES (1, 'durchströmt');
256 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd'); 256 INSERT INTO sobek_kinds (id, name) VALUES (2, 'nicht durchströmt');
257 257
258 CREATE TABLE boundary_kinds ( 258 CREATE TABLE boundary_kinds (
259 id NUMBER PRIMARY KEY NOT NULL, 259 id NUMBER PRIMARY KEY NOT NULL,
260 name VARCHAR(64) NOT NULL 260 name VARCHAR(64) NOT NULL
261 ); 261 );
336 OGR_FID NUMBER(38), 336 OGR_FID NUMBER(38),
337 GEOM MDSYS.SDO_GEOMETRY, 337 GEOM MDSYS.SDO_GEOMETRY,
338 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, 338 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
339 name VARCHAR(255), 339 name VARCHAR(255),
340 kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id), 340 kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id),
341 sectie NUMBER(38) DEFAULT 0 NOT NULL REFERENCES sectie_kinds(id), 341 sectie NUMBER(38) REFERENCES sectie_kinds(id),
342 sobek NUMBER(38) DEFAULT 0 NOT NULL REFERENCES sobek_kinds(id), 342 sobek NUMBER(38) REFERENCES sobek_kinds(id),
343 path VARCHAR(256), 343 path VARCHAR(256),
344 id NUMBER PRIMARY KEY NOT NULL 344 id NUMBER PRIMARY KEY NOT NULL
345 ); 345 );
346 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); 346 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);
347 CREATE OR REPLACE TRIGGER hydr_boundaries_trigger BEFORE INSERT ON hydr_boundaries FOR each ROW 347 CREATE OR REPLACE TRIGGER hydr_boundaries_trigger BEFORE INSERT ON hydr_boundaries FOR each ROW
355 OGR_FID NUMBER(38), 355 OGR_FID NUMBER(38),
356 GEOM MDSYS.SDO_GEOMETRY, 356 GEOM MDSYS.SDO_GEOMETRY,
357 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, 357 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
358 name VARCHAR(255), 358 name VARCHAR(255),
359 kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id), 359 kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id),
360 sectie NUMBER(38) DEFAULT 0 NOT NULL REFERENCES sectie_kinds(id), 360 sectie NUMBER(38) REFERENCES sectie_kinds(id),
361 sobek NUMBER(38) DEFAULT 0 NOT NULL REFERENCES sobek_kinds(id), 361 sobek NUMBER(38) REFERENCES sobek_kinds(id),
362 path VARCHAR(256), 362 path VARCHAR(256),
363 id NUMBER PRIMARY KEY NOT NULL 363 id NUMBER PRIMARY KEY NOT NULL
364 ); 364 );
365 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); 365 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);
366 CREATE OR REPLACE TRIGGER hydr_boundaries_poly_trigger BEFORE INSERT ON hydr_boundaries_poly FOR each ROW 366 CREATE OR REPLACE TRIGGER hydr_boundaries_poly_trigger BEFORE INSERT ON hydr_boundaries_poly FOR each ROW
http://dive4elements.wald.intevation.org