Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial.sql @ 5214:b46df0609276

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
add lookup tables for floodplain and cross section tracks
author Tom Gottfried <tom@intevation.de>
date Mon, 11 Mar 2013 11:43:44 +0100
parents 908848b74d7e
children 8f71fe38977c
comparison
equal deleted inserted replaced
5213:76818dc2c937 5214:b46df0609276
47 / 47 /
48 --CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point'); 48 --CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point');
49 49
50 50
51 --Geodaesie/Querprofile/QP-Spuren/qps.shp 51 --Geodaesie/Querprofile/QP-Spuren/qps.shp
52 CREATE TABLE cross_section_track_kinds(
53 id NUMBER PRIMARY KEY NOT NULL,
54 name VARCHAR(64)
55 );
56 INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige');
57 INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'Aktuell');
58
52 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; 59 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
53 CREATE TABLE cross_section_tracks ( 60 CREATE TABLE cross_section_tracks (
54 OGR_FID NUMBER(38), 61 OGR_FID NUMBER(38),
55 GEOM MDSYS.SDO_GEOMETRY, 62 GEOM MDSYS.SDO_GEOMETRY,
56 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, 63 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
64 kind_id NUMBER(38) REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0,
57 km NUMBER(38,12) NOT NULL, 65 km NUMBER(38,12) NOT NULL,
58 z NUMBER(38,12) DEFAULT 0 NOT NULL, 66 z NUMBER(38,12) DEFAULT 0 NOT NULL,
59 name VARCHAR(64), 67 name VARCHAR(64),
60 path VARCHAR(256), 68 path VARCHAR(256),
61 ID NUMBER PRIMARY KEY NOT NULL 69 ID NUMBER PRIMARY KEY NOT NULL
110 / 118 /
111 --CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT'); 119 --CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
112 120
113 121
114 -- Hydrologie/Hydr. Grenzen/talaue.shp 122 -- Hydrologie/Hydr. Grenzen/talaue.shp
123 CREATE TABLE floodplain_kinds(
124 id NUMBER PRIMARY KEY NOT NULL,
125 name VARCHAR(64)
126 );
127 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige');
128 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'Aktuell');
129
115 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; 130 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
116 CREATE TABLE floodplain( 131 CREATE TABLE floodplain(
117 OGR_FID NUMBER(38), 132 OGR_FID NUMBER(38),
118 GEOM MDSYS.SDO_GEOMETRY, 133 GEOM MDSYS.SDO_GEOMETRY,
119 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, 134 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
135 kind_id NUMBER(38) REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0,
120 name VARCHAR(64), 136 name VARCHAR(64),
121 path VARCHAR(256), 137 path VARCHAR(256),
122 ID NUMBER PRIMARY KEY NOT NULL 138 ID NUMBER PRIMARY KEY NOT NULL
123 ); 139 );
124 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodplain', '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); 140 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodplain', '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);
http://dive4elements.wald.intevation.org