Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial.sql @ 5122:5ba502e78e05 dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix import of hws_* on Oracle The OCI driver needs a field named OGR_FID as the first element of a table, otherwise it gets confused and tries to add the fid twice in the insert statement leading to the "too many values" error.
author Andre Heinecke <aheinecke@intevation.de>
date Thu, 28 Feb 2013 10:09:31 +0100
parents e4cc9aebfcf1
children 3d8000616ed5
comparison
equal deleted inserted replaced
5121:e4cc9aebfcf1 5122:5ba502e78e05
181 181
182 --Hydrologie/HW-Schutzanlagen/hws.shp 182 --Hydrologie/HW-Schutzanlagen/hws.shp
183 -- HWS-Lines 183 -- HWS-Lines
184 CREATE SEQUENCE HWS_LINES_ID_SEQ; 184 CREATE SEQUENCE HWS_LINES_ID_SEQ;
185 CREATE TABLE hws_lines ( 185 CREATE TABLE hws_lines (
186 id NUMBER PRIMARY KEY NOT NULL, 186 OGR_FID NUMBER(38),
187 GEOM MDSYS.SDO_GEOMETRY, 187 GEOM MDSYS.SDO_GEOMETRY,
188 ogr_fid NUMBER,
189 kind_id NUMBER(2) DEFAULT 2 REFERENCES hws_kinds(id), 188 kind_id NUMBER(2) DEFAULT 2 REFERENCES hws_kinds(id),
190 fed_state_id NUMBER(2) REFERENCES fed_states(id), 189 fed_state_id NUMBER(2) REFERENCES fed_states(id),
191 river_id NUMBER(38) REFERENCES rivers(id), 190 river_id NUMBER(38) REFERENCES rivers(id),
192 name VARCHAR(256), 191 name VARCHAR(256),
193 path VARCHAR(256), 192 path VARCHAR(256),
195 agency VARCHAR(256), 194 agency VARCHAR(256),
196 range VARCHAR(256), 195 range VARCHAR(256),
197 shore_side NUMBER DEFAULT 0, 196 shore_side NUMBER DEFAULT 0,
198 source VARCHAR(256), 197 source VARCHAR(256),
199 status_date TIMESTAMP, 198 status_date TIMESTAMP,
200 description VARCHAR(256) 199 description VARCHAR(256),
200 id NUMBER PRIMARY KEY NOT NULL
201 ); 201 );
202 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); 202 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);
203 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW 203 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW
204 BEGIN 204 BEGIN
205 SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual; 205 SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual;
233 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige'); 233 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
234 234
235 -- HWS Points 235 -- HWS Points
236 CREATE SEQUENCE HWS_POINTS_ID_SEQ; 236 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
237 CREATE TABLE hws_points ( 237 CREATE TABLE hws_points (
238 id NUMBER PRIMARY KEY NOT NULL, 238 OGR_FID NUMBER(38),
239 GEOM MDSYS.SDO_GEOMETRY, 239 GEOM MDSYS.SDO_GEOMETRY,
240 ogr_fid NUMBER, 240 ogr_fid NUMBER,
241 kind_id NUMBER DEFAULT 2 REFERENCES hws_kinds(id), 241 kind_id NUMBER DEFAULT 2 REFERENCES hws_kinds(id),
242 fed_state_id NUMBER REFERENCES fed_states(id), 242 fed_state_id NUMBER REFERENCES fed_states(id),
243 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, 243 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
252 description VARCHAR(256), 252 description VARCHAR(256),
253 freeboard NUMBER(19,5), 253 freeboard NUMBER(19,5),
254 dike_km NUMBER(19,5), 254 dike_km NUMBER(19,5),
255 z NUMBER(19,5), 255 z NUMBER(19,5),
256 z_target NUMBER(19,5), 256 z_target NUMBER(19,5),
257 rated_level NUMBER(19,5) 257 rated_level NUMBER(19,5),
258 id NUMBER PRIMARY KEY NOT NULL
258 ); 259 );
259 260
260 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); 261 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);
261 262
262 CREATE OR REPLACE TRIGGER hws_points_trigger BEFORE INSERT ON hws_points FOR each ROW 263 CREATE OR REPLACE TRIGGER hws_points_trigger BEFORE INSERT ON hws_points FOR each ROW
http://dive4elements.wald.intevation.org