Mercurial > dive4elements > river

comparison backend/doc/schema/oracle-spatial.sql @ 5838:5aa05a7a34b7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Rename modules to more fitting names.
author Sascha L. Teichmann <teichmann@intevation.de>
date Thu, 25 Apr 2013 15:23:37 +0200
parents flys-backend/doc/schema/oracle-spatial.sql@153456f84602
children 249b8766a178
comparison
equal deleted inserted replaced
5837:d9901a08d0a6 5838:5aa05a7a34b7
1 WHENEVER SQLERROR EXIT;
2
3 CREATE TABLE axis_kinds(
4 id NUMBER PRIMARY KEY NOT NULL,
5 name VARCHAR(64)
6 );
7 INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt');
8 INSERT INTO axis_kinds(id, name) VALUES (1, 'aktuelle Achse');
9 INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige');
10
11 -- Geodaesie/Flussachse+km/achse
12 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
13 CREATE TABLE river_axes(
14 OGR_FID NUMBER(38),
15 GEOM MDSYS.SDO_GEOMETRY,
16 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
17 kind_id NUMBER(38) REFERENCES axis_kinds(id) NOT NULL DEFAULT 0,
18 name VARCHAR(64),
19 path VARCHAR(256),
20 ID NUMBER PRIMARY KEY NOT NULL
21 );
22 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);
23 CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW
24 BEGIN
25 SELECT RIVER_AXES_ID_SEQ.nextval INTO :new.id FROM dual;
26 END;
27 /
28 --CREATE INDEX river_axes_spatial_idx ON river_axes(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
29
30
31 -- Geodaesie/Flussachse+km/km.shp
32 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
33 CREATE TABLE river_axes_km(
34 OGR_FID NUMBER(38),
35 GEOM MDSYS.SDO_GEOMETRY,
36 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
37 km NUMBER(7,3) NOT NULL,
38 name VARCHAR(64),
39 path VARCHAR(256),
40 ID NUMBER PRIMARY KEY NOT NULL
41 );
42 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)), 31467);
43 CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW
44 BEGIN
45 SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual;
46 END;
47 /
48 --CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point');
49
50
51 --Geodaesie/Querprofile/QP-Spuren/qps.shp
52 CREATE TABLE cross_section_track_kinds(
53 id NUMBER PRIMARY KEY NOT NULL,
54 name VARCHAR(64)
55 );
56 INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige');
57 INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'aktuelle Querprofilspuren');
58
59 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
60 CREATE TABLE cross_section_tracks (
61 OGR_FID NUMBER(38),
62 GEOM MDSYS.SDO_GEOMETRY,
63 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
64 kind_id NUMBER(38) REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0,
65 km NUMBER(38,12) NOT NULL,
66 z NUMBER(38,12) DEFAULT 0 NOT NULL,
67 name VARCHAR(64),
68 path VARCHAR(256),
69 ID NUMBER PRIMARY KEY NOT NULL
70 );
71 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);
72 CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW
73 BEGIN
74 SELECT CROSS_SECTION_TRACKS_ID_SEQ.nextval INTO :new.id FROM dual;
75 END;
76 /
77 --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
78
79
80 CREATE TABLE building_kinds(
81 id NUMBER PRIMARY KEY NOT NULL,
82 name VARCHAR(64)
83 );
84 INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige');
85 INSERT INTO building_kinds(id, name) VALUES (1, 'Brücken');
86 INSERT INTO building_kinds(id, name) VALUES (2, 'Wehre');
87 INSERT INTO building_kinds(id, name) VALUES (3, 'Pegel');
88
89 -- Geodaesie/Bauwerke
90 CREATE SEQUENCE BUILDINGS_ID_SEQ;
91 CREATE TABLE buildings(
92 OGR_FID NUMBER(38),
93 GEOM MDSYS.SDO_GEOMETRY,
94 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
95 kind_id NUMBER(38) DEFAULT 0 NOT NULL REFERENCES building_kinds(id),
96 km NUMBER(38,11),
97 name VARCHAR2(255), -- The layername
98 description VARCHAR(256), -- Name taken from attributes
99 path VARCHAR(256),
100 ID NUMBER PRIMARY KEY NOT NULL
101 );
102 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);
103 CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW
104 BEGIN
105 SELECT BUILDINGS_ID_SEQ.nextval INTO :new.id FROM dual;
106 END;
107 /
108 --CREATE INDEX buildings_spatial_idx ON buildings(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
109
110
111 -- Geodaesie/Festpunkte/Festpunkte.shp
112 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
113 CREATE TABLE fixpoints (
114 OGR_FID NUMBER(38),
115 GEOM MDSYS.SDO_GEOMETRY,
116 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
117 x NUMBER(38,11),
118 y NUMBER(38,11),
119 km NUMBER(38,11) NOT NULL,
120 HPGP VARCHAR(64),
121 name VARCHAR(64),
122 path VARCHAR(256),
123 ID NUMBER PRIMARY KEY NOT NULL
124 );
125 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)), 31467);
126 CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW
127 BEGIN
128 SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual;
129 END;
130 /
131 --CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
132
133
134 -- Hydrologie/Hydr. Grenzen/talaue.shp
135 CREATE TABLE floodplain_kinds(
136 id NUMBER PRIMARY KEY NOT NULL,
137 name VARCHAR(64)
138 );
139 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige');
140 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'aktuelle Talaue');
141
142 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
143 CREATE TABLE floodplain(
144 OGR_FID NUMBER(38),
145 GEOM MDSYS.SDO_GEOMETRY,
146 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
147 kind_id NUMBER(38) REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0,
148 name VARCHAR(64),
149 path VARCHAR(256),
150 ID NUMBER PRIMARY KEY NOT NULL
151 );
152 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);
153 CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW
154 BEGIN
155 SELECT FLOODPLAIN_ID_SEQ.nextval INTO :new.id FROM dual;
156 END;
157 /
158 --CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON');
159
160
161 -- Geodaesie/Hoehenmodelle/*
162 CREATE SEQUENCE DEM_ID_SEQ;
163 CREATE TABLE dem (
164 ID NUMBER PRIMARY KEY NOT NULL,
165 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
166 name VARCHAR(64),
167 range_id NUMBER(38) REFERENCES ranges(id),
168 time_interval_id NUMBER(38) REFERENCES time_intervals(id),
169 projection VARCHAR(32),
170 elevation_state VARCHAR(32),
171 srid NUMBER NOT NULL,
172 format VARCHAR(32),
173 border_break NUMBER(1) DEFAULT 0 NOT NULL,
174 resolution VARCHAR(16),
175 description VARCHAR(256),
176 path VARCHAR(256) NOT NULL
177 );
178 CREATE OR REPLACE TRIGGER dem_trigger BEFORE INSERT ON dem FOR each ROW
179 BEGIN
180 SELECT DEM_ID_SEQ.nextval INTO :new.id FROM dual;
181 END;
182 /
183
184 --Static lookup tables for Hochwasserschutzanlagen
185 CREATE TABLE hws_kinds (
186 id NUMBER PRIMARY KEY NOT NULL,
187 kind VARCHAR(64) NOT NULL
188 );
189 INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass');
190 INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm');
191 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
192
193 CREATE TABLE fed_states (
194 id NUMBER PRIMARY KEY NOT NULL,
195 name VARCHAR(23) NOT NULL
196 );
197 INSERT INTO fed_states (id, name) VALUES (1, 'Bayern');
198 INSERT INTO fed_states (id, name) VALUES (2, 'Hessen');
199 INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen');
200 INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen');
201 INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz');
202 INSERT INTO fed_states (id, name) VALUES (6, 'Saarland');
203 INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein');
204 INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg');
205 INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern');
206 INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen');
207 INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg');
208 INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt');
209 INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen');
210 INSERT INTO fed_states (id, name) VALUES (14, 'Berlin');
211 INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
212 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
213
214 --Hydrologie/HW-Schutzanlagen/hws.shp
215 -- HWS-Lines
216 CREATE SEQUENCE HWS_LINES_ID_SEQ;
217 CREATE TABLE hws_lines (
218 OGR_FID NUMBER(38),
219 GEOM MDSYS.SDO_GEOMETRY,
220 kind_id NUMBER(2) DEFAULT 2 REFERENCES hws_kinds(id),
221 fed_state_id NUMBER(2) REFERENCES fed_states(id),
222 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
223 name VARCHAR(256),
224 path VARCHAR(256),
225 official NUMBER DEFAULT 0,
226 agency VARCHAR(256),
227 range VARCHAR(256),
228 shore_side NUMBER DEFAULT 0,
229 source VARCHAR(256),
230 status_date TIMESTAMP,
231 description VARCHAR(256),
232 id NUMBER PRIMARY KEY NOT NULL
233 );
234 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);
235 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW
236 BEGIN
237 SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual;
238 END;
239 /
240
241 CREATE TABLE sectie_kinds (
242 id NUMBER PRIMARY KEY NOT NULL,
243 name VARCHAR(64) NOT NULL
244 );
245 INSERT INTO sectie_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
246 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Hauptgerinne');
247 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbereich');
248 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Vorland');
249
250 CREATE TABLE sobek_kinds (
251 id NUMBER PRIMARY KEY NOT NULL,
252 name VARCHAR(64) NOT NULL
253 );
254 INSERT INTO sobek_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
255 INSERT INTO sobek_kinds (id, name) VALUES (1, 'durchströmt');
256 INSERT INTO sobek_kinds (id, name) VALUES (2, 'nicht durchströmt');
257
258 CREATE TABLE boundary_kinds (
259 id NUMBER PRIMARY KEY NOT NULL,
260 name VARCHAR(64) NOT NULL
261 );
262 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
263 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
264 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
265 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
266
267 -- HWS Points
268 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
269 CREATE TABLE hws_points (
270 OGR_FID NUMBER(38),
271 GEOM MDSYS.SDO_GEOMETRY,
272 kind_id NUMBER DEFAULT 2 REFERENCES hws_kinds(id),
273 fed_state_id NUMBER REFERENCES fed_states(id),
274 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
275 name VARCHAR(256),
276 path VARCHAR(256),
277 official NUMBER DEFAULT 0,
278 agency VARCHAR(256),
279 range VARCHAR(256),
280 shore_side NUMBER DEFAULT 0,
281 source VARCHAR(256),
282 status_date VARCHAR(256),
283 description VARCHAR(256),
284 freeboard NUMBER(19,5),
285 dike_km NUMBER(19,5),
286 z NUMBER(19,5),
287 z_target NUMBER(19,5),
288 rated_level NUMBER(19,5),
289 id NUMBER PRIMARY KEY NOT NULL
290 );
291
292 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);
293
294 CREATE OR REPLACE TRIGGER hws_points_trigger BEFORE INSERT ON hws_points FOR each ROW
295 BEGIN
296 SELECT HWS_POINTS_ID_SEQ.nextval INTO :new.id FROM dual;
297 END;
298 /
299
300 --Hydrologie/UeSG
301 CREATE TABLE floodmap_kinds (
302 id NUMBER PRIMARY KEY NOT NULL,
303 name varchar(64) NOT NULL
304 );
305 INSERT INTO floodmap_kinds VALUES (200, 'Messung');
306 INSERT INTO floodmap_kinds VALUES (111, 'Berechnung-Aktuell-BfG');
307 INSERT INTO floodmap_kinds VALUES (112, 'Berechnung-Aktuell-Bundesländer');
308 INSERT INTO floodmap_kinds VALUES (121, 'Berechnung-Potenziell-BfG');
309 INSERT INTO floodmap_kinds VALUES (122, 'Berechnung-Potenziell-Bundesländer');
310
311 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
312 CREATE TABLE floodmaps (
313 OGR_FID NUMBER(38),
314 GEOM MDSYS.SDO_GEOMETRY,
315 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
316 name VARCHAR(255) NOT NULL,
317 kind NUMBER NOT NULL REFERENCES floodmap_kinds(id),
318 diff NUMBER(19,5),
319 count NUMBER(38),
320 area NUMBER(19,5),
321 perimeter NUMBER(19,5),
322 path VARCHAR(256),
323 source varchar(64),
324 id NUMBER PRIMARY KEY NOT NULL
325 );
326 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);
327 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW
328 BEGIN
329 SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual;
330 END;
331 /
332
333 --Hydrologie/Hydr.Grenzen/Linien
334 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
335 CREATE TABLE hydr_boundaries (
336 OGR_FID NUMBER(38),
337 GEOM MDSYS.SDO_GEOMETRY,
338 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
339 name VARCHAR(255),
340 kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id),
341 sectie NUMBER(38) REFERENCES sectie_kinds(id),
342 sobek NUMBER(38) REFERENCES sobek_kinds(id),
343 path VARCHAR(256),
344 id NUMBER PRIMARY KEY NOT NULL
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);
347 CREATE OR REPLACE TRIGGER hydr_boundaries_trigger BEFORE INSERT ON hydr_boundaries FOR each ROW
348 BEGIN
349 SELECT HYDR_BOUNDARIES_ID_SEQ.nextval INTO :new.id FROM dual;
350 END;
351 /
352
353 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
354 CREATE TABLE hydr_boundaries_poly (
355 OGR_FID NUMBER(38),
356 GEOM MDSYS.SDO_GEOMETRY,
357 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
358 name VARCHAR(255),
359 kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id),
360 sectie NUMBER(38) REFERENCES sectie_kinds(id),
361 sobek NUMBER(38) REFERENCES sobek_kinds(id),
362 path VARCHAR(256),
363 id NUMBER PRIMARY KEY NOT NULL
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);
366 CREATE OR REPLACE TRIGGER hydr_boundaries_poly_trigger BEFORE INSERT ON hydr_boundaries_poly FOR each ROW
367 BEGIN
368 SELECT HYDR_BOUNDARIES_POLY_ID_SEQ.nextval INTO :new.id FROM dual;
369 END;
370 /
371
372 -- Hydrologie/Streckendaten/
373 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
374 CREATE TABLE gauge_location (
375 OGR_FID NUMBER(38),
376 GEOM MDSYS.SDO_GEOMETRY,
377 river_id NUMBER(38),
378 name VARCHAR(64),
379 path VARCHAR(256),
380 id NUMBER PRIMARY KEY NOT NULL
381 );
382 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);
383 CREATE OR REPLACE TRIGGER gauge_location_trigger BEFORE INSERT ON gauge_location FOR EACH ROW
384 BEGIN
385 SELECT GAUGE_LOCATION_ID_SEQ.nextval INTO :new.id FROM dual;
386 END;
387 /
388
389
390 CREATE TABLE jetty_kinds(
391 id NUMBER PRIMARY KEY NOT NULL,
392 name VARCHAR(64)
393 );
394 INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf');
395 INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß');
396 INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel');
397
398 CREATE SEQUENCE JETTIES_ID_SEQ;
399 CREATE TABLE jetties (
400 OGR_FID NUMBER(38),
401 GEOM MDSYS.SDO_GEOMETRY,
402 id NUMBER PRIMARY KEY NOT NULL,
403 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
404 path VARCHAR(256),
405 kind_id NUMBER(38) REFERENCES jetty_kinds(id),
406 km NUMBER(7,3),
407 z NUMBER(38,12)
408 );
409 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('jetties', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001)), 31467);
410 CREATE OR REPLACE TRIGGER jetties_trigger BEFORE INSERT ON jetties FOR EACH ROW
411 BEGIN
412 SELECT JETTIES_ID_SEQ.nextval INTO :new.id FROM dual;
413 END;
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