Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial.sql @ 5025:9ef49f3b0304 dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Sync Posgres and Oracle spatial schema
author Andre Heinecke <aheinecke@intevation.de>
date Tue, 19 Feb 2013 14:58:33 +0100
parents eecd1ebba128
children a5f438895a38
comparison
equal deleted inserted replaced
5024:808718c6de97 5025:9ef49f3b0304
1 WHENEVER SQLERROR EXIT SQL.ERRORCODE ROLLBACK;
1 -- Geodaesie/Flussachse+km/achse 2 -- Geodaesie/Flussachse+km/achse
2 CREATE SEQUENCE RIVER_AXES_ID_SEQ; 3 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
3 CREATE TABLE river_axes( 4 CREATE TABLE river_axes(
4 OGR_FID NUMBER(38), 5 OGR_FID NUMBER(38),
5 GEOM MDSYS.SDO_GEOMETRY, 6 GEOM MDSYS.SDO_GEOMETRY,
6 river_id NUMBER(38), 7 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
7 kind NUMBER(38) DEFAULT 0 NOT NULL, 8 kind NUMBER(38) DEFAULT 0 NOT NULL,
8 name VARCHAR(64), 9 name VARCHAR(64),
9 path VARCHAR(256), 10 path VARCHAR(256),
10 ID NUMBER PRIMARY KEY NOT NULL 11 ID NUMBER PRIMARY KEY NOT NULL
11 ); 12 );
21 -- Geodaesie/Flussachse+km/km.shp 22 -- Geodaesie/Flussachse+km/km.shp
22 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; 23 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
23 CREATE TABLE river_axes_km( 24 CREATE TABLE river_axes_km(
24 OGR_FID NUMBER(38), 25 OGR_FID NUMBER(38),
25 GEOM MDSYS.SDO_GEOMETRY, 26 GEOM MDSYS.SDO_GEOMETRY,
26 river_id NUMBER(38), 27 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
27 km NUMBER(6,3), 28 km NUMBER(6,3),
28 name VARCHAR(64), 29 name VARCHAR(64),
29 path VARCHAR(256), 30 path VARCHAR(256),
30 ID NUMBER PRIMARY KEY NOT NULL 31 ID NUMBER PRIMARY KEY NOT NULL
31 ); 32 );
41 --Geodaesie/Querprofile/QP-Spuren/qps.shp 42 --Geodaesie/Querprofile/QP-Spuren/qps.shp
42 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; 43 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
43 CREATE TABLE cross_section_tracks ( 44 CREATE TABLE cross_section_tracks (
44 OGR_FID NUMBER(38), 45 OGR_FID NUMBER(38),
45 GEOM MDSYS.SDO_GEOMETRY, 46 GEOM MDSYS.SDO_GEOMETRY,
46 river_id NUMBER(38), 47 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
47 km NUMBER(38,12) NOT NULL, 48 km NUMBER(38,12) NOT NULL,
48 z NUMBER(38,12) DEFAULT 0 NOT NULL, 49 z NUMBER(38,12) DEFAULT 0 NOT NULL,
49 name VARCHAR(64), 50 name VARCHAR(64),
50 path VARCHAR(256), 51 path VARCHAR(256),
51 ID NUMBER PRIMARY KEY NOT NULL 52 ID NUMBER PRIMARY KEY NOT NULL
62 -- Geodaesie/Bauwerke/Wehre.shp 63 -- Geodaesie/Bauwerke/Wehre.shp
63 CREATE SEQUENCE BUILDINGS_ID_SEQ; 64 CREATE SEQUENCE BUILDINGS_ID_SEQ;
64 CREATE TABLE buildings( 65 CREATE TABLE buildings(
65 OGR_FID NUMBER(38), 66 OGR_FID NUMBER(38),
66 GEOM MDSYS.SDO_GEOMETRY, 67 GEOM MDSYS.SDO_GEOMETRY,
67 river_id NUMBER(38), 68 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
68 name VARCHAR2(255), 69 name VARCHAR2(255),
69 path VARCHAR(256), 70 path VARCHAR(256),
70 ID NUMBER PRIMARY KEY NOT NULL 71 ID NUMBER PRIMARY KEY NOT NULL
71 ); 72 );
72 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); 73 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);
81 -- Geodaesie/Festpunkte/Festpunkte.shp 82 -- Geodaesie/Festpunkte/Festpunkte.shp
82 CREATE SEQUENCE FIXPOINTS_ID_SEQ; 83 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
83 CREATE TABLE fixpoints ( 84 CREATE TABLE fixpoints (
84 OGR_FID NUMBER(38), 85 OGR_FID NUMBER(38),
85 GEOM MDSYS.SDO_GEOMETRY, 86 GEOM MDSYS.SDO_GEOMETRY,
86 river_id NUMBER(38), 87 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
87 x NUMBER(38,11), 88 x NUMBER(38,11),
88 y NUMBER(38,11), 89 y NUMBER(38,11),
89 km NUMBER(38,11) NOT NULL, 90 km NUMBER(38,11) NOT NULL,
90 HPGP VARCHAR2(255), 91 HPGP VARCHAR2(255),
91 name VARCHAR(64), 92 name VARCHAR(64),
104 -- Hydrologie/Hydr. Grenzen/talaue.shp 105 -- Hydrologie/Hydr. Grenzen/talaue.shp
105 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; 106 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
106 CREATE TABLE floodplain( 107 CREATE TABLE floodplain(
107 OGR_FID NUMBER(38), 108 OGR_FID NUMBER(38),
108 GEOM MDSYS.SDO_GEOMETRY, 109 GEOM MDSYS.SDO_GEOMETRY,
109 river_id NUMBER(38), 110 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
110 name VARCHAR(64), 111 name VARCHAR(64),
111 path VARCHAR(256), 112 path VARCHAR(256),
112 ID NUMBER PRIMARY KEY NOT NULL 113 ID NUMBER PRIMARY KEY NOT NULL
113 ); 114 );
114 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); 115 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);
124 -- NOTE: It's not a spatial schema! 125 -- NOTE: It's not a spatial schema!
125 -- Geodaesie/Hoehenmodelle/* 126 -- Geodaesie/Hoehenmodelle/*
126 CREATE SEQUENCE DEM_ID_SEQ; 127 CREATE SEQUENCE DEM_ID_SEQ;
127 CREATE TABLE dem ( 128 CREATE TABLE dem (
128 ID NUMBER PRIMARY KEY NOT NULL, 129 ID NUMBER PRIMARY KEY NOT NULL,
129 river_id NUMBER(38), 130 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
130 -- XXX Should we use the ranges table instead? 131 -- XXX Should we use the ranges table instead?
131 name VARCHAR(64), 132 name VARCHAR(64),
132 lower NUMBER(19,5), 133 lower NUMBER(19,5),
133 upper NUMBER(19,5), 134 upper NUMBER(19,5),
134 year_from VARCHAR(32) NOT NULL, 135 year_from VARCHAR(32) NOT NULL,
135 year_to VARCHAR(32) NOT NULL, 136 year_to VARCHAR(32) NOT NULL,
136 projection VARCHAR(32) NOT NULL, 137 projection VARCHAR(32) NOT NULL,
137 elevation_state VARCHAR(32), 138 elevation_state VARCHAR(32),
139 srid NUMBER NOT NULL,
138 format VARCHAR(32), 140 format VARCHAR(32),
139 border_break BOOLEAN NOT NULL DEFAULT FALSE, 141 border_break NUMBER(1) DEFAULT 0 NOT NULL,
140 resolution VARCHAR(16), 142 resolution VARCHAR(16),
141 description VARCHAR(256), 143 description VARCHAR(256),
142 path VARCHAR(256) 144 path VARCHAR(256)
143 ); 145 );
144 CREATE OR REPLACE TRIGGER dem_trigger BEFORE INSERT ON dem FOR each ROW 146 CREATE OR REPLACE TRIGGER dem_trigger BEFORE INSERT ON dem FOR each ROW
151 -- Hydrologie/Einzugsgebiete/EZG.shp 153 -- Hydrologie/Einzugsgebiete/EZG.shp
152 CREATE SEQUENCE CATCHMENT_ID_SEQ; 154 CREATE SEQUENCE CATCHMENT_ID_SEQ;
153 CREATE TABLE catchment( 155 CREATE TABLE catchment(
154 OGR_FID NUMBER(38), 156 OGR_FID NUMBER(38),
155 GEOM MDSYS.SDO_GEOMETRY, 157 GEOM MDSYS.SDO_GEOMETRY,
156 river_id NUMBER(38), 158 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
157 area NUMBER(19,5), 159 area NUMBER(19,5),
158 name VARCHAR2(255), 160 name VARCHAR2(255),
159 path VARCHAR(256), 161 path VARCHAR(256),
160 ID NUMBER PRIMARY KEY NOT NULL 162 ID NUMBER PRIMARY KEY NOT NULL
161 ); 163 );
166 SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual; 168 SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual;
167 END; 169 END;
168 / 170 /
169 --CREATE INDEX catchment_spatial_idx ON catchment(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=polygon'); 171 --CREATE INDEX catchment_spatial_idx ON catchment(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=polygon');
170 172
173 --Static lookup tables for Hochwasserschutzanlagen
174 CREATE TABLE hws_kinds (
175 id NUMBER PRIMARY KEY NOT NULL,
176 kind VARCHAR(64) NOT NULL
177 );
178 INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass');
179 INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm');
180 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
181
182 CREATE TABLE fed_states (
183 id NUMBER PRIMARY KEY NOT NULL,
184 name VARCHAR(23) NOT NULL
185 );
186 INSERT INTO fed_states (id, name) VALUES (1, 'Bayern');
187 INSERT INTO fed_states (id, name) VALUES (2, 'Hessen');
188 INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen');
189 INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen');
190 INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz');
191 INSERT INTO fed_states (id, name) VALUES (6, 'Saarland');
192 INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein');
193 INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg');
194 INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern');
195 INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen');
196 INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg');
197 INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt');
198 INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen');
199 INSERT INTO fed_states (id, name) VALUES (14, 'Berlin');
200 INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
201 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
202
171 --Hydrologie/HW-Schutzanlagen/hws.shp 203 --Hydrologie/HW-Schutzanlagen/hws.shp
172 CREATE SEQUENCE HWS_ID_SEQ; 204 -- HWS-Lines
173 CREATE TABLE hws( 205 CREATE SEQUENCE HWS_LINES_ID_SEQ;
174 OGR_FID NUMBER(38), 206 CREATE TABLE hws_lines (
175 GEOM MDSYS.SDO_GEOMETRY, 207 id NUMBER PRIMARY KEY NOT NULL,
176 river_id NUMBER(38), 208 GEOM MDSYS.SDO_GEOMETRY,
177 hws_facility VARCHAR2(255), 209 ogr_fid NUMBER,
178 type VARCHAR2(255), 210 kind_id NUMBER(2) DEFAULT 2 REFERENCES hws_kinds(id),
179 name VARCHAR(64), 211 fed_state_id NUMBER(2) REFERENCES fed_states(id),
180 path VARCHAR(256), 212 river_id NUMBER(38) REFERENCES rivers(id),
181 ID NUMBER PRIMARY KEY NOT NULL 213 name VARCHAR(256),
182 ); 214 path VARCHAR(256),
183 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); 215 official NUMBER DEFAULT 0,
184 CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW 216 agency VARCHAR(256),
185 BEGIN 217 range VARCHAR(256),
186 SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual; 218 shore_side NUMBER DEFAULT 0,
187 END; 219 source VARCHAR(256),
188 / 220 status_date TIMESTAMP,
189 --CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); 221 description VARCHAR(256)
222 );
223 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);
224 CREATE INDEX hws_lines_spatial_idx ON hws_lines(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
225
226 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW
227 BEGIN
228 SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual;
229 END;
230
231 -- HWS Points lookup tables
232 CREATE TABLE sectie_kinds (
233 id NUMBER PRIMARY KEY NOT NULL,
234 name VARCHAR(64) NOT NULL
235 );
236 INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt');
237 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch');
238 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank');
239 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich');
240
241 CREATE TABLE sobek_kinds (
242 id NUMBER PRIMARY KEY NOT NULL,
243 name VARCHAR(64) NOT NULL
244 );
245 INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt');
246 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend');
247 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd');
248
249 CREATE TABLE boundary_kinds (
250 id NUMBER PRIMARY KEY NOT NULL,
251 name VARCHAR(64) NOT NULL
252 );
253 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
254 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
255 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
256 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
257
258
259 -- HWS Points
260 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
261 CREATE TABLE hws_points (
262 id NUMBER PRIMARY KEY NOT NULL,
263 GEOM MDSYS.SDO_GEOMETRY,
264 ogr_fid NUMBER,
265 kind_id NUMBER DEFAULT 2 REFERENCES hws_kinds(id),
266 fed_state_id NUMBER REFERENCES fed_states(id),
267 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
268 name VARCHAR(256),
269 path VARCHAR(256),
270 official NUMBER DEFAULT 0,
271 agency VARCHAR(256),
272 range VARCHAR(256),
273 shore_side NUMBER DEFAULT 0,
274 source VARCHAR(256),
275 status_date VARCHAR(256),
276 description VARCHAR(256),
277 freeboard NUMBER(19,5),
278 dike_km NUMBER(19,5),
279 z NUMBER(19,5),
280 z_target NUMBER(19,5),
281 rated_level NUMBER(19,5)
282 );
283
284 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);
285 CREATE INDEX hws_lines_spatial_idx ON hws_pints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
286
287 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW
288 BEGIN
289 SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual;
290 END;
190 291
191 292
192 --Hydrologie/UeSG 293 --Hydrologie/UeSG
193 CREATE SEQUENCE FLOODMAPS_ID_SEQ; 294 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
194 CREATE TABLE floodmaps ( 295 CREATE TABLE floodmaps (
195 OGR_FID NUMBER(38), 296 OGR_FID NUMBER(38),
196 GEOM MDSYS.SDO_GEOMETRY, 297 GEOM MDSYS.SDO_GEOMETRY,
197 river_id NUMBER(38), 298 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
198 name VARCHAR(255), 299 name VARCHAR(255),
199 kind NUMBER(38), 300 kind NUMBER(38),
200 diff NUMBER(19,5), 301 diff NUMBER(19,5),
201 count NUMBER(38), 302 count NUMBER(38),
202 area NUMBER(19,5), 303 area NUMBER(19,5),
207 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); 308 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);
208 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW 309 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW
209 BEGIN 310 BEGIN
210 SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual; 311 SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual;
211 END; 312 END;
212 / 313
213 CREATE INDEX floodmaps_spatial_idx ON floodmaps(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON'); 314 CREATE INDEX floodmaps_spatial_idx ON floodmaps(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON');
214 315
215 316
216 --Hydrologie/Hydr.Grenzen/Linien 317 --Hydrologie/Hydr.Grenzen/Linien
217 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; 318 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
218 CREATE TABLE hydr_boundaries ( 319 CREATE TABLE hydr_boundaries (
219 OGR_FID NUMBER(38), 320 OGR_FID NUMBER(38),
220 GEOM MDSYS.SDO_GEOMETRY, 321 GEOM MDSYS.SDO_GEOMETRY,
221 river_id NUMBER(38), 322 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
222 name VARCHAR(255), 323 name VARCHAR(255),
223 kind NUMBER(38), 324 kind NUMBER(38),
224 path VARCHAR(256), 325 path VARCHAR(256),
225 id NUMBER PRIMARY KEY NOT NULL 326 id NUMBER PRIMARY KEY NOT NULL
226 ); 327 );
227 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); 328 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);
228 CREATE OR REPLACE TRIGGER hydr_boundaries_trigger BEFORE INSERT ON hydr_boundaries FOR each ROW 329 CREATE OR REPLACE TRIGGER hydr_boundaries_trigger BEFORE INSERT ON hydr_boundaries FOR each ROW
229 BEGIN 330 BEGIN
230 SELECT HYDR_BOUNDARIES_ID_SEQ.nextval INTO :new.id FROM dual; 331 SELECT HYDR_BOUNDARIES_ID_SEQ.nextval INTO :new.id FROM dual;
231 END; 332 END;
232 / 333
233 CREATE INDEX hydr_boundaries_idx ON hydr_boundaries(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); 334 CREATE INDEX hydr_boundaries_idx ON hydr_boundaries(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
234 335
235 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; 336 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
236 CREATE TABLE hydr_boundaries_poly ( 337 CREATE TABLE hydr_boundaries_poly (
237 OGR_FID NUMBER(38), 338 OGR_FID NUMBER(38),
238 GEOM MDSYS.SDO_GEOMETRY, 339 GEOM MDSYS.SDO_GEOMETRY,
239 river_id NUMBER(38), 340 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
240 name VARCHAR(255), 341 name VARCHAR(255),
241 kind NUMBER(38), 342 kind NUMBER(38),
242 path VARCHAR(256), 343 path VARCHAR(256),
243 id NUMBER PRIMARY KEY NOT NULL 344 id NUMBER PRIMARY KEY NOT NULL
244 ); 345 );
245 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); 346 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);
246 CREATE OR REPLACE TRIGGER hydr_boundaries_poly_trigger BEFORE INSERT ON hydr_boundaries_poly FOR each ROW 347 CREATE OR REPLACE TRIGGER hydr_boundaries_poly_trigger BEFORE INSERT ON hydr_boundaries_poly FOR each ROW
247 BEGIN 348 BEGIN
248 SELECT HYDR_BOUNDARIES_POLY_ID_SEQ.nextval INTO :new.id FROM dual; 349 SELECT HYDR_BOUNDARIES_POLY_ID_SEQ.nextval INTO :new.id FROM dual;
249 END; 350 END;
250 / 351
251 CREATE INDEX hydr_boundaries_poly_idx ON hydr_boundaries_poly(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON'); 352 CREATE INDEX hydr_boundaries_poly_idx ON hydr_boundaries_poly(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON');
252 353
253 354
254 -- Hydrologie/Streckendaten/ 355 -- Hydrologie/Streckendaten/
255 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ; 356 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
264 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); 365 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);
265 CREATE OR REPLACE TRIGGER gauge_location_trigger BEFORE INSERT ON gauge_location FOR EACH ROW 366 CREATE OR REPLACE TRIGGER gauge_location_trigger BEFORE INSERT ON gauge_location FOR EACH ROW
266 BEGIN 367 BEGIN
267 SELECT GAUGE_LOCATION_ID_SEQ.nextval INTO :new.id FROM dual; 368 SELECT GAUGE_LOCATION_ID_SEQ.nextval INTO :new.id FROM dual;
268 END; 369 END;
269 / 370
270 CREATE INDEX gauge_location_idx ON gauge_location(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT'); 371 CREATE INDEX gauge_location_idx ON gauge_location(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
http://dive4elements.wald.intevation.org