Mercurial > dive4elements > river
comparison flys-backend/doc/schema/oracle-spatial.sql @ 2340:c4f090a00178
Added missing schemas.
flys-backend/trunk@2818 c6561f87-3c4e-4783-a992-168aeb5c3f6f
| author | Bjoern Schilberg <bjoern@intevation.de> |
|---|---|
| date | Fri, 23 Sep 2011 11:15:20 +0000 |
| parents | b28ab244a77d |
| children | 60532f75396b |
comparison
equal
deleted
inserted
replaced
| 2339:b28ab244a77d | 2340:c4f090a00178 |
|---|---|
| 51 END; | 51 END; |
| 52 / | 52 / |
| 53 --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); | 53 --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); |
| 54 | 54 |
| 55 | 55 |
| 56 -- TODO | 56 -- TODO: TestMe. Fix Importer-Script. Fix oracle_spatial_idx.sql script. |
| 57 -- Geodaesie/Linien/rohre-und-spreen | 57 -- Geodaesie/Linien/rohre-und-speeren |
| 58 CREATE SEQUENCE LINES_ID_SEQ; | |
| 59 CREATE TABLE lines ( | |
| 60 OGR_FID NUMBER(38), | |
| 61 GEOM MDSYS.SDO_GEOMETRY, | |
| 62 river_id NUMBER(38), | |
| 63 kind NUMBER(38) DEFAULT 0 NOT NULL, | |
| 64 z NUMBER(38,12) DEFAULT 0, | |
| 65 ID NUMBER PRIMARY KEY NOT NULL | |
| 66 ); | |
| 67 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('lines', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466); | |
| 68 CREATE OR REPLACE TRIGGER lines_trigger BEFORE INSERT ON lines FOR each ROW | |
| 69 BEGIN | |
| 70 SELECT LINES_ID_SEQ.nextval INTO :new.id FROM dual; | |
| 71 END; | |
| 72 / | |
| 73 -- NOTE: Should lines should be 3D. | |
| 74 -- TODO: Test index. | |
| 75 --CREATE INDEX lines_idx ON lines(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); | |
| 76 -- 'kind': | |
| 77 -- 0: ROHR1 | |
| 78 -- 1: DAMM | |
| 58 | 79 |
| 59 | 80 |
| 60 -- Geodaesie/Bauwerke/Wehre.shp | 81 -- Geodaesie/Bauwerke/Wehre.shp |
| 61 CREATE SEQUENCE BUILDINGS_ID_SEQ; | 82 CREATE SEQUENCE BUILDINGS_ID_SEQ; |
| 62 CREATE TABLE buildings( | 83 CREATE TABLE buildings( |
| 111 END; | 132 END; |
| 112 / | 133 / |
| 113 --CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON'); | 134 --CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON'); |
| 114 | 135 |
| 115 | 136 |
| 116 -- TODO | 137 -- TODO: Test-Me. Fix Importer-Skript. |
| 138 -- NOTE: It's not a spatial schema! | |
| 117 -- Geodaesie/Hoehenmodelle/* | 139 -- Geodaesie/Hoehenmodelle/* |
| 140 CREATE SEQUENCE DEM_ID_SEQ; | |
| 141 CREATE TABLE dem ( | |
| 142 ID NUMBER PRIMARY KEY NOT NULL, | |
| 143 river_id NUMBER(38), | |
| 144 -- XXX Should we use the ranges table instead? | |
| 145 lower NUMBER(38), | |
| 146 upper NUMBER(38), | |
| 147 path VARCHAR(256), | |
| 148 UNIQUE (river_id, lower, upper) | |
| 149 ); | |
| 150 CREATE OR REPLACE TRIGGER dem_trigger BEFORE INSERT ON dem FOR each ROW | |
| 151 BEGIN | |
| 152 SELECT DEM_ID_SEQ.nextval INTO :new.id FROM dual; | |
| 153 END; | |
| 154 / | |
| 118 | 155 |
| 119 | 156 |
| 120 -- Hydrologie/Einzugsgebiete/EZG.shp | 157 -- Hydrologie/Einzugsgebiete/EZG.shp |
| 121 CREATE SEQUENCE CATCHMENT_ID_SEQ; | 158 CREATE SEQUENCE CATCHMENT_ID_SEQ; |
| 122 CREATE TABLE catchment( | 159 CREATE TABLE catchment( |