Mercurial > dive4elements > river
comparison flys-backend/doc/schema/oracle-spatial.sql @ 2796:f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
flys-backend/trunk@4125 c6561f87-3c4e-4783-a992-168aeb5c3f6f
| author | Ingo Weinzierl <ingo.weinzierl@intevation.de> |
|---|---|
| date | Tue, 06 Mar 2012 11:54:41 +0000 |
| parents | 7d378970d764 |
| children | efb10ed2af96 |
comparison
equal
deleted
inserted
replaced
| 2795:c41bb1293acb | 2796:f3b4e32b5db6 |
|---|---|
| 188 BEGIN | 188 BEGIN |
| 189 SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual; | 189 SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual; |
| 190 END; | 190 END; |
| 191 / | 191 / |
| 192 --CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); | 192 --CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); |
| 193 | |
| 194 | |
| 195 --Hydrologie/UeSG | |
| 196 CREATE SEQUENCE FLOODMAPS_ID_SEQ; | |
| 197 CREATE TABLE floodmaps ( | |
| 198 id NUMBER PRIMARY KEY NOT NULL, | |
| 199 river_id NUMBER(38), | |
| 200 kind NUMBER(38), | |
| 201 diff NUMBER(19,5), | |
| 202 count NUMBER(38), | |
| 203 area NUMBER(19,5), | |
| 204 perimeter NUMBER(19,5) | |
| 205 ); | |
| 206 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW | |
| 207 BEGIN | |
| 208 SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual; | |
| 209 END; | |
| 210 / | |
| 211 | |
| 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 / |