Mercurial > dive4elements > river
annotate backend/doc/schema/postgresql-sinfo-uinfo.sql @ 8997:d5802f22e4f5
Work on uinfo
| author | gernotbelger |
|---|---|
| date | Thu, 12 Apr 2018 19:14:19 +0200 |
| parents | 07dcedddf839 |
| children | ce99d3db9477 |
| rev | line source |
|---|---|
| 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 ); | |
|
8990
07dcedddf839
Oracle schema extension added, a few changes in the postgresql schema
mschaefer
parents:
8968
diff
changeset
|
158 COMMENT ON TABLE tkh IS 'Longitudinal section of computed Transportkörperhöhe of a river' ; |
| 8968 | 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 ); | |
|
8990
07dcedddf839
Oracle schema extension added, a few changes in the postgresql schema
mschaefer
parents:
8968
diff
changeset
|
174 COMMENT ON TABLE tkh_column IS 'Longitudinal section of computed Transportkörperhöhe for a waterlevel series' ; |
| 8968 | 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 ); | |
|
8990
07dcedddf839
Oracle schema extension added, a few changes in the postgresql schema
mschaefer
parents:
8968
diff
changeset
|
186 COMMENT ON TABLE tkh_values IS 'Transportkörperhöhe of a river station and referenced file column' ; |
| 8968 | 187 COMMENT ON COLUMN tkh_values.station IS 'River km' ; |
|
8990
07dcedddf839
Oracle schema extension added, a few changes in the postgresql schema
mschaefer
parents:
8968
diff
changeset
|
188 COMMENT ON COLUMN tkh_values.tkheight IS 'Transportkörperhöhe of a river station computed for a waterlevel in m' ; |
| 8968 | 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, | |
|
8990
07dcedddf839
Oracle schema extension added, a few changes in the postgresql schema
mschaefer
parents:
8968
diff
changeset
|
329 salix_id NUMERIC(9,0) NOT NULL CONSTRAINT cSalixValuesSalix REFERENCES salix(id) ON DELETE CASCADE, |
| 8968 | 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; |