Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial.sql @ 5276:170f514a4f29

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
author Tom Gottfried <tom@intevation.de>
date Wed, 13 Mar 2013 18:39:28 +0100
parents 8f71fe38977c
children 66d9389c80cb
comparison
equal deleted inserted replaced
5275:911f62917753 5276:170f514a4f29
284 SELECT HWS_POINTS_ID_SEQ.nextval INTO :new.id FROM dual; 284 SELECT HWS_POINTS_ID_SEQ.nextval INTO :new.id FROM dual;
285 END; 285 END;
286 / 286 /
287 287
288 --Hydrologie/UeSG 288 --Hydrologie/UeSG
289 CREATE TABLE floodmap_kinds (
290 id NUMBER PRIMARY KEY NOT NULL,
291 name varchar(64) NOT NULL
292 );
293 INSERT INTO floodmap_kinds VALUES (200, 'Messung');
294 INSERT INTO floodmap_kinds VALUES (111, 'Berechnung-Aktuell-BfG');
295 INSERT INTO floodmap_kinds VALUES (112, 'Berechnung-Aktuell-Bundesländer');
296 INSERT INTO floodmap_kinds VALUES (121, 'Berechnung-Potenziell-BfG');
297 INSERT INTO floodmap_kinds VALUES (122, 'Berechnung-Potenziell-Bundesländer');
298
289 CREATE SEQUENCE FLOODMAPS_ID_SEQ; 299 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
290 CREATE TABLE floodmaps ( 300 CREATE TABLE floodmaps (
291 OGR_FID NUMBER(38), 301 OGR_FID NUMBER(38),
292 GEOM MDSYS.SDO_GEOMETRY, 302 GEOM MDSYS.SDO_GEOMETRY,
293 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, 303 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
294 name VARCHAR(255), 304 name VARCHAR(255),
295 kind NUMBER(38), 305 kind NUMBER NOT NULL REFERENCES floodmap_kinds(id),
296 diff NUMBER(19,5), 306 diff NUMBER(19,5),
297 count NUMBER(38), 307 count NUMBER(38),
298 area NUMBER(19,5), 308 area NUMBER(19,5),
299 perimeter NUMBER(19,5), 309 perimeter NUMBER(19,5),
300 path VARCHAR(256), 310 path VARCHAR(256),
311 source varchar(64),
301 id NUMBER PRIMARY KEY NOT NULL 312 id NUMBER PRIMARY KEY NOT NULL
302 ); 313 );
303 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); 314 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);
304 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW 315 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW
305 BEGIN 316 BEGIN
http://dive4elements.wald.intevation.org