Mercurial > dive4elements > river
comparison flys-backend/doc/schema/oracle-spatial.sql @ 5291:66d9389c80cb
added tables for jetties in spatial schema
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Thu, 14 Mar 2013 12:16:58 +0100 |
| parents | 170f514a4f29 |
| children | aa407dfb9949 |
comparison
equal
deleted
inserted
replaced
| 5285:05eaecd2be42 | 5291:66d9389c80cb |
|---|---|
| 371 CREATE OR REPLACE TRIGGER gauge_location_trigger BEFORE INSERT ON gauge_location FOR EACH ROW | 371 CREATE OR REPLACE TRIGGER gauge_location_trigger BEFORE INSERT ON gauge_location FOR EACH ROW |
| 372 BEGIN | 372 BEGIN |
| 373 SELECT GAUGE_LOCATION_ID_SEQ.nextval INTO :new.id FROM dual; | 373 SELECT GAUGE_LOCATION_ID_SEQ.nextval INTO :new.id FROM dual; |
| 374 END; | 374 END; |
| 375 / | 375 / |
| 376 | |
| 377 | |
| 378 CREATE TABLE jetty_kinds( | |
| 379 id NUMBER PRIMARY KEY NOT NULL, | |
| 380 name VARCHAR(64) | |
| 381 ); | |
| 382 INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf'); | |
| 383 INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß'); | |
| 384 INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel'); | |
| 385 | |
| 386 CREATE SEQUENCE JETTIES_ID_SEQ; | |
| 387 CREATE TABLE jetties ( | |
| 388 OGR_FID NUMBER(38), | |
| 389 GEOM MDSYS.SDO_GEOMETRY, | |
| 390 id NUMBER PRIMARY KEY NOT NULL, | |
| 391 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, | |
| 392 path VARCHAR(256), | |
| 393 kind_id NUMBER(38) REFERENCES jetty_kinds(id), | |
| 394 km NUMBER(7,3), | |
| 395 z NUMBER(38,12) | |
| 396 ); | |
| 397 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('jetties', '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); | |
| 398 CREATE OR REPLACE TRIGGER jetties_trigger BEFORE INSERT ON jetties FOR EACH ROW | |
| 399 BEGIN | |
| 400 SELECT JETTIES_ID_SEQ.nextval INTO :new.id FROM dual; | |
| 401 END; | |
| 402 / |