|
8968
|
1 BEGIN; |
|
|
2 |
|
|
3 |
|
|
4 CREATE TABLE bed_mobility ( |
|
|
5 id NUMERIC(9,0) PRIMARY KEY, |
|
|
6 river_id integer NOT NULL CONSTRAINT cBedMobilityRivers REFERENCES rivers(id) ON DELETE CASCADE, |
|
|
7 kmrange_info VARCHAR(32), |
|
|
8 filename VARCHAR(256) NOT NULL, |
|
|
9 "comment" VARCHAR(256) |
|
|
10 ); |
|
|
11 COMMENT ON TABLE bed_mobility IS 'Longitudinal section of the bed mobility of a river' ; |
|
|
12 COMMENT ON COLUMN bed_mobility.kmrange_info IS 'File header line info "Strecke"' ; |
|
|
13 COMMENT ON COLUMN bed_mobility.filename IS 'Name without type extension of the imported file' ; |
|
|
14 COMMENT ON COLUMN bed_mobility."comment" IS 'File header line info "weitere Bemerkungen"' ; |
|
|
15 |
|
|
16 CREATE SEQUENCE BED_MOBILITY_ID_SEQ ; |
|
|
17 |
|
|
18 |
|
|
19 CREATE TABLE bed_mobility_values ( |
|
|
20 id NUMERIC(9,0) PRIMARY KEY, |
|
|
21 bed_mobility_id NUMERIC(9,0) NOT NULL CONSTRAINT cBedMobilityValuesBedMobility REFERENCES bed_mobility(id) ON DELETE CASCADE, |
|
|
22 station NUMERIC(7,3) NOT NULL, |
|
|
23 moving NUMERIC(1,0) DEFAULT 0 NOT NULL CHECK(moving IN (0,1)) |
|
|
24 ); |
|
|
25 COMMENT ON TABLE bed_mobility_values IS 'Bed mobility of a km' ; |
|
|
26 COMMENT ON COLUMN bed_mobility_values.station IS 'River km' ; |
|
|
27 COMMENT ON COLUMN bed_mobility_values.moving IS 'Whether the river bed at the station is moving' ; |
|
|
28 |
|
|
29 CREATE SEQUENCE BED_MOBILITY_VALUES_ID_SEQ ; |
|
|
30 |
|
|
31 |
|
|
32 CREATE TABLE infrastructure ( |
|
|
33 id NUMERIC(9,0) PRIMARY KEY, |
|
|
34 river_id integer NOT NULL CONSTRAINT cInfrastructureRivers REFERENCES rivers(id) ON DELETE CASCADE, |
|
|
35 annotation_type_id integer NOT NULL CONSTRAINT cInfrastructureAnnotationType REFERENCES annotation_types(id), |
|
|
36 year NUMERIC(4,0) CHECK((year >= 1700) AND (year <= 2199)), |
|
|
37 dataprovider VARCHAR(256), |
|
|
38 evaluation_by VARCHAR(256), |
|
|
39 kmrange_info VARCHAR(32), |
|
|
40 filename VARCHAR(256) NOT NULL, |
|
|
41 "comment" VARCHAR(256) |
|
|
42 ); |
|
|
43 COMMENT ON TABLE infrastructure IS 'Longitudinal section of infrastructures of a river and a type' ; |
|
|
44 COMMENT ON COLUMN infrastructure.year IS 'File header line info "Stand"' ; |
|
|
45 COMMENT ON COLUMN infrastructure.dataprovider IS 'File header line info "Datenherkunft"' ; |
|
|
46 COMMENT ON COLUMN infrastructure.evaluation_by IS 'File header line info "Auswerter"' ; |
|
|
47 COMMENT ON COLUMN infrastructure.kmrange_info IS 'File header line info "Strecke"' ; |
|
|
48 COMMENT ON COLUMN infrastructure.filename IS 'Name without type extension of the imported file' ; |
|
|
49 COMMENT ON COLUMN infrastructure."comment" IS 'File header line info "weitere Bemerkungen"' ; |
|
|
50 |
|
|
51 CREATE SEQUENCE INFRASTRUCTURE_ID_SEQ ; |
|
|
52 |
|
|
53 |
|
|
54 CREATE TABLE infrastructure_values ( |
|
|
55 id NUMERIC(9,0) PRIMARY KEY, |
|
|
56 infrastructure_id NUMERIC(9,0) CONSTRAINT cInfrastructureValuesInfrastructure REFERENCES infrastructure(id) ON DELETE CASCADE, |
|
|
57 station NUMERIC(7,3) NOT NULL, |
|
|
58 attribute_id integer CONSTRAINT cInfrastructureValuesAttributes REFERENCES attributes(id), |
|
|
59 height NUMERIC(6,2) |
|
|
60 ); |
|
|
61 COMMENT ON TABLE infrastructure_values IS 'Infrastructure at a river station' ; |
|
|
62 COMMENT ON COLUMN infrastructure_values.station IS 'River km' ; |
|
|
63 COMMENT ON COLUMN infrastructure_values.height IS 'Geodetic height of the top of the infrastructure in m' ; |
|
|
64 |
|
|
65 CREATE SEQUENCE INFRASTRUCTURE_VALUES_ID_SEQ ; |
|
|
66 |
|
|
67 |
|
|
68 CREATE TABLE channel ( |
|
|
69 id NUMERIC(9,0) PRIMARY KEY, |
|
|
70 river_id integer NOT NULL CONSTRAINT cChannelRivers REFERENCES rivers(id) ON DELETE CASCADE, |
|
|
71 kmrange_info VARCHAR(32), |
|
|
72 filename VARCHAR(256) NOT NULL, |
|
|
73 "comment" VARCHAR(256), |
|
|
74 year_from NUMERIC(4,0), |
|
|
75 year_to NUMERIC(4,0) |
|
|
76 ); |
|
|
77 COMMENT ON TABLE channel IS 'Longitudinal section of the navigable channel of a river' ; |
|
|
78 COMMENT ON COLUMN channel.kmrange_info IS 'File header line info "Strecke"' ; |
|
|
79 COMMENT ON COLUMN channel.filename IS 'Name without type extension of the imported file' ; |
|
|
80 COMMENT ON COLUMN channel."comment" IS 'File header line info "weitere Bemerkungen"' ; |
|
|
81 COMMENT ON COLUMN channel.year_from IS 'Start year of the period for which the channel values are valid, NULL when valid for the whole past'; |
|
|
82 COMMENT ON COLUMN channel.year_to IS 'End year of the period for which the channel values are valid, NULL when unlimited'; |
|
|
83 |
|
|
84 |
|
|
85 CREATE SEQUENCE CHANNEL_ID_SEQ ; |
|
|
86 |
|
|
87 |
|
|
88 CREATE TABLE channel_values ( |
|
|
89 id NUMERIC(9,0) PRIMARY KEY, |
|
|
90 channel_id NUMERIC(9,0) CONSTRAINT cChannelValuesChannel REFERENCES channel(id) ON DELETE CASCADE, |
|
|
91 station NUMERIC(7,3) NOT NULL, |
|
|
92 width NUMERIC(6,2), |
|
|
93 depth NUMERIC(6,2) |
|
|
94 ); |
|
|
95 COMMENT ON TABLE channel_values IS 'Nominal size of the navigable channel at a river station' ; |
|
|
96 COMMENT ON COLUMN channel_values.station IS 'River km' ; |
|
|
97 COMMENT ON COLUMN channel_values.width IS 'Nominal width of the channel in m' ; |
|
|
98 COMMENT ON COLUMN channel_values.depth IS 'Nominal depth of the channel in m' ; |
|
|
99 |
|
|
100 CREATE SEQUENCE CHANNEL_VALUES_ID_SEQ ; |
|
|
101 |
|
|
102 |
|
|
103 CREATE TABLE collision_type ( |
|
|
104 id NUMERIC(9,0) PRIMARY KEY, |
|
|
105 name VARCHAR(64) NOT NULL UNIQUE |
|
|
106 ); |
|
|
107 COMMENT ON TABLE collision_type IS 'Type of a ship collision' ; |
|
|
108 COMMENT ON COLUMN collision_type.name IS 'Name of the collision type' ; |
|
|
109 |
|
|
110 CREATE SEQUENCE COLLISION_TYPE_ID_SEQ ; |
|
|
111 |
|
|
112 |
|
|
113 CREATE TABLE collision ( |
|
|
114 id NUMERIC(9,0) PRIMARY KEY, |
|
|
115 river_id integer NOT NULL CONSTRAINT cCollisionRivers REFERENCES rivers(id) ON DELETE CASCADE, |
|
|
116 year NUMERIC(4,0) NOT NULL, |
|
|
117 kmrange_info VARCHAR(32), |
|
|
118 filename VARCHAR(256) NOT NULL, |
|
|
119 "comment" VARCHAR(256) |
|
|
120 ); |
|
|
121 COMMENT ON TABLE collision IS 'Longitudinal section of ship collisions with the river bed' ; |
|
|
122 COMMENT ON COLUMN collision.year IS 'File header line info "Jahr"' ; |
|
|
123 COMMENT ON COLUMN collision.kmrange_info IS 'File header line info "Strecke"' ; |
|
|
124 COMMENT ON COLUMN collision.filename IS 'Name without type extension of the imported file' ; |
|
|
125 COMMENT ON COLUMN collision."comment" IS 'File header line info "weitere Bemerkungen"' ; |
|
|
126 |
|
|
127 CREATE SEQUENCE COLLISION_ID_SEQ ; |
|
|
128 |
|
|
129 |
|
|
130 CREATE TABLE collision_values ( |
|
|
131 id NUMERIC(9,0) PRIMARY KEY, |
|
|
132 collision_id NUMERIC(9,0) NOT NULL CONSTRAINT cCollisionValueCollision REFERENCES collision(id) ON DELETE CASCADE, |
|
|
133 station NUMERIC(7,3) NOT NULL, |
|
|
134 event_date DATE NOT NULL, |
|
|
135 gauge_w NUMERIC(6,2), |
|
|
136 gauge_name VARCHAR(64), |
|
|
137 collision_type_id NUMERIC(9,0) NOT NULL CONSTRAINT cCollisionValuesCollisionType REFERENCES collision_type(id) |
|
|
138 ); |
|
|
139 COMMENT ON TABLE collision_values IS 'Collision event' ; |
|
|
140 COMMENT ON COLUMN collision_values.station IS 'River km' ; |
|
|
141 COMMENT ON COLUMN collision_values.event_date IS 'Date of the collision' ; |
|
|
142 COMMENT ON COLUMN collision_values.gauge_w IS 'Waterlevel during the collision' ; |
|
|
143 COMMENT ON COLUMN collision_values.gauge_name IS 'Name of the gauge' ; |
|
|
144 |
|
|
145 CREATE SEQUENCE COLLISION_VALUES_ID_SEQ ; |
|
|
146 |
|
|
147 |
|
|
148 CREATE TABLE tkh ( |
|
|
149 id NUMERIC(9,0) PRIMARY KEY, |
|
|
150 river_id integer NOT NULL CONSTRAINT cTkhRivers REFERENCES rivers(id) ON DELETE CASCADE, |
|
|
151 year NUMERIC(4,0) NOT NULL, |
|
|
152 kmrange_info VARCHAR(32), |
|
|
153 filename VARCHAR(256) NOT NULL, |
|
|
154 sounding_info VARCHAR(64), |
|
|
155 evaluation_by VARCHAR(256), |
|
|
156 "comment" VARCHAR(256) |
|
|
157 ); |
|
|
158 COMMENT ON TABLE tkh IS 'Longitudinal section of computed Transportk�rperh�he of a river' ; |
|
|
159 COMMENT ON COLUMN tkh.year IS 'File header line info "Bezugsjahr"' ; |
|
|
160 COMMENT ON COLUMN tkh.kmrange_info IS 'File header line info "Strecke"' ; |
|
|
161 COMMENT ON COLUMN tkh.filename IS 'Name without type extension of the imported file' ; |
|
|
162 COMMENT ON COLUMN tkh.sounding_info IS 'File header line info "Peilung"' ; |
|
|
163 COMMENT ON COLUMN tkh.evaluation_by IS 'File header line info "Auswerter"' ; |
|
|
164 COMMENT ON COLUMN tkh."comment" IS 'File header line info "weitere Bemerkungen"' ; |
|
|
165 |
|
|
166 CREATE SEQUENCE TKH_ID_SEQ ; |
|
|
167 |
|
|
168 |
|
|
169 CREATE TABLE tkh_column ( |
|
|
170 id NUMERIC(9,0) PRIMARY KEY, |
|
|
171 tkh_id NUMERIC(9,0) NOT NULL CONSTRAINT cTkhColumnTkh REFERENCES tkh(id) ON DELETE CASCADE, |
|
|
172 name VARCHAR(64) NOT NULL |
|
|
173 ); |
|
|
174 COMMENT ON TABLE tkh_column IS 'Longitudinal section of computed Transportk�rperh�he for a waterlevel series' ; |
|
|
175 COMMENT ON COLUMN tkh_column.name IS 'Name of the tkh computation column' ; |
|
|
176 |
|
|
177 CREATE SEQUENCE TKH_COLUMN_ID_SEQ ; |
|
|
178 |
|
|
179 |
|
|
180 CREATE TABLE tkh_values ( |
|
|
181 id NUMERIC(9,0) PRIMARY KEY, |
|
|
182 tkh_column_id NUMERIC(9,0) NOT NULL CONSTRAINT cTkhValuesTkhColumn REFERENCES tkh_column(id) ON DELETE CASCADE, |
|
|
183 station NUMERIC(7,3) NOT NULL, |
|
|
184 tkheight NUMERIC(7,3) |
|
|
185 ); |
|
|
186 COMMENT ON TABLE tkh_values IS 'Transportk�rperh�he of a river station and referenced file column' ; |
|
|
187 COMMENT ON COLUMN tkh_values.station IS 'River km' ; |
|
|
188 COMMENT ON COLUMN tkh_values.tkheight IS 'Transportk�rperh�he of a river station computed for a waterlevel in m' ; |
|
|
189 |
|
|
190 CREATE SEQUENCE TKH_VALUES_ID_SEQ ; |
|
|
191 |
|
|
192 |
|
|
193 CREATE TABLE flow_depth ( |
|
|
194 id NUMERIC(9,0) PRIMARY KEY, |
|
|
195 river_id integer NOT NULL CONSTRAINT cFlowDepthRivers REFERENCES rivers(id) ON DELETE CASCADE, |
|
|
196 year NUMERIC(4,0) NOT NULL, |
|
|
197 kmrange_info VARCHAR(32), |
|
|
198 filename VARCHAR(256) NOT NULL, |
|
|
199 sounding_info VARCHAR(64), |
|
|
200 evaluation_by VARCHAR(255), |
|
|
201 "comment" VARCHAR(256) |
|
|
202 ); |
|
|
203 |
|
|
204 COMMENT ON TABLE flow_depth IS 'Longitudinal section of computed flow depth of a river' ; |
|
|
205 COMMENT ON COLUMN flow_depth.year IS 'File header line info "Bezugsjahr"' ; |
|
|
206 COMMENT ON COLUMN flow_depth.kmrange_info IS 'File header line info "Strecke"' ; |
|
|
207 COMMENT ON COLUMN flow_depth.filename IS 'Name without type extension of the imported file' ; |
|
|
208 COMMENT ON COLUMN flow_depth.sounding_info IS 'File header line info "Peilung"' ; |
|
|
209 COMMENT ON COLUMN flow_depth.evaluation_by IS 'File header line info "Auswerter"' ; |
|
|
210 COMMENT ON COLUMN flow_depth."comment" IS 'File header line info "weitere Bemerkungen"' ; |
|
|
211 |
|
|
212 CREATE SEQUENCE FLOW_DEPTH_ID_SEQ ; |
|
|
213 |
|
|
214 |
|
|
215 CREATE TABLE flow_depth_column ( |
|
|
216 id NUMERIC(9,0) PRIMARY KEY, |
|
|
217 flow_depth_id NUMERIC(9,0) NOT NULL CONSTRAINT cFlowDepthColumnFlowDepth REFERENCES flow_depth(id) ON DELETE CASCADE, |
|
|
218 name VARCHAR(64) NOT NULL |
|
|
219 ); |
|
|
220 |
|
|
221 COMMENT ON TABLE flow_depth_column IS 'Longitudinal section of computed flow depth for a waterlevel series' ; |
|
|
222 COMMENT ON COLUMN flow_depth_column.name IS 'Name of the flow depth computation column' ; |
|
|
223 |
|
|
224 CREATE SEQUENCE FLOW_DEPTH_COLUMN_ID_SEQ ; |
|
|
225 |
|
|
226 |
|
|
227 CREATE TABLE flow_depth_values ( |
|
|
228 id NUMERIC(9,0) PRIMARY KEY, |
|
|
229 flow_depth_column_id NUMERIC(9,0) NOT NULL CONSTRAINT cFlowDepthValuesFlowDepthColumn REFERENCES flow_depth_column(id) ON DELETE CASCADE, |
|
|
230 station NUMERIC(7,3) NOT NULL, |
|
|
231 depth NUMERIC(7,3) |
|
|
232 ); |
|
|
233 |
|
|
234 COMMENT ON TABLE flow_depth_values IS 'Flow depth of a river station and referenced file column' ; |
|
|
235 COMMENT ON COLUMN flow_depth_values.station IS 'River km' ; |
|
|
236 COMMENT ON COLUMN flow_depth_values.depth IS 'Flow depth of a river station computed for a waterlevel in m' ; |
|
|
237 |
|
|
238 CREATE SEQUENCE FLOW_DEPTH_VALUES_ID_SEQ ; |
|
|
239 |
|
|
240 |
|
|
241 CREATE TABLE depth_evolution ( |
|
|
242 id NUMERIC(9,0) PRIMARY KEY, |
|
|
243 river_id integer NOT NULL CONSTRAINT cDepthEvolutionRivers REFERENCES rivers(id) ON DELETE CASCADE, |
|
|
244 reference_year NUMERIC(4,0) NOT NULL, |
|
|
245 start_year NUMERIC(4,0) NOT NULL, |
|
|
246 curr_sounding VARCHAR(64) NOT NULL, |
|
|
247 old_sounding VARCHAR(64) NOT NULL, |
|
|
248 kmrange_info VARCHAR(32), |
|
|
249 curr_glw VARCHAR(64) NOT NULL, |
|
|
250 old_glw VARCHAR(64) NOT NULL, |
|
|
251 filename VARCHAR(256) NOT NULL, |
|
|
252 "comment" VARCHAR(256) |
|
|
253 ); |
|
|
254 COMMENT ON TABLE depth_evolution IS 'Longitudinal section of the evolution of the flow depth of a river' ; |
|
|
255 COMMENT ON COLUMN depth_evolution.reference_year IS 'File header line info "Bezugsjahr" (GlW)' ; |
|
|
256 COMMENT ON COLUMN depth_evolution.start_year IS 'File header line info "Ausgangsjahr" (GlW)' ; |
|
|
257 COMMENT ON COLUMN depth_evolution.curr_sounding IS 'File header line info "Aktuelle Peilung / Epoche"' ; |
|
|
258 COMMENT ON COLUMN depth_evolution.old_sounding IS 'File header line info "Historische Peilung / Epoche"' ; |
|
|
259 COMMENT ON COLUMN depth_evolution.kmrange_info IS 'File header line info "Strecke"' ; |
|
|
260 COMMENT ON COLUMN depth_evolution.curr_glw IS 'File header line info "Aktuelle Wasserspiegellage"' ; |
|
|
261 COMMENT ON COLUMN depth_evolution.old_glw IS 'File header line info "Historische Wasserspiegellage"' ; |
|
|
262 COMMENT ON COLUMN depth_evolution.filename IS 'Name without type extension of the imported file' ; |
|
|
263 COMMENT ON COLUMN depth_evolution."comment" IS 'File header line info "weitere Bemerkungen"' ; |
|
|
264 |
|
|
265 CREATE SEQUENCE DEPTH_EVOLUTION_ID_SEQ ; |
|
|
266 |
|
|
267 |
|
|
268 CREATE TABLE depth_evolution_values ( |
|
|
269 id NUMERIC(9,0) PRIMARY KEY, |
|
|
270 depth_evolution_id NUMERIC(9,0) NOT NULL CONSTRAINT cDepthEvolutionValuesDepthEvolution REFERENCES depth_evolution(id) ON DELETE CASCADE, |
|
|
271 station NUMERIC(7,3) NOT NULL, |
|
|
272 total_change NUMERIC(8,4), |
|
|
273 change_per_year NUMERIC(8,4) |
|
|
274 ); |
|
|
275 COMMENT ON TABLE depth_evolution_values IS 'Evolution of the flow depth of a river station in a referenced period of time' ; |
|
|
276 COMMENT ON COLUMN depth_evolution_values.station IS 'River km' ; |
|
|
277 COMMENT ON COLUMN depth_evolution_values.total_change IS 'Flow depth change of the whole period of time in m' ; |
|
|
278 COMMENT ON COLUMN depth_evolution_values.change_per_year IS 'Average yearly flow depth change in the referenced period of time in m' ; |
|
|
279 |
|
|
280 CREATE SEQUENCE DEPTH_EVOLUTION_VALUES_ID_SEQ ; |
|
|
281 |
|
|
282 |
|
|
283 CREATE TABLE daily_discharge ( |
|
|
284 id NUMERIC(9,0) PRIMARY KEY, |
|
|
285 gauge_id integer NOT NULL CONSTRAINT cDailyDischargeGauge REFERENCES gauges(id) ON DELETE CASCADE, |
|
|
286 filename VARCHAR(256) NOT NULL |
|
|
287 ); |
|
|
288 |
|
|
289 COMMENT ON TABLE daily_discharge IS 'Daily discharge value series' ; |
|
|
290 COMMENT ON COLUMN daily_discharge.filename IS 'Name without type extension of the imported file' ; |
|
|
291 |
|
|
292 CREATE SEQUENCE DAILY_DISCHARGE_ID_SEQ ; |
|
|
293 |
|
|
294 |
|
|
295 CREATE TABLE daily_discharge_values ( |
|
|
296 id NUMERIC(9,0) PRIMARY KEY, |
|
|
297 daily_discharge_id NUMERIC(9,0) NOT NULL CONSTRAINT cDailyDischargeValueDailyDischarge REFERENCES daily_discharge(id) ON DELETE CASCADE, |
|
|
298 day DATE NOT NULL, |
|
|
299 discharge NUMERIC(8,3) NOT NULL |
|
|
300 ); |
|
|
301 |
|
|
302 COMMENT ON TABLE daily_discharge_values IS 'Mean daily discharge of a date and a gauge' ; |
|
|
303 COMMENT ON COLUMN daily_discharge_values.day IS 'Date of the discharge value' ; |
|
|
304 COMMENT ON COLUMN daily_discharge_values.discharge IS 'Mean daily discharge in m^3/s' ; |
|
|
305 |
|
|
306 CREATE SEQUENCE DAILY_DISCHARGE_VALUES_ID_SEQ ; |
|
|
307 |
|
|
308 |
|
|
309 CREATE TABLE salix ( |
|
|
310 id NUMERIC(9,0) PRIMARY KEY, |
|
|
311 river_id integer NOT NULL CONSTRAINT cSalixRivers REFERENCES rivers(id) ON DELETE CASCADE, |
|
|
312 evaluation_by VARCHAR(255), |
|
|
313 kmrange_info VARCHAR(32), |
|
|
314 filename VARCHAR(256) NOT NULL, |
|
|
315 "comment" VARCHAR(256) |
|
|
316 ); |
|
|
317 |
|
|
318 COMMENT ON TABLE salix IS 'Longitudinal section of the salix line parameters of a river' ; |
|
|
319 COMMENT ON COLUMN salix.evaluation_by IS 'File header line info "Auswerter"' ; |
|
|
320 COMMENT ON COLUMN salix.kmrange_info IS 'File header line info "Strecke"' ; |
|
|
321 COMMENT ON COLUMN salix.filename IS 'Name without type extension of the imported file' ; |
|
|
322 COMMENT ON COLUMN salix."comment" IS 'File header line info "weitere Bemerkungen"' ; |
|
|
323 |
|
|
324 CREATE SEQUENCE SALIX_ID_SEQ ; |
|
|
325 |
|
|
326 |
|
|
327 CREATE TABLE salix_values ( |
|
|
328 id NUMERIC(9,0) PRIMARY KEY, |
|
|
329 salix_id integer NOT NULL CONSTRAINT cSalixValuesSalix REFERENCES salix(id) ON DELETE CASCADE, |
|
|
330 station NUMERIC(7,3) NOT NULL, |
|
|
331 factor NUMERIC(6,2) NOT NULL, |
|
|
332 mnw_mw_diff NUMERIC(6,2) |
|
|
333 ); |
|
|
334 |
|
|
335 COMMENT ON COLUMN salix_values.station IS 'River km' ; |
|
|
336 COMMENT ON COLUMN salix_values.factor IS 'Salix "factor" of the station in m' ; |
|
|
337 COMMENT ON COLUMN salix_values.mnw_mw_diff IS 'Difference between MNW and MW in m (less than zero)' ; |
|
|
338 |
|
|
339 CREATE SEQUENCE SALIX_VALUES_ID_SEQ ; |
|
|
340 |
|
|
341 |
|
|
342 CREATE TABLE salix_rank ( |
|
|
343 id NUMERIC(9,0) PRIMARY KEY, |
|
|
344 min_value NUMERIC(6,2), |
|
|
345 max_value NUMERIC(6,2), |
|
|
346 name VARCHAR(16) NOT NULL |
|
|
347 ); |
|
|
348 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (1, NULL, -0.3, 'invalid'); |
|
|
349 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (2, -0.3, 0.3, 'very good'); |
|
|
350 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (3, 0.3, 0.5, 'good'); |
|
|
351 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (4, 0.5, 1.0, 'moderate'); |
|
|
352 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (5, 1.0, 1.5, 'bad'); |
|
|
353 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (6, 1.5, NULL, 'very bad'); |
|
|
354 |
|
|
355 |
|
|
356 CREATE TABLE vegetation_type ( |
|
|
357 id NUMERIC(9,0) PRIMARY KEY, |
|
|
358 name VARCHAR(256) NOT NULL |
|
|
359 ); |
|
|
360 INSERT INTO vegetation_type (id, name) VALUES (1, 'zonal forest'); |
|
|
361 INSERT INTO vegetation_type (id, name) VALUES (2, 'dry hartwood forest floodplain'); |
|
|
362 INSERT INTO vegetation_type (id, name) VALUES (3, 'wet hartwood forest floodplain'); |
|
|
363 INSERT INTO vegetation_type (id, name) VALUES (4, 'salix alba forest'); |
|
|
364 INSERT INTO vegetation_type (id, name) VALUES (5, 'salix shrubs'); |
|
|
365 INSERT INTO vegetation_type (id, name) VALUES (6, 'reed bed'); |
|
|
366 INSERT INTO vegetation_type (id, name) VALUES (7, 'bank pioneers'); |
|
|
367 INSERT INTO vegetation_type (id, name) VALUES (8, 'no vegetation'); |
|
|
368 INSERT INTO vegetation_type (id, name) VALUES (9, 'water'); |
|
|
369 |
|
|
370 |
|
|
371 CREATE TABLE vegetation ( |
|
|
372 id NUMERIC(9,0) PRIMARY KEY, |
|
|
373 river_id integer NOT NULL CONSTRAINT cVegetationRivers REFERENCES rivers(id) ON DELETE CASCADE, |
|
|
374 name VARCHAR(256), |
|
|
375 filename VARCHAR(256) NOT NULL, |
|
|
376 "comment" VARCHAR(256) |
|
|
377 ); |
|
|
378 |
|
|
379 COMMENT ON TABLE vegetation IS 'Classification of the vegetation of a river' ; |
|
|
380 COMMENT ON COLUMN vegetation.name IS 'File header line info "Einteilung"' ; |
|
|
381 COMMENT ON COLUMN vegetation.filename IS 'Name without type extension of the imported file' ; |
|
|
382 COMMENT ON COLUMN vegetation."comment" IS 'File header line info "weitere Bemerkungen"' ; |
|
|
383 |
|
|
384 CREATE SEQUENCE VEGETATION_ID_SEQ ; |
|
|
385 |
|
|
386 |
|
|
387 CREATE TABLE vegetation_zone ( |
|
|
388 id NUMERIC(9,0) PRIMARY KEY, |
|
|
389 vegetation_id NUMERIC(9,0) NOT NULL CONSTRAINT cVegetationZoneVegetation REFERENCES vegetation(id) ON DELETE CASCADE, |
|
|
390 vegetation_type_id NUMERIC(9,0) NOT NULL CONSTRAINT cVegetationZoneVegetationType REFERENCES vegetation_type(id) ON DELETE CASCADE, |
|
|
391 min_overflow_days NUMERIC(3,0) NOT NULL, |
|
|
392 max_overflow_days NUMERIC(3,0) NOT NULL |
|
|
393 ); |
|
|
394 |
|
|
395 COMMENT ON TABLE vegetation_zone IS 'Vegetation zone of a river station' ; |
|
|
396 COMMENT ON COLUMN vegetation_zone.min_overflow_days IS 'Minimum number (inclusive) of overflow days in a year for the zone type' ; |
|
|
397 COMMENT ON COLUMN vegetation_zone.max_overflow_days IS 'Maximum number (exclusive) of overflow days in a year for the zone type' ; |
|
|
398 |
|
|
399 CREATE SEQUENCE VEGETATION_ZONE_ID_SEQ ; |
|
|
400 |
|
|
401 |
|
|
402 COMMIT; |
