diff flys-backend/doc/schema/oracle-spatial.sql @ 2800:efb10ed2af96

Modified the oracle db schema (relation 'floodmaps'). flys-backend/trunk@4153 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Fri, 16 Mar 2012 07:23:11 +0000
parents f3b4e32b5db6
children cdc084071c14
line wrap: on
line diff
--- a/flys-backend/doc/schema/oracle-spatial.sql	Thu Mar 08 13:32:45 2012 +0000
+++ b/flys-backend/doc/schema/oracle-spatial.sql	Fri Mar 16 07:23:11 2012 +0000
@@ -195,43 +195,21 @@
 --Hydrologie/UeSG
 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
 CREATE TABLE floodmaps (
-    id NUMBER PRIMARY KEY NOT NULL,
+    OGR_FID NUMBER(38),
+    GEOM MDSYS.SDO_GEOMETRY,
     river_id NUMBER(38),
+    name VARCHAR(255),
     kind NUMBER(38),
     diff NUMBER(19,5),
     count NUMBER(38),
     area NUMBER(19,5),
-    perimeter NUMBER(19,5)
+    perimeter NUMBER(19,5),
+    id NUMBER PRIMARY KEY NOT NULL
 );
+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);
 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW
     BEGIN
         SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual;
     END;
-/
-
-
-CREATE SEQUENCE FLOODMAP_GEOMS_SEQ;
-CREATE TABLE floodmap_geoms (
-    id           NUMBER(38) PRIMARY KEY NOT NULL,
-    floodmaps_id NUMBER(38) NOT NULL,
-    polygons_id  NUMBER(38) NOT NULL
-);
-CREATE OR REPLACE TRIGGER floodmap_geoms_trigger BEFORE INSERT ON floodmap_geoms FOR each ROW
-    BEGIN
-        SELECT FLOODMAP_GEOMS_SEQ.nextval INTO :new.id FROM dual;
-    END;
+CREATE INDEX floodmaps_spatial_idx ON floodmaps(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON');
 /
-
-
-CREATE SEQUENCE POLYGONS_SEQ;
-CREATE TABLE polygons (
-    OGR_FID NUMBER(38),
-    GEOM MDSYS.SDO_GEOMETRY,
-    id NUMBER(38) PRIMARY KEY NOT NULL
-);
-INSERT INTO USER_SDO_GEOM_METADATA VALUES ('polygons', '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);
-CREATE OR REPLACE TRIGGER polygons_trigger BEFORE INSERT ON polygons FOR each ROW
-    BEGIN
-        SELECT POLYGONS_SEQ.nextval INTO :new.id FROM dual;
-    END;
-/

http://dive4elements.wald.intevation.org