Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial.sql @ 2861:b0132e1b9719

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added further shape importers and added the option to reproject shapes during the import process. flys-backend/trunk@4342 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Thu, 03 May 2012 14:19:52 +0000
parents 875a87b8489f
children 998f1a7dcfde
comparison
equal deleted inserted replaced
2860:2f874d14ac68 2861:b0132e1b9719
3 CREATE TABLE river_axes( 3 CREATE TABLE river_axes(
4 OGR_FID NUMBER(38), 4 OGR_FID NUMBER(38),
5 GEOM MDSYS.SDO_GEOMETRY, 5 GEOM MDSYS.SDO_GEOMETRY,
6 river_id NUMBER(38), 6 river_id NUMBER(38),
7 kind NUMBER(38) DEFAULT 0 NOT NULL, 7 kind NUMBER(38) DEFAULT 0 NOT NULL,
8 name VARCHAR(64),
8 ID NUMBER PRIMARY KEY NOT NULL 9 ID NUMBER PRIMARY KEY NOT NULL
9 ); 10 );
10 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); 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);
11 CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW 12 CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW
12 BEGIN 13 BEGIN
21 CREATE TABLE river_axes_km( 22 CREATE TABLE river_axes_km(
22 OGR_FID NUMBER(38), 23 OGR_FID NUMBER(38),
23 GEOM MDSYS.SDO_GEOMETRY, 24 GEOM MDSYS.SDO_GEOMETRY,
24 river_id NUMBER(38), 25 river_id NUMBER(38),
25 km NUMBER(6,3), 26 km NUMBER(6,3),
27 name VARCHAR(64),
26 ID NUMBER PRIMARY KEY NOT NULL 28 ID NUMBER PRIMARY KEY NOT NULL
27 ); 29 );
28 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); 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);
29 CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW 31 CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW
30 BEGIN 32 BEGIN
40 OGR_FID NUMBER(38), 42 OGR_FID NUMBER(38),
41 GEOM MDSYS.SDO_GEOMETRY, 43 GEOM MDSYS.SDO_GEOMETRY,
42 river_id NUMBER(38), 44 river_id NUMBER(38),
43 km NUMBER(38,12) NOT NULL, 45 km NUMBER(38,12) NOT NULL,
44 z NUMBER(38,12) DEFAULT 0 NOT NULL, 46 z NUMBER(38,12) DEFAULT 0 NOT NULL,
47 name VARCHAR(64),
45 ID NUMBER PRIMARY KEY NOT NULL 48 ID NUMBER PRIMARY KEY NOT NULL
46 ); 49 );
47 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); 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);
48 CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW 51 CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW
49 BEGIN 52 BEGIN
60 OGR_FID NUMBER(38), 63 OGR_FID NUMBER(38),
61 GEOM MDSYS.SDO_GEOMETRY, 64 GEOM MDSYS.SDO_GEOMETRY,
62 river_id NUMBER(38), 65 river_id NUMBER(38),
63 kind VARCHAR2(16) NOT NULL, 66 kind VARCHAR2(16) NOT NULL,
64 z NUMBER(38,12) DEFAULT 0, 67 z NUMBER(38,12) DEFAULT 0,
68 name VARCHAR(64),
65 ID NUMBER PRIMARY KEY NOT NULL 69 ID NUMBER PRIMARY KEY NOT NULL
66 ); 70 );
67 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); 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);
68 CREATE OR REPLACE TRIGGER lines_trigger BEFORE INSERT ON lines FOR each ROW 72 CREATE OR REPLACE TRIGGER lines_trigger BEFORE INSERT ON lines FOR each ROW
69 BEGIN 73 BEGIN
104 river_id NUMBER(38), 108 river_id NUMBER(38),
105 x NUMBER(38,11), 109 x NUMBER(38,11),
106 y NUMBER(38,11), 110 y NUMBER(38,11),
107 km NUMBER(38,11) NOT NULL, 111 km NUMBER(38,11) NOT NULL,
108 HPGP VARCHAR2(255), 112 HPGP VARCHAR2(255),
113 name VARCHAR(64),
109 ID NUMBER PRIMARY KEY NOT NULL 114 ID NUMBER PRIMARY KEY NOT NULL
110 ); 115 );
111 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); 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);
112 CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW 117 CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW
113 BEGIN 118 BEGIN
121 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; 126 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
122 CREATE TABLE floodplain( 127 CREATE TABLE floodplain(
123 OGR_FID NUMBER(38), 128 OGR_FID NUMBER(38),
124 GEOM MDSYS.SDO_GEOMETRY, 129 GEOM MDSYS.SDO_GEOMETRY,
125 river_id NUMBER(38), 130 river_id NUMBER(38),
131 name VARCHAR(64),
126 ID NUMBER PRIMARY KEY NOT NULL 132 ID NUMBER PRIMARY KEY NOT NULL
127 ); 133 );
128 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); 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);
129 CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW 135 CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW
130 BEGIN 136 BEGIN
179 OGR_FID NUMBER(38), 185 OGR_FID NUMBER(38),
180 GEOM MDSYS.SDO_GEOMETRY, 186 GEOM MDSYS.SDO_GEOMETRY,
181 river_id NUMBER(38), 187 river_id NUMBER(38),
182 hws_facility VARCHAR2(255), 188 hws_facility VARCHAR2(255),
183 type VARCHAR2(255), 189 type VARCHAR2(255),
190 name VARCHAR(64),
184 ID NUMBER PRIMARY KEY NOT NULL 191 ID NUMBER PRIMARY KEY NOT NULL
185 ); 192 );
186 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); 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);
187 CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW 194 CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW
188 BEGIN 195 BEGIN
211 BEGIN 218 BEGIN
212 SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual; 219 SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual;
213 END; 220 END;
214 / 221 /
215 CREATE INDEX floodmaps_spatial_idx ON floodmaps(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON'); 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');
http://dive4elements.wald.intevation.org