annotate backend/doc/schema/postgresql-sinfo-uinfo.sql @ 8969:fe81eb39080c

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

http://dive4elements.wald.intevation.org