Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial.sql @ 3689:c938e568c4a2 2.9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged flys-backend/2.9
author Thomas Arendsen Hein <thomas@intevation.de>
date Fri, 28 Sep 2012 12:14:44 +0200
parents c37effda3655
children 89ada0b9083f
comparison
equal deleted inserted replaced
3651:06a65baae494 3689:c938e568c4a2
1 -- Geodaesie/Flussachse+km/achse
2 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
3 CREATE TABLE river_axes(
4 OGR_FID NUMBER(38),
5 GEOM MDSYS.SDO_GEOMETRY,
6 river_id NUMBER(38),
7 kind NUMBER(38) DEFAULT 0 NOT NULL,
8 name VARCHAR(64),
9 path VARCHAR(256),
10 ID NUMBER PRIMARY KEY NOT NULL
11 );
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);
13 CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW
14 BEGIN
15 SELECT RIVER_AXES_ID_SEQ.nextval INTO :new.id FROM dual;
16 END;
17 /
18 --CREATE INDEX river_axes_spatial_idx ON river_axes(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
19
20
21 -- Geodaesie/Flussachse+km/km.shp
22 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
23 CREATE TABLE river_axes_km(
24 OGR_FID NUMBER(38),
25 GEOM MDSYS.SDO_GEOMETRY,
26 river_id NUMBER(38),
27 km NUMBER(6,3),
28 name VARCHAR(64),
29 path VARCHAR(256),
30 ID NUMBER PRIMARY KEY NOT NULL
31 );
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);
33 CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW
34 BEGIN
35 SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual;
36 END;
37 /
38 --CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point');
39
40
41 --Geodaesie/Querprofile/QP-Spuren/qps.shp
42 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
43 CREATE TABLE cross_section_tracks (
44 OGR_FID NUMBER(38),
45 GEOM MDSYS.SDO_GEOMETRY,
46 river_id NUMBER(38),
47 km NUMBER(38,12) NOT NULL,
48 z NUMBER(38,12) DEFAULT 0 NOT NULL,
49 name VARCHAR(64),
50 path VARCHAR(256),
51 ID NUMBER PRIMARY KEY NOT NULL
52 );
53 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);
54 CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW
55 BEGIN
56 SELECT CROSS_SECTION_TRACKS_ID_SEQ.nextval INTO :new.id FROM dual;
57 END;
58 /
59 --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
60
61
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
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),
95 name VARCHAR2(255),
96 path VARCHAR(256),
97 ID NUMBER PRIMARY KEY NOT NULL
98 );
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);
100 CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW
101 BEGIN
102 SELECT BUILDINGS_ID_SEQ.nextval INTO :new.id FROM dual;
103 END;
104 /
105 --CREATE INDEX buildings_spatial_idx ON buildings(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
106
107
108 -- Geodaesie/Festpunkte/Festpunkte.shp
109 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
110 CREATE TABLE fixpoints (
111 OGR_FID NUMBER(38),
112 GEOM MDSYS.SDO_GEOMETRY,
113 river_id NUMBER(38),
114 x NUMBER(38,11),
115 y NUMBER(38,11),
116 km NUMBER(38,11) NOT NULL,
117 HPGP VARCHAR2(255),
118 name VARCHAR(64),
119 path VARCHAR(256),
120 ID NUMBER PRIMARY KEY NOT NULL
121 );
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);
123 CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW
124 BEGIN
125 SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual;
126 END;
127 /
128 --CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
129
130
131 -- Hydrologie/Hydr. Grenzen/talaue.shp
132 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
133 CREATE TABLE floodplain(
134 OGR_FID NUMBER(38),
135 GEOM MDSYS.SDO_GEOMETRY,
136 river_id NUMBER(38),
137 name VARCHAR(64),
138 path VARCHAR(256),
139 ID NUMBER PRIMARY KEY NOT NULL
140 );
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);
142 CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW
143 BEGIN
144 SELECT FLOODPLAIN_ID_SEQ.nextval INTO :new.id FROM dual;
145 END;
146 /
147 --CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON');
148
149
150 -- TODO: Test-Me. Fix Importer-Skript.
151 -- NOTE: It's not a spatial schema!
152 -- Geodaesie/Hoehenmodelle/*
153 CREATE SEQUENCE DEM_ID_SEQ;
154 CREATE TABLE dem (
155 ID NUMBER PRIMARY KEY NOT NULL,
156 river_id NUMBER(38),
157 -- XXX Should we use the ranges table instead?
158 lower NUMBER(19,5),
159 upper NUMBER(19,5),
160 path VARCHAR(256),
161 UNIQUE (river_id, lower, upper)
162 );
163 CREATE OR REPLACE TRIGGER dem_trigger BEFORE INSERT ON dem FOR each ROW
164 BEGIN
165 SELECT DEM_ID_SEQ.nextval INTO :new.id FROM dual;
166 END;
167 /
168
169
170 -- Hydrologie/Einzugsgebiete/EZG.shp
171 CREATE SEQUENCE CATCHMENT_ID_SEQ;
172 CREATE TABLE catchment(
173 OGR_FID NUMBER(38),
174 GEOM MDSYS.SDO_GEOMETRY,
175 river_id NUMBER(38),
176 area NUMBER(19,5),
177 name VARCHAR2(255),
178 path VARCHAR(256),
179 ID NUMBER PRIMARY KEY NOT NULL
180 );
181 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);
182
183 CREATE TRIGGER catchment_trigger BEFORE INSERT ON catchment FOR each ROW
184 BEGIN
185 SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual;
186 END;
187 /
188 --CREATE INDEX catchment_spatial_idx ON catchment(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=polygon');
189
190 --Hydrologie/HW-Schutzanlagen/hws.shp
191 CREATE SEQUENCE HWS_ID_SEQ;
192 CREATE TABLE hws(
193 OGR_FID NUMBER(38),
194 GEOM MDSYS.SDO_GEOMETRY,
195 river_id NUMBER(38),
196 hws_facility VARCHAR2(255),
197 type VARCHAR2(255),
198 name VARCHAR(64),
199 path VARCHAR(256),
200 ID NUMBER PRIMARY KEY NOT NULL
201 );
202 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);
203 CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW
204 BEGIN
205 SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual;
206 END;
207 /
208 --CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
209
210
211 --Hydrologie/UeSG
212 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
213 CREATE TABLE floodmaps (
214 OGR_FID NUMBER(38),
215 GEOM MDSYS.SDO_GEOMETRY,
216 river_id NUMBER(38),
217 name VARCHAR(255),
218 kind NUMBER(38),
219 diff NUMBER(19,5),
220 count NUMBER(38),
221 area NUMBER(19,5),
222 perimeter NUMBER(19,5),
223 path VARCHAR(256),
224 id NUMBER PRIMARY KEY NOT NULL
225 );
226 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);
227 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW
228 BEGIN
229 SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual;
230 END;
231 /
232 CREATE INDEX floodmaps_spatial_idx ON floodmaps(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON');
233
234
235 --Hydrologie/Hydr.Grenzen/Linien
236 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
237 CREATE TABLE hydr_boundaries (
238 OGR_FID NUMBER(38),
239 GEOM MDSYS.SDO_GEOMETRY,
240 river_id NUMBER(38),
241 name VARCHAR(255),
242 kind NUMBER(38),
243 path VARCHAR(256),
244 id NUMBER PRIMARY KEY NOT NULL
245 );
246 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);
247 CREATE OR REPLACE TRIGGER hydr_boundaries_trigger BEFORE INSERT ON hydr_boundaries FOR each ROW
248 BEGIN
249 SELECT HYDR_BOUNDARIES_ID_SEQ.nextval INTO :new.id FROM dual;
250 END;
251 /
252 CREATE INDEX hydr_boundaries_idx ON hydr_boundaries(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
253
254 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
255 CREATE TABLE hydr_boundaries_poly (
256 OGR_FID NUMBER(38),
257 GEOM MDSYS.SDO_GEOMETRY,
258 river_id NUMBER(38),
259 name VARCHAR(255),
260 kind NUMBER(38),
261 path VARCHAR(256),
262 id NUMBER PRIMARY KEY NOT NULL
263 );
264 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);
265 CREATE OR REPLACE TRIGGER hydr_boundaries_poly_trigger BEFORE INSERT ON hydr_boundaries_poly FOR each ROW
266 BEGIN
267 SELECT HYDR_BOUNDARIES_POLY_ID_SEQ.nextval INTO :new.id FROM dual;
268 END;
269 /
270 CREATE INDEX hydr_boundaries_poly_idx ON hydr_boundaries_poly(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON');
271
272
273 -- Hydrologie/Streckendaten/
274 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
275 CREATE TABLE gauge_location (
276 OGR_FID NUMBER(38),
277 GEOM MDSYS.SDO_GEOMETRY,
278 river_id NUMBER(38),
279 name VARCHAR(64),
280 path VARCHAR(256),
281 id NUMBER PRIMARY KEY NOT NULL
282 );
283 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);
284 CREATE OR REPLACE TRIGGER gauge_location_trigger BEFORE INSERT ON gauge_location FOR EACH ROW
285 BEGIN
286 SELECT GAUGE_LOCATION_ID_SEQ.nextval INTO :new.id FROM dual;
287 END;
288 /
289 CREATE INDEX gauge_location_idx ON gauge_location(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
http://dive4elements.wald.intevation.org