Mercurial > dive4elements > river

comparison backend/doc/schema/postgresql-sinfo-uinfo.sql @ 9008:ce99d3db9477

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
author mschaefer
date Wed, 18 Apr 2018 07:34:28 +0200
parents 07dcedddf839
children 1f63e9d3b0ec
comparison
equal deleted inserted replaced
9007:fe561931026c 9008:ce99d3db9477
1 BEGIN; 1 BEGIN;
2 2
3
4 --Add new column wsts.sinfo_selection
5
6 ALTER TABLE wsts ADD COLUMN sinfo_selection VARCHAR(1) CHECK((sinfo_selection IS NULL) OR (sinfo_selection IN ('W','Q')));
7 COMMENT ON COLUMN wsts.sinfo_selection IS 'Whether and how the WST series is selected within S-INFO calculation types ("W"=without-Q-group, "Q"=with-Q-group, NULL=not-selectable)' ;
8
9
10 --Add new columns bed_height.sounding_width_info and bed_height.comment
11
12 ALTER TABLE bed_height ADD COLUMN sounding_width_info VARCHAR(256);
13 COMMENT ON COLUMN bed_height.sounding_width_info IS 'File header line info "ausgewertete Peilbreite"' ;
14
15 ALTER TABLE bed_height ADD COLUMN "comment" VARCHAR(256);
16 COMMENT ON COLUMN bed_height."comment" IS 'File header line info "weitere Bemerkungen"' ;
17
18
19 --Add new columns bed_height_values.min_height (Talweg) and bed_height_values.max_height (Kammlage) (same type as existing height column)
20
21 ALTER TABLE bed_height_values ADD COLUMN min_height NUMERIC;
22 COMMENT ON COLUMN bed_height_values.min_height IS 'Minimum bed height (Talweg) in m' ;
23
24 ALTER TABLE bed_height_values ADD COLUMN max_height NUMERIC;
25 COMMENT ON COLUMN bed_height_values.max_height IS 'Maximum bed height (Kammlage) in m' ;
26
27
28 --Bed mobility
3 29
4 CREATE TABLE bed_mobility ( 30 CREATE TABLE bed_mobility (
5 id NUMERIC(9,0) PRIMARY KEY, 31 id NUMERIC(9,0) PRIMARY KEY,
6 river_id integer NOT NULL CONSTRAINT cBedMobilityRivers REFERENCES rivers(id) ON DELETE CASCADE, 32 river_id integer NOT NULL CONSTRAINT cBedMobilityRivers REFERENCES rivers(id) ON DELETE CASCADE,
7 kmrange_info VARCHAR(32), 33 kmrange_info VARCHAR(32),
26 COMMENT ON COLUMN bed_mobility_values.station IS 'River km' ; 52 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' ; 53 COMMENT ON COLUMN bed_mobility_values.moving IS 'Whether the river bed at the station is moving' ;
28 54
29 CREATE SEQUENCE BED_MOBILITY_VALUES_ID_SEQ ; 55 CREATE SEQUENCE BED_MOBILITY_VALUES_ID_SEQ ;
30 56
57
58 --Infrastructure
31 59
32 CREATE TABLE infrastructure ( 60 CREATE TABLE infrastructure (
33 id NUMERIC(9,0) PRIMARY KEY, 61 id NUMERIC(9,0) PRIMARY KEY,
34 river_id integer NOT NULL CONSTRAINT cInfrastructureRivers REFERENCES rivers(id) ON DELETE CASCADE, 62 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), 63 annotation_type_id integer NOT NULL CONSTRAINT cInfrastructureAnnotationType REFERENCES annotation_types(id),
63 COMMENT ON COLUMN infrastructure_values.height IS 'Geodetic height of the top of the infrastructure in m' ; 91 COMMENT ON COLUMN infrastructure_values.height IS 'Geodetic height of the top of the infrastructure in m' ;
64 92
65 CREATE SEQUENCE INFRASTRUCTURE_VALUES_ID_SEQ ; 93 CREATE SEQUENCE INFRASTRUCTURE_VALUES_ID_SEQ ;
66 94
67 95
96 --Channel
97
68 CREATE TABLE channel ( 98 CREATE TABLE channel (
69 id NUMERIC(9,0) PRIMARY KEY, 99 id NUMERIC(9,0) PRIMARY KEY,
70 river_id integer NOT NULL CONSTRAINT cChannelRivers REFERENCES rivers(id) ON DELETE CASCADE, 100 river_id integer NOT NULL CONSTRAINT cChannelRivers REFERENCES rivers(id) ON DELETE CASCADE,
71 kmrange_info VARCHAR(32), 101 kmrange_info VARCHAR(32),
72 filename VARCHAR(256) NOT NULL, 102 filename VARCHAR(256) NOT NULL,
98 COMMENT ON COLUMN channel_values.depth IS 'Nominal depth of the channel in m' ; 128 COMMENT ON COLUMN channel_values.depth IS 'Nominal depth of the channel in m' ;
99 129
100 CREATE SEQUENCE CHANNEL_VALUES_ID_SEQ ; 130 CREATE SEQUENCE CHANNEL_VALUES_ID_SEQ ;
101 131
102 132
133 --Collision
134
103 CREATE TABLE collision_type ( 135 CREATE TABLE collision_type (
104 id NUMERIC(9,0) PRIMARY KEY, 136 id NUMERIC(9,0) PRIMARY KEY,
105 name VARCHAR(64) NOT NULL UNIQUE 137 name VARCHAR(64) NOT NULL UNIQUE
106 ); 138 );
107 COMMENT ON TABLE collision_type IS 'Type of a ship collision' ; 139 COMMENT ON TABLE collision_type IS 'Type of a ship collision' ;
143 COMMENT ON COLUMN collision_values.gauge_name IS 'Name of the gauge' ; 175 COMMENT ON COLUMN collision_values.gauge_name IS 'Name of the gauge' ;
144 176
145 CREATE SEQUENCE COLLISION_VALUES_ID_SEQ ; 177 CREATE SEQUENCE COLLISION_VALUES_ID_SEQ ;
146 178
147 179
180 --Tkh
181
148 CREATE TABLE tkh ( 182 CREATE TABLE tkh (
149 id NUMERIC(9,0) PRIMARY KEY, 183 id NUMERIC(9,0) PRIMARY KEY,
150 river_id integer NOT NULL CONSTRAINT cTkhRivers REFERENCES rivers(id) ON DELETE CASCADE, 184 river_id integer NOT NULL CONSTRAINT cTkhRivers REFERENCES rivers(id) ON DELETE CASCADE,
151 year NUMERIC(4,0) NOT NULL, 185 day DATE,
152 kmrange_info VARCHAR(32), 186 kmrange_info VARCHAR(32),
153 filename VARCHAR(256) NOT NULL, 187 filename VARCHAR(256) NOT NULL,
154 sounding_info VARCHAR(64), 188 sounding_info VARCHAR(64),
155 evaluation_by VARCHAR(256), 189 evaluation_by VARCHAR(256),
156 "comment" VARCHAR(256) 190 "comment" VARCHAR(256)
157 ); 191 );
158 COMMENT ON TABLE tkh IS 'Longitudinal section of computed Transportkörperhöhe of a river' ; 192 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"' ; 193 COMMENT ON COLUMN tkh.day IS 'File header line info "Datum"' ;
160 COMMENT ON COLUMN tkh.kmrange_info IS 'File header line info "Strecke"' ; 194 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' ; 195 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"' ; 196 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"' ; 197 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"' ; 198 COMMENT ON COLUMN tkh."comment" IS 'File header line info "weitere Bemerkungen"' ;
187 COMMENT ON COLUMN tkh_values.station IS 'River km' ; 221 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' ; 222 COMMENT ON COLUMN tkh_values.tkheight IS 'Transportkörperhöhe of a river station computed for a waterlevel in m' ;
189 223
190 CREATE SEQUENCE TKH_VALUES_ID_SEQ ; 224 CREATE SEQUENCE TKH_VALUES_ID_SEQ ;
191 225
226
227 --Flow depth
192 228
193 CREATE TABLE flow_depth ( 229 CREATE TABLE flow_depth (
194 id NUMERIC(9,0) PRIMARY KEY, 230 id NUMERIC(9,0) PRIMARY KEY,
195 river_id integer NOT NULL CONSTRAINT cFlowDepthRivers REFERENCES rivers(id) ON DELETE CASCADE, 231 river_id integer NOT NULL CONSTRAINT cFlowDepthRivers REFERENCES rivers(id) ON DELETE CASCADE,
196 year NUMERIC(4,0) NOT NULL, 232 year NUMERIC(4,0) NOT NULL,
235 COMMENT ON COLUMN flow_depth_values.station IS 'River km' ; 271 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' ; 272 COMMENT ON COLUMN flow_depth_values.depth IS 'Flow depth of a river station computed for a waterlevel in m' ;
237 273
238 CREATE SEQUENCE FLOW_DEPTH_VALUES_ID_SEQ ; 274 CREATE SEQUENCE FLOW_DEPTH_VALUES_ID_SEQ ;
239 275
276
277 --Depth evolution
240 278
241 CREATE TABLE depth_evolution ( 279 CREATE TABLE depth_evolution (
242 id NUMERIC(9,0) PRIMARY KEY, 280 id NUMERIC(9,0) PRIMARY KEY,
243 river_id integer NOT NULL CONSTRAINT cDepthEvolutionRivers REFERENCES rivers(id) ON DELETE CASCADE, 281 river_id integer NOT NULL CONSTRAINT cDepthEvolutionRivers REFERENCES rivers(id) ON DELETE CASCADE,
244 reference_year NUMERIC(4,0) NOT NULL, 282 reference_year NUMERIC(4,0) NOT NULL,
278 COMMENT ON COLUMN depth_evolution_values.change_per_year IS 'Average yearly flow depth change in the referenced period of time in m' ; 316 COMMENT ON COLUMN depth_evolution_values.change_per_year IS 'Average yearly flow depth change in the referenced period of time in m' ;
279 317
280 CREATE SEQUENCE DEPTH_EVOLUTION_VALUES_ID_SEQ ; 318 CREATE SEQUENCE DEPTH_EVOLUTION_VALUES_ID_SEQ ;
281 319
282 320
321 --Daily discharge
322
283 CREATE TABLE daily_discharge ( 323 CREATE TABLE daily_discharge (
284 id NUMERIC(9,0) PRIMARY KEY, 324 id NUMERIC(9,0) PRIMARY KEY,
285 gauge_id integer NOT NULL CONSTRAINT cDailyDischargeGauge REFERENCES gauges(id) ON DELETE CASCADE, 325 gauge_id integer NOT NULL CONSTRAINT cDailyDischargeGauge REFERENCES gauges(id) ON DELETE CASCADE,
286 filename VARCHAR(256) NOT NULL 326 filename VARCHAR(256) NOT NULL
287 ); 327 );
303 COMMENT ON COLUMN daily_discharge_values.day IS 'Date of the discharge value' ; 343 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' ; 344 COMMENT ON COLUMN daily_discharge_values.discharge IS 'Mean daily discharge in m^3/s' ;
305 345
306 CREATE SEQUENCE DAILY_DISCHARGE_VALUES_ID_SEQ ; 346 CREATE SEQUENCE DAILY_DISCHARGE_VALUES_ID_SEQ ;
307 347
348
349 --Salix line
308 350
309 CREATE TABLE salix ( 351 CREATE TABLE salix (
310 id NUMERIC(9,0) PRIMARY KEY, 352 id NUMERIC(9,0) PRIMARY KEY,
311 river_id integer NOT NULL CONSTRAINT cSalixRivers REFERENCES rivers(id) ON DELETE CASCADE, 353 river_id integer NOT NULL CONSTRAINT cSalixRivers REFERENCES rivers(id) ON DELETE CASCADE,
312 evaluation_by VARCHAR(255), 354 evaluation_by VARCHAR(255),
350 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (3, 0.3, 0.5, 'good'); 392 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'); 393 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'); 394 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'); 395 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (6, 1.5, NULL, 'very bad');
354 396
397
398 --Vegetation zone
355 399
356 CREATE TABLE vegetation_type ( 400 CREATE TABLE vegetation_type (
357 id NUMERIC(9,0) PRIMARY KEY, 401 id NUMERIC(9,0) PRIMARY KEY,
358 name VARCHAR(256) NOT NULL 402 name VARCHAR(256) NOT NULL
359 ); 403 );
http://dive4elements.wald.intevation.org