Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial.sql @ 5540:25c2505df28f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged
author Christian Lins <christian.lins@intevation.de>
date Wed, 03 Apr 2013 16:00:21 +0200
parents 16afd4613f9d
children 5f91881124ba
comparison
equal deleted inserted replaced
5539:8d0af912351c 5540:25c2505df28f
3 CREATE TABLE axis_kinds( 3 CREATE TABLE axis_kinds(
4 id NUMBER PRIMARY KEY NOT NULL, 4 id NUMBER PRIMARY KEY NOT NULL,
5 name VARCHAR(64) 5 name VARCHAR(64)
6 ); 6 );
7 INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt'); 7 INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt');
8 INSERT INTO axis_kinds(id, name) VALUES (1, 'Aktuell'); 8 INSERT INTO axis_kinds(id, name) VALUES (1, 'aktuelle Achse');
9 INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige'); 9 INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige');
10 10
11 -- Geodaesie/Flussachse+km/achse 11 -- Geodaesie/Flussachse+km/achse
12 CREATE SEQUENCE RIVER_AXES_ID_SEQ; 12 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
13 CREATE TABLE river_axes( 13 CREATE TABLE river_axes(
52 CREATE TABLE cross_section_track_kinds( 52 CREATE TABLE cross_section_track_kinds(
53 id NUMBER PRIMARY KEY NOT NULL, 53 id NUMBER PRIMARY KEY NOT NULL,
54 name VARCHAR(64) 54 name VARCHAR(64)
55 ); 55 );
56 INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige'); 56 INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige');
57 INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'Aktuell'); 57 INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'aktuelle Querprofilspuren');
58 58
59 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; 59 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
60 CREATE TABLE cross_section_tracks ( 60 CREATE TABLE cross_section_tracks (
61 OGR_FID NUMBER(38), 61 OGR_FID NUMBER(38),
62 GEOM MDSYS.SDO_GEOMETRY, 62 GEOM MDSYS.SDO_GEOMETRY,
90 CREATE SEQUENCE BUILDINGS_ID_SEQ; 90 CREATE SEQUENCE BUILDINGS_ID_SEQ;
91 CREATE TABLE buildings( 91 CREATE TABLE buildings(
92 OGR_FID NUMBER(38), 92 OGR_FID NUMBER(38),
93 GEOM MDSYS.SDO_GEOMETRY, 93 GEOM MDSYS.SDO_GEOMETRY,
94 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, 94 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
95 kind_id NUMBER(38) REFERENCES building_kinds(id) NOT NULL DEFAULT 0, 95 kind_id NUMBER(38) DEFAULT 0 NOT NULL REFERENCES building_kinds(id),
96 km NUMBER(38,11) NOT NULL, 96 km NUMBER(38,11),
97 name VARCHAR2(255), -- The layername 97 name VARCHAR2(255), -- The layername
98 description VARCHAR(256), -- Name taken from attributes 98 description VARCHAR(256), -- Name taken from attributes
99 path VARCHAR(256), 99 path VARCHAR(256),
100 ID NUMBER PRIMARY KEY NOT NULL 100 ID NUMBER PRIMARY KEY NOT NULL
101 ); 101 );
135 CREATE TABLE floodplain_kinds( 135 CREATE TABLE floodplain_kinds(
136 id NUMBER PRIMARY KEY NOT NULL, 136 id NUMBER PRIMARY KEY NOT NULL,
137 name VARCHAR(64) 137 name VARCHAR(64)
138 ); 138 );
139 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige'); 139 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige');
140 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'Aktuell'); 140 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'aktuelle Talaue');
141 141
142 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; 142 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
143 CREATE TABLE floodplain( 143 CREATE TABLE floodplain(
144 OGR_FID NUMBER(38), 144 OGR_FID NUMBER(38),
145 GEOM MDSYS.SDO_GEOMETRY, 145 GEOM MDSYS.SDO_GEOMETRY,
240 -- HWS Points lookup tables 240 -- HWS Points lookup tables
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, 'Unbekannt'); 245 INSERT INTO sectie_kinds (id, name) VALUES (0, 'SECTIE Unbekannt');
246 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch'); 246 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch');
247 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank'); 247 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank');
248 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich'); 248 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich');
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, 'Unbekannt'); 254 INSERT INTO sobek_kinds (id, name) VALUES (0, 'SOBEK Unbekannt');
255 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend'); 255 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend');
256 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd'); 256 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd');
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,
335 CREATE TABLE hydr_boundaries ( 335 CREATE TABLE hydr_boundaries (
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) REFERENCES boundary_kinds(id), 340 kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id),
341 sectie NUMBER(38) REFERENCES sectie_kinds(id), 341 sectie NUMBER(38) DEFAULT 0 NOT NULL REFERENCES sectie_kinds(id),
342 sobek NUMBER(38) REFERENCES sobek_kinds(id), 342 sobek NUMBER(38) DEFAULT 0 NOT NULL 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
354 CREATE TABLE hydr_boundaries_poly ( 354 CREATE TABLE hydr_boundaries_poly (
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) REFERENCES boundary_kinds(id), 359 kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id),
360 sectie NUMBER(38) REFERENCES sectie_kinds(id), 360 sectie NUMBER(38) DEFAULT 0 NOT NULL REFERENCES sectie_kinds(id),
361 sobek NUMBER(38) REFERENCES sobek_kinds(id), 361 sobek NUMBER(38) DEFAULT 0 NOT NULL 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
410 CREATE OR REPLACE TRIGGER jetties_trigger BEFORE INSERT ON jetties FOR EACH ROW 410 CREATE OR REPLACE TRIGGER jetties_trigger BEFORE INSERT ON jetties FOR EACH ROW
411 BEGIN 411 BEGIN
412 SELECT JETTIES_ID_SEQ.nextval INTO :new.id FROM dual; 412 SELECT JETTIES_ID_SEQ.nextval INTO :new.id FROM dual;
413 END; 413 END;
414 / 414 /
415
416 CREATE SEQUENCE FLOOD_MARKS_ID_SEQ;
417 CREATE TABLE flood_marks (
418 OGR_FID NUMBER(38),
419 GEOM MDSYS.SDO_GEOMETRY,
420 id NUMBER PRIMARY KEY NOT NULL,
421 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
422 path VARCHAR(256),
423 km NUMBER(7,3),
424 z NUMBER(38,12),
425 location VARCHAR(64),
426 year NUMBER(38,0)
427 );
428 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('flood_marks', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001)), 31467);
429 CREATE OR REPLACE TRIGGER flood_marks_trigger BEFORE INSERT ON flood_marks FOR EACH ROW
430 BEGIN
431 SELECT FLOOD_MARKS_ID_SEQ.nextval INTO :new.id FROM dual;
432 END;
433 /
http://dive4elements.wald.intevation.org