Mercurial > dive4elements > river
comparison flys-backend/doc/schema/oracle-spatial.sql @ 5385:2da74705c29d
Scheme change: Building table is extended and got a lookup table for kind
| author | Andre Heinecke <aheinecke@intevation.de> |
|---|---|
| date | Fri, 22 Mar 2013 17:30:10 +0100 |
| parents | 59f9760e8b7f |
| children | e65fee5ea98a |
comparison
equal
deleted
inserted
replaced
| 5384:5a42ca06b93e | 5385:2da74705c29d |
|---|---|
| 75 END; | 75 END; |
| 76 / | 76 / |
| 77 --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); | 77 --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); |
| 78 | 78 |
| 79 | 79 |
| 80 -- Geodaesie/Bauwerke/Wehre.shp | 80 CREATE TABLE building_kinds( |
| 81 id NUMBER PRIMARY KEY NOT NULL, | |
| 82 name VARCHAR(64) | |
| 83 ); | |
| 84 INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige'); | |
| 85 INSERT INTO building_kinds(id, name) VALUES (1, 'Brücken'); | |
| 86 INSERT INTO building_kinds(id, name) VALUES (2, 'Wehre'); | |
| 87 INSERT INTO building_kinds(id, name) VALUES (3, 'Pegel'); | |
| 88 | |
| 89 -- Geodaesie/Bauwerke | |
| 81 CREATE SEQUENCE BUILDINGS_ID_SEQ; | 90 CREATE SEQUENCE BUILDINGS_ID_SEQ; |
| 82 CREATE TABLE buildings( | 91 CREATE TABLE buildings( |
| 83 OGR_FID NUMBER(38), | 92 OGR_FID NUMBER(38), |
| 84 GEOM MDSYS.SDO_GEOMETRY, | 93 GEOM MDSYS.SDO_GEOMETRY, |
| 85 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, | 94 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, |
| 86 name VARCHAR2(255), | 95 kind_id NUMBER(38) REFERENCES building_kinds(id) NOT NULL DEFAULT 0, |
| 96 station NUMBER(38,11) NOT NULL, | |
| 97 name VARCHAR2(255), -- The layername | |
| 98 description VARCHAR(256), -- Name taken from attributes | |
| 87 path VARCHAR(256), | 99 path VARCHAR(256), |
| 88 ID NUMBER PRIMARY KEY NOT NULL | 100 ID NUMBER PRIMARY KEY NOT NULL |
| 89 ); | 101 ); |
| 90 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('buildings', '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); | 102 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('buildings', '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); |
| 91 CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW | 103 CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW |