Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial.sql @ 5379:61bf64b102bc mapgenfix

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merge with default branch
author Christian Lins <christian.lins@intevation.de>
date Fri, 22 Mar 2013 11:25:54 +0100
parents 59f9760e8b7f
children 2da74705c29d
comparison
equal deleted inserted replaced
5175:cfc5540a4eec 5379:61bf64b102bc
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, 'Aktuell');
9 INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige');
10
1 -- Geodaesie/Flussachse+km/achse 11 -- Geodaesie/Flussachse+km/achse
2 CREATE SEQUENCE RIVER_AXES_ID_SEQ; 12 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
3 CREATE TABLE river_axes( 13 CREATE TABLE river_axes(
4 OGR_FID NUMBER(38), 14 OGR_FID NUMBER(38),
5 GEOM MDSYS.SDO_GEOMETRY, 15 GEOM MDSYS.SDO_GEOMETRY,
6 river_id NUMBER(38), 16 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
7 kind NUMBER(38) DEFAULT 0 NOT NULL, 17 kind_id NUMBER(38) REFERENCES axis_kinds(id) NOT NULL DEFAULT 0,
8 name VARCHAR(64), 18 name VARCHAR(64),
9 path VARCHAR(256), 19 path VARCHAR(256),
10 ID NUMBER PRIMARY KEY NOT NULL 20 ID NUMBER PRIMARY KEY NOT NULL
11 ); 21 );
12 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); 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);
21 -- Geodaesie/Flussachse+km/km.shp 31 -- Geodaesie/Flussachse+km/km.shp
22 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; 32 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
23 CREATE TABLE river_axes_km( 33 CREATE TABLE river_axes_km(
24 OGR_FID NUMBER(38), 34 OGR_FID NUMBER(38),
25 GEOM MDSYS.SDO_GEOMETRY, 35 GEOM MDSYS.SDO_GEOMETRY,
26 river_id NUMBER(38), 36 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
27 km NUMBER(6,3), 37 km NUMBER(7,3),
28 name VARCHAR(64), 38 name VARCHAR(64),
29 path VARCHAR(256), 39 path VARCHAR(256),
30 ID NUMBER PRIMARY KEY NOT NULL 40 ID NUMBER PRIMARY KEY NOT NULL
31 ); 41 );
32 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),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); 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);
33 CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW 43 CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW
34 BEGIN 44 BEGIN
35 SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual; 45 SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual;
36 END; 46 END;
37 / 47 /
38 --CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point'); 48 --CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point');
39 49
40 50
41 --Geodaesie/Querprofile/QP-Spuren/qps.shp 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, 'Aktuell');
58
42 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; 59 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
43 CREATE TABLE cross_section_tracks ( 60 CREATE TABLE cross_section_tracks (
44 OGR_FID NUMBER(38), 61 OGR_FID NUMBER(38),
45 GEOM MDSYS.SDO_GEOMETRY, 62 GEOM MDSYS.SDO_GEOMETRY,
46 river_id NUMBER(38), 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,
47 km NUMBER(38,12) NOT NULL, 65 km NUMBER(38,12) NOT NULL,
48 z NUMBER(38,12) DEFAULT 0 NOT NULL, 66 z NUMBER(38,12) DEFAULT 0 NOT NULL,
49 name VARCHAR(64), 67 name VARCHAR(64),
50 path VARCHAR(256), 68 path VARCHAR(256),
51 ID NUMBER PRIMARY KEY NOT NULL 69 ID NUMBER PRIMARY KEY NOT NULL
57 END; 75 END;
58 / 76 /
59 --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); 77 --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
60 78
61 79
62 -- TODO: TestMe. Fix Importer-Script. Fix oracle_spatial_idx.sql script.
63 -- Geodaesie/Linien/rohre-und-speeren
64 CREATE SEQUENCE LINES_ID_SEQ;
65 CREATE TABLE lines (
66 OGR_FID NUMBER(38),
67 GEOM MDSYS.SDO_GEOMETRY,
68 river_id NUMBER(38),
69 kind VARCHAR2(16) NOT NULL,
70 z NUMBER(38,12) DEFAULT 0,
71 name VARCHAR(64),
72 path VARCHAR(256),
73 ID NUMBER PRIMARY KEY NOT NULL
74 );
75 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('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);
76 CREATE OR REPLACE TRIGGER lines_trigger BEFORE INSERT ON lines FOR each ROW
77 BEGIN
78 SELECT LINES_ID_SEQ.nextval INTO :new.id FROM dual;
79 END;
80 /
81 -- NOTE: Should lines should be 3D.
82 -- TODO: Test index.
83 --CREATE INDEX lines_idx ON lines(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
84 -- 'kind':
85 -- 0: ROHR1
86 -- 1: DAMM
87
88
89 -- Geodaesie/Bauwerke/Wehre.shp 80 -- Geodaesie/Bauwerke/Wehre.shp
90 CREATE SEQUENCE BUILDINGS_ID_SEQ; 81 CREATE SEQUENCE BUILDINGS_ID_SEQ;
91 CREATE TABLE buildings( 82 CREATE TABLE buildings(
92 OGR_FID NUMBER(38), 83 OGR_FID NUMBER(38),
93 GEOM MDSYS.SDO_GEOMETRY, 84 GEOM MDSYS.SDO_GEOMETRY,
94 river_id NUMBER(38), 85 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
95 name VARCHAR2(255), 86 name VARCHAR2(255),
96 path VARCHAR(256), 87 path VARCHAR(256),
97 ID NUMBER PRIMARY KEY NOT NULL 88 ID NUMBER PRIMARY KEY NOT NULL
98 ); 89 );
99 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); 90 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);
108 -- Geodaesie/Festpunkte/Festpunkte.shp 99 -- Geodaesie/Festpunkte/Festpunkte.shp
109 CREATE SEQUENCE FIXPOINTS_ID_SEQ; 100 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
110 CREATE TABLE fixpoints ( 101 CREATE TABLE fixpoints (
111 OGR_FID NUMBER(38), 102 OGR_FID NUMBER(38),
112 GEOM MDSYS.SDO_GEOMETRY, 103 GEOM MDSYS.SDO_GEOMETRY,
113 river_id NUMBER(38), 104 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
114 x NUMBER(38,11), 105 x NUMBER(38,11),
115 y NUMBER(38,11), 106 y NUMBER(38,11),
116 km NUMBER(38,11) NOT NULL, 107 km NUMBER(38,11) NOT NULL,
117 HPGP VARCHAR2(255), 108 HPGP VARCHAR2(255),
118 name VARCHAR(64), 109 name VARCHAR(64),
119 path VARCHAR(256), 110 path VARCHAR(256),
120 ID NUMBER PRIMARY KEY NOT NULL 111 ID NUMBER PRIMARY KEY NOT NULL
121 ); 112 );
122 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),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); 113 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);
123 CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW 114 CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW
124 BEGIN 115 BEGIN
125 SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual; 116 SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual;
126 END; 117 END;
127 / 118 /
128 --CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT'); 119 --CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
129 120
130 121
131 -- Hydrologie/Hydr. Grenzen/talaue.shp 122 -- Hydrologie/Hydr. Grenzen/talaue.shp
123 CREATE TABLE floodplain_kinds(
124 id NUMBER PRIMARY KEY NOT NULL,
125 name VARCHAR(64)
126 );
127 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige');
128 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'Aktuell');
129
132 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; 130 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
133 CREATE TABLE floodplain( 131 CREATE TABLE floodplain(
134 OGR_FID NUMBER(38), 132 OGR_FID NUMBER(38),
135 GEOM MDSYS.SDO_GEOMETRY, 133 GEOM MDSYS.SDO_GEOMETRY,
136 river_id NUMBER(38), 134 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
135 kind_id NUMBER(38) REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0,
137 name VARCHAR(64), 136 name VARCHAR(64),
138 path VARCHAR(256), 137 path VARCHAR(256),
139 ID NUMBER PRIMARY KEY NOT NULL 138 ID NUMBER PRIMARY KEY NOT NULL
140 ); 139 );
141 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); 140 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);
145 END; 144 END;
146 / 145 /
147 --CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON'); 146 --CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON');
148 147
149 148
150 -- TODO: Test-Me. Fix Importer-Skript.
151 -- NOTE: It's not a spatial schema!
152 -- Geodaesie/Hoehenmodelle/* 149 -- Geodaesie/Hoehenmodelle/*
153 CREATE SEQUENCE DEM_ID_SEQ; 150 CREATE SEQUENCE DEM_ID_SEQ;
154 CREATE TABLE dem ( 151 CREATE TABLE dem (
155 ID NUMBER PRIMARY KEY NOT NULL, 152 ID NUMBER PRIMARY KEY NOT NULL,
156 river_id NUMBER(38), 153 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
157 -- XXX Should we use the ranges table instead? 154 name VARCHAR(64),
158 name VARCHAR(64), 155 range_id NUMBER(38) REFERENCES ranges(id),
159 lower NUMBER(19,5), 156 time_interval_id NUMBER(38) REFERENCES time_intervals(id),
160 upper NUMBER(19,5), 157 projection VARCHAR(32),
161 year_from VARCHAR(32) NOT NULL, 158 elevation_state VARCHAR(32),
162 year_to VARCHAR(32) NOT NULL, 159 srid NUMBER NOT NULL,
163 projection VARCHAR(32) NOT NULL, 160 format VARCHAR(32),
164 elevation_state VARCHAR(32), 161 border_break NUMBER(1) DEFAULT 0 NOT NULL,
165 format VARCHAR(32), 162 resolution VARCHAR(16),
166 border_break BOOLEAN NOT NULL DEFAULT FALSE, 163 description VARCHAR(256),
167 resolution VARCHAR(16), 164 path VARCHAR(256) NOT NULL
168 description VARCHAR(256),
169 path VARCHAR(256)
170 ); 165 );
171 CREATE OR REPLACE TRIGGER dem_trigger BEFORE INSERT ON dem FOR each ROW 166 CREATE OR REPLACE TRIGGER dem_trigger BEFORE INSERT ON dem FOR each ROW
172 BEGIN 167 BEGIN
173 SELECT DEM_ID_SEQ.nextval INTO :new.id FROM dual; 168 SELECT DEM_ID_SEQ.nextval INTO :new.id FROM dual;
174 END; 169 END;
175 / 170 /
176 171
177 172 --Static lookup tables for Hochwasserschutzanlagen
178 -- Hydrologie/Einzugsgebiete/EZG.shp 173 CREATE TABLE hws_kinds (
179 CREATE SEQUENCE CATCHMENT_ID_SEQ; 174 id NUMBER PRIMARY KEY NOT NULL,
180 CREATE TABLE catchment( 175 kind VARCHAR(64) NOT NULL
181 OGR_FID NUMBER(38), 176 );
182 GEOM MDSYS.SDO_GEOMETRY, 177 INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass');
183 river_id NUMBER(38), 178 INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm');
184 area NUMBER(19,5), 179 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
185 name VARCHAR2(255), 180
186 path VARCHAR(256), 181 CREATE TABLE fed_states (
187 ID NUMBER PRIMARY KEY NOT NULL 182 id NUMBER PRIMARY KEY NOT NULL,
188 ); 183 name VARCHAR(23) NOT NULL
189 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('CATCHMENT', '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); 184 );
190 185 INSERT INTO fed_states (id, name) VALUES (1, 'Bayern');
191 CREATE TRIGGER catchment_trigger BEFORE INSERT ON catchment FOR each ROW 186 INSERT INTO fed_states (id, name) VALUES (2, 'Hessen');
192 BEGIN 187 INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen');
193 SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual; 188 INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen');
194 END; 189 INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz');
195 / 190 INSERT INTO fed_states (id, name) VALUES (6, 'Saarland');
196 --CREATE INDEX catchment_spatial_idx ON catchment(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=polygon'); 191 INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein');
192 INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg');
193 INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern');
194 INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen');
195 INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg');
196 INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt');
197 INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen');
198 INSERT INTO fed_states (id, name) VALUES (14, 'Berlin');
199 INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
200 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
197 201
198 --Hydrologie/HW-Schutzanlagen/hws.shp 202 --Hydrologie/HW-Schutzanlagen/hws.shp
199 CREATE SEQUENCE HWS_ID_SEQ; 203 -- HWS-Lines
200 CREATE TABLE hws( 204 CREATE SEQUENCE HWS_LINES_ID_SEQ;
201 OGR_FID NUMBER(38), 205 CREATE TABLE hws_lines (
202 GEOM MDSYS.SDO_GEOMETRY, 206 OGR_FID NUMBER(38),
203 river_id NUMBER(38), 207 GEOM MDSYS.SDO_GEOMETRY,
204 hws_facility VARCHAR2(255), 208 kind_id NUMBER(2) DEFAULT 2 REFERENCES hws_kinds(id),
205 type VARCHAR2(255), 209 fed_state_id NUMBER(2) REFERENCES fed_states(id),
206 name VARCHAR(64), 210 river_id NUMBER(38) REFERENCES rivers(id),
207 path VARCHAR(256), 211 name VARCHAR(256),
208 ID NUMBER PRIMARY KEY NOT NULL 212 path VARCHAR(256),
209 ); 213 official NUMBER DEFAULT 0,
210 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws', '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); 214 agency VARCHAR(256),
211 CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW 215 range VARCHAR(256),
212 BEGIN 216 shore_side NUMBER DEFAULT 0,
213 SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual; 217 source VARCHAR(256),
214 END; 218 status_date TIMESTAMP,
215 / 219 description VARCHAR(256),
216 --CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); 220 id NUMBER PRIMARY KEY NOT NULL
217 221 );
222 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);
223 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW
224 BEGIN
225 SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual;
226 END;
227 /
228 -- HWS Points lookup tables
229 CREATE TABLE sectie_kinds (
230 id NUMBER PRIMARY KEY NOT NULL,
231 name VARCHAR(64) NOT NULL
232 );
233 INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt');
234 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch');
235 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank');
236 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich');
237
238 CREATE TABLE sobek_kinds (
239 id NUMBER PRIMARY KEY NOT NULL,
240 name VARCHAR(64) NOT NULL
241 );
242 INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt');
243 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend');
244 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd');
245
246 CREATE TABLE boundary_kinds (
247 id NUMBER PRIMARY KEY NOT NULL,
248 name VARCHAR(64) NOT NULL
249 );
250 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
251 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
252 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
253 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
254
255 -- HWS Points
256 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
257 CREATE TABLE hws_points (
258 OGR_FID NUMBER(38),
259 GEOM MDSYS.SDO_GEOMETRY,
260 kind_id NUMBER DEFAULT 2 REFERENCES hws_kinds(id),
261 fed_state_id NUMBER REFERENCES fed_states(id),
262 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
263 name VARCHAR(256),
264 path VARCHAR(256),
265 official NUMBER DEFAULT 0,
266 agency VARCHAR(256),
267 range VARCHAR(256),
268 shore_side NUMBER DEFAULT 0,
269 source VARCHAR(256),
270 status_date VARCHAR(256),
271 description VARCHAR(256),
272 freeboard NUMBER(19,5),
273 dike_km NUMBER(19,5),
274 z NUMBER(19,5),
275 z_target NUMBER(19,5),
276 rated_level NUMBER(19,5),
277 id NUMBER PRIMARY KEY NOT NULL
278 );
279
280 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);
281
282 CREATE OR REPLACE TRIGGER hws_points_trigger BEFORE INSERT ON hws_points FOR each ROW
283 BEGIN
284 SELECT HWS_POINTS_ID_SEQ.nextval INTO :new.id FROM dual;
285 END;
286 /
218 287
219 --Hydrologie/UeSG 288 --Hydrologie/UeSG
289 CREATE TABLE floodmap_kinds (
290 id NUMBER PRIMARY KEY NOT NULL,
291 name varchar(64) NOT NULL
292 );
293 INSERT INTO floodmap_kinds VALUES (200, 'Messung');
294 INSERT INTO floodmap_kinds VALUES (111, 'Berechnung-Aktuell-BfG');
295 INSERT INTO floodmap_kinds VALUES (112, 'Berechnung-Aktuell-Bundesländer');
296 INSERT INTO floodmap_kinds VALUES (121, 'Berechnung-Potenziell-BfG');
297 INSERT INTO floodmap_kinds VALUES (122, 'Berechnung-Potenziell-Bundesländer');
298
220 CREATE SEQUENCE FLOODMAPS_ID_SEQ; 299 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
221 CREATE TABLE floodmaps ( 300 CREATE TABLE floodmaps (
222 OGR_FID NUMBER(38), 301 OGR_FID NUMBER(38),
223 GEOM MDSYS.SDO_GEOMETRY, 302 GEOM MDSYS.SDO_GEOMETRY,
224 river_id NUMBER(38), 303 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
225 name VARCHAR(255), 304 name VARCHAR(255),
226 kind NUMBER(38), 305 kind NUMBER NOT NULL REFERENCES floodmap_kinds(id),
227 diff NUMBER(19,5), 306 diff NUMBER(19,5),
228 count NUMBER(38), 307 count NUMBER(38),
229 area NUMBER(19,5), 308 area NUMBER(19,5),
230 perimeter NUMBER(19,5), 309 perimeter NUMBER(19,5),
231 path VARCHAR(256), 310 path VARCHAR(256),
311 source varchar(64),
232 id NUMBER PRIMARY KEY NOT NULL 312 id NUMBER PRIMARY KEY NOT NULL
233 ); 313 );
234 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); 314 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);
235 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW 315 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW
236 BEGIN 316 BEGIN
237 SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual; 317 SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual;
238 END; 318 END;
239 / 319 /
240 CREATE INDEX floodmaps_spatial_idx ON floodmaps(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON');
241
242 320
243 --Hydrologie/Hydr.Grenzen/Linien 321 --Hydrologie/Hydr.Grenzen/Linien
244 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; 322 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
245 CREATE TABLE hydr_boundaries ( 323 CREATE TABLE hydr_boundaries (
246 OGR_FID NUMBER(38), 324 OGR_FID NUMBER(38),
247 GEOM MDSYS.SDO_GEOMETRY, 325 GEOM MDSYS.SDO_GEOMETRY,
248 river_id NUMBER(38), 326 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
249 name VARCHAR(255), 327 name VARCHAR(255),
250 kind NUMBER(38), 328 kind NUMBER(38) REFERENCES boundary_kinds(id),
329 sectie NUMBER(38) REFERENCES sectie_kinds(id),
330 sobek NUMBER(38) REFERENCES sobek_kinds(id),
251 path VARCHAR(256), 331 path VARCHAR(256),
252 id NUMBER PRIMARY KEY NOT NULL 332 id NUMBER PRIMARY KEY NOT NULL
253 ); 333 );
254 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); 334 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);
255 CREATE OR REPLACE TRIGGER hydr_boundaries_trigger BEFORE INSERT ON hydr_boundaries FOR each ROW 335 CREATE OR REPLACE TRIGGER hydr_boundaries_trigger BEFORE INSERT ON hydr_boundaries FOR each ROW
256 BEGIN 336 BEGIN
257 SELECT HYDR_BOUNDARIES_ID_SEQ.nextval INTO :new.id FROM dual; 337 SELECT HYDR_BOUNDARIES_ID_SEQ.nextval INTO :new.id FROM dual;
258 END; 338 END;
259 / 339 /
260 CREATE INDEX hydr_boundaries_idx ON hydr_boundaries(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
261 340
262 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; 341 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
263 CREATE TABLE hydr_boundaries_poly ( 342 CREATE TABLE hydr_boundaries_poly (
264 OGR_FID NUMBER(38), 343 OGR_FID NUMBER(38),
265 GEOM MDSYS.SDO_GEOMETRY, 344 GEOM MDSYS.SDO_GEOMETRY,
266 river_id NUMBER(38), 345 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
267 name VARCHAR(255), 346 name VARCHAR(255),
268 kind NUMBER(38), 347 kind NUMBER(38) REFERENCES boundary_kinds(id),
348 sectie NUMBER(38) REFERENCES sectie_kinds(id),
349 sobek NUMBER(38) REFERENCES sobek_kinds(id),
269 path VARCHAR(256), 350 path VARCHAR(256),
270 id NUMBER PRIMARY KEY NOT NULL 351 id NUMBER PRIMARY KEY NOT NULL
271 ); 352 );
272 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); 353 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);
273 CREATE OR REPLACE TRIGGER hydr_boundaries_poly_trigger BEFORE INSERT ON hydr_boundaries_poly FOR each ROW 354 CREATE OR REPLACE TRIGGER hydr_boundaries_poly_trigger BEFORE INSERT ON hydr_boundaries_poly FOR each ROW
274 BEGIN 355 BEGIN
275 SELECT HYDR_BOUNDARIES_POLY_ID_SEQ.nextval INTO :new.id FROM dual; 356 SELECT HYDR_BOUNDARIES_POLY_ID_SEQ.nextval INTO :new.id FROM dual;
276 END; 357 END;
277 / 358 /
278 CREATE INDEX hydr_boundaries_poly_idx ON hydr_boundaries_poly(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON');
279
280 359
281 -- Hydrologie/Streckendaten/ 360 -- Hydrologie/Streckendaten/
282 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ; 361 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
283 CREATE TABLE gauge_location ( 362 CREATE TABLE gauge_location (
284 OGR_FID NUMBER(38), 363 OGR_FID NUMBER(38),
292 CREATE OR REPLACE TRIGGER gauge_location_trigger BEFORE INSERT ON gauge_location FOR EACH ROW 371 CREATE OR REPLACE TRIGGER gauge_location_trigger BEFORE INSERT ON gauge_location FOR EACH ROW
293 BEGIN 372 BEGIN
294 SELECT GAUGE_LOCATION_ID_SEQ.nextval INTO :new.id FROM dual; 373 SELECT GAUGE_LOCATION_ID_SEQ.nextval INTO :new.id FROM dual;
295 END; 374 END;
296 / 375 /
297 CREATE INDEX gauge_location_idx ON gauge_location(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT'); 376
377
378 CREATE TABLE jetty_kinds(
379 id NUMBER PRIMARY KEY NOT NULL,
380 name VARCHAR(64)
381 );
382 INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf');
383 INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß');
384 INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel');
385
386 CREATE SEQUENCE JETTIES_ID_SEQ;
387 CREATE TABLE jetties (
388 OGR_FID NUMBER(38),
389 GEOM MDSYS.SDO_GEOMETRY,
390 id NUMBER PRIMARY KEY NOT NULL,
391 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
392 path VARCHAR(256),
393 kind_id NUMBER(38) REFERENCES jetty_kinds(id),
394 km NUMBER(7,3),
395 z NUMBER(38,12)
396 );
397 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);
398 CREATE OR REPLACE TRIGGER jetties_trigger BEFORE INSERT ON jetties FOR EACH ROW
399 BEGIN
400 SELECT JETTIES_ID_SEQ.nextval INTO :new.id FROM dual;
401 END;
402 /
http://dive4elements.wald.intevation.org