Mercurial > dive4elements > river
comparison 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 |
comparison
equal
deleted
inserted
replaced
| 2799:501f00e355eb | 2800:efb10ed2af96 |
|---|---|
| 193 | 193 |
| 194 | 194 |
| 195 --Hydrologie/UeSG | 195 --Hydrologie/UeSG |
| 196 CREATE SEQUENCE FLOODMAPS_ID_SEQ; | 196 CREATE SEQUENCE FLOODMAPS_ID_SEQ; |
| 197 CREATE TABLE floodmaps ( | 197 CREATE TABLE floodmaps ( |
| 198 id NUMBER PRIMARY KEY NOT NULL, | 198 OGR_FID NUMBER(38), |
| 199 river_id NUMBER(38), | 199 GEOM MDSYS.SDO_GEOMETRY, |
| 200 river_id NUMBER(38), | |
| 201 name VARCHAR(255), | |
| 200 kind NUMBER(38), | 202 kind NUMBER(38), |
| 201 diff NUMBER(19,5), | 203 diff NUMBER(19,5), |
| 202 count NUMBER(38), | 204 count NUMBER(38), |
| 203 area NUMBER(19,5), | 205 area NUMBER(19,5), |
| 204 perimeter NUMBER(19,5) | 206 perimeter NUMBER(19,5), |
| 205 ); | 207 id NUMBER PRIMARY KEY NOT NULL |
| 208 ); | |
| 209 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); | |
| 206 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW | 210 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW |
| 207 BEGIN | 211 BEGIN |
| 208 SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual; | 212 SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual; |
| 209 END; | 213 END; |
| 210 / | 214 CREATE INDEX floodmaps_spatial_idx ON floodmaps(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON'); |
| 211 | 215 / |
| 212 | |
| 213 CREATE SEQUENCE FLOODMAP_GEOMS_SEQ; | |
| 214 CREATE TABLE floodmap_geoms ( | |
| 215 id NUMBER(38) PRIMARY KEY NOT NULL, | |
| 216 floodmaps_id NUMBER(38) NOT NULL, | |
| 217 polygons_id NUMBER(38) NOT NULL | |
| 218 ); | |
| 219 CREATE OR REPLACE TRIGGER floodmap_geoms_trigger BEFORE INSERT ON floodmap_geoms FOR each ROW | |
| 220 BEGIN | |
| 221 SELECT FLOODMAP_GEOMS_SEQ.nextval INTO :new.id FROM dual; | |
| 222 END; | |
| 223 / | |
| 224 | |
| 225 | |
| 226 CREATE SEQUENCE POLYGONS_SEQ; | |
| 227 CREATE TABLE polygons ( | |
| 228 OGR_FID NUMBER(38), | |
| 229 GEOM MDSYS.SDO_GEOMETRY, | |
| 230 id NUMBER(38) PRIMARY KEY NOT NULL | |
| 231 ); | |
| 232 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); | |
| 233 CREATE OR REPLACE TRIGGER polygons_trigger BEFORE INSERT ON polygons FOR each ROW | |
| 234 BEGIN | |
| 235 SELECT POLYGONS_SEQ.nextval INTO :new.id FROM dual; | |
| 236 END; | |
| 237 / |