Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial.sql @ 2426:f97e9b810e58 2.6.1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged flys-backend/2.6.1
author Thomas Arendsen Hein <thomas@intevation.de>
date Fri, 28 Sep 2012 12:14:26 +0200
parents 7d378970d764
children f3b4e32b5db6
comparison
equal deleted inserted replaced
2424:092e519ff461 2426:f97e9b810e58
1 -- Geodaesie/Flussachse+km/achse
2 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
3 CREATE TABLE river_axes(
4 OGR_FID NUMBER(38),
5 GEOM MDSYS.SDO_GEOMETRY,
6 river_id NUMBER(38),
7 kind NUMBER(38) DEFAULT 0 NOT NULL,
8 ID NUMBER PRIMARY KEY NOT NULL
9 );
10 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes', '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);
11 CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW
12 BEGIN
13 SELECT RIVER_AXES_ID_SEQ.nextval INTO :new.id FROM dual;
14 END;
15 /
16 --CREATE INDEX river_axes_spatial_idx ON river_axes(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
17
18
19 -- Geodaesie/Flussachse+km/km.shp
20 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
21 CREATE TABLE river_axes_km(
22 OGR_FID NUMBER(38),
23 GEOM MDSYS.SDO_GEOMETRY,
24 river_id NUMBER(38),
25 km NUMBER(6,3),
26 ID NUMBER PRIMARY KEY NOT NULL
27 );
28 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes_km', '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);
29 CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW
30 BEGIN
31 SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual;
32 END;
33 /
34 --CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point');
35
36
37 --Geodaesie/Querprofile/QP-Spuren/qps.shp
38 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
39 CREATE TABLE cross_section_tracks (
40 OGR_FID NUMBER(38),
41 GEOM MDSYS.SDO_GEOMETRY,
42 river_id NUMBER(38),
43 km NUMBER(38,12) NOT NULL,
44 z NUMBER(38,12) DEFAULT 0 NOT NULL,
45 ID NUMBER PRIMARY KEY NOT NULL
46 );
47 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('cross_section_tracks', '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);
48 CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW
49 BEGIN
50 SELECT CROSS_SECTION_TRACKS_ID_SEQ.nextval INTO :new.id FROM dual;
51 END;
52 /
53 --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
54
55
56 -- TODO: TestMe. Fix Importer-Script. Fix oracle_spatial_idx.sql script.
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 VARCHAR2(16) 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',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
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
79
80
81 -- Geodaesie/Bauwerke/Wehre.shp
82 CREATE SEQUENCE BUILDINGS_ID_SEQ;
83 CREATE TABLE buildings(
84 OGR_FID NUMBER(38),
85 GEOM MDSYS.SDO_GEOMETRY,
86 river_id NUMBER(38),
87 name VARCHAR2(255),
88 ID NUMBER PRIMARY KEY NOT NULL
89 );
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);
91 CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW
92 BEGIN
93 SELECT BUILDINGS_ID_SEQ.nextval INTO :new.id FROM dual;
94 END;
95 /
96 --CREATE INDEX buildings_spatial_idx ON buildings(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
97
98
99 -- Geodaesie/Festpunkte/Festpunkte.shp
100 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
101 CREATE TABLE fixpoints (
102 OGR_FID NUMBER(38),
103 GEOM MDSYS.SDO_GEOMETRY,
104 river_id NUMBER(38),
105 x NUMBER(38,11),
106 y NUMBER(38,11),
107 km NUMBER(38,11) NOT NULL,
108 HPGP VARCHAR2(255),
109 ID NUMBER PRIMARY KEY NOT NULL
110 );
111 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('fixpoints', '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);
112 CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW
113 BEGIN
114 SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual;
115 END;
116 /
117 --CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
118
119
120 -- Hydrologie/Hydr. Grenzen/talaue.shp
121 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
122 CREATE TABLE floodplain(
123 OGR_FID NUMBER(38),
124 GEOM MDSYS.SDO_GEOMETRY,
125 river_id NUMBER(38),
126 ID NUMBER PRIMARY KEY NOT NULL
127 );
128 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);
129 CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW
130 BEGIN
131 SELECT FLOODPLAIN_ID_SEQ.nextval INTO :new.id FROM dual;
132 END;
133 /
134 --CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON');
135
136
137 -- TODO: Test-Me. Fix Importer-Skript.
138 -- NOTE: It's not a spatial schema!
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 /
155
156
157 -- Hydrologie/Einzugsgebiete/EZG.shp
158 CREATE SEQUENCE CATCHMENT_ID_SEQ;
159 CREATE TABLE catchment(
160 OGR_FID NUMBER(38),
161 GEOM MDSYS.SDO_GEOMETRY,
162 river_id NUMBER(38),
163 area NUMBER(19,5),
164 name VARCHAR2(255),
165 ID NUMBER PRIMARY KEY NOT NULL
166 );
167 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('CATCHMENT', '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);
168
169 CREATE TRIGGER catchment_trigger BEFORE INSERT ON catchment FOR each ROW
170 BEGIN
171 SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual;
172 END;
173 /
174 --CREATE INDEX catchment_spatial_idx ON catchment(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=polygon');
175
176 --Hydrologie/HW-Schutzanlagen/hws.shp
177 CREATE SEQUENCE HWS_ID_SEQ;
178 CREATE TABLE hws(
179 OGR_FID NUMBER(38),
180 GEOM MDSYS.SDO_GEOMETRY,
181 river_id NUMBER(38),
182 hws_facility VARCHAR2(255),
183 type VARCHAR2(255),
184 ID NUMBER PRIMARY KEY NOT NULL
185 );
186 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws', '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);
187 CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW
188 BEGIN
189 SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual;
190 END;
191 /
192 --CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
http://dive4elements.wald.intevation.org