Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial.sql @ 5528:33d735f5433e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged.
author Felix Wolfsteller <felix.wolfsteller@intevation.de>
date Tue, 02 Apr 2013 08:58:24 +0200
parents 4c4fed23693f
children 16afd4613f9d
comparison
equal deleted inserted replaced
5527:6dbc111e9aec 5528:33d735f5433e
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,
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) REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0,
341 sectie NUMBER(38) REFERENCES sectie_kinds(id), 341 sectie NUMBER(38) REFERENCES sectie_kinds(id) NOT NULL DEFAULT 0,
342 sobek NUMBER(38) REFERENCES sobek_kinds(id), 342 sobek NUMBER(38) REFERENCES sobek_kinds(id) NOT NULL DEFAULT 0,
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) REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0,
360 sectie NUMBER(38) REFERENCES sectie_kinds(id), 360 sectie NUMBER(38) REFERENCES sectie_kinds(id) NOT NULL DEFAULT 0,
361 sobek NUMBER(38) REFERENCES sobek_kinds(id), 361 sobek NUMBER(38) REFERENCES sobek_kinds(id) NOT NULL DEFAULT 0,
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