Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial.sql @ 3344:cb376f48dd37 2.8

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