Mercurial > dive4elements > river

comparison backend/doc/schema/postgresql-minfo.sql @ 5838:5aa05a7a34b7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Rename modules to more fitting names.
author Sascha L. Teichmann <teichmann@intevation.de>
date Thu, 25 Apr 2013 15:23:37 +0200
parents flys-backend/doc/schema/postgresql-minfo.sql@e89d922df8ae
children 62e6598a2c4b
comparison
equal deleted inserted replaced
5837:d9901a08d0a6 5838:5aa05a7a34b7
1 BEGIN;
2
3 CREATE SEQUENCE LOCATION_SYSTEM_SEQ;
4
5 CREATE TABLE location_system (
6 id int NOT NULL,
7 name VARCHAR(32) NOT NULL,
8 description VARCHAR(255),
9 PRIMARY KEY(id)
10 );
11
12
13 CREATE SEQUENCE ELEVATION_MODEL_SEQ;
14
15 CREATE TABLE elevation_model (
16 id int NOT NULL,
17 name VARCHAR(32) NOT NULL,
18 unit_id int NOT NULL,
19 PRIMARY KEY(id),
20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
21 );
22
23
24 -- lookup table for bedheight types
25 CREATE TABLE bed_height_type (
26 id int NOT NULL,
27 name VARCHAR(64) NOT NULL,
28 PRIMARY KEY(id)
29 );
30 INSERT INTO bed_height_type VALUES (1, 'Querprofile');
31 INSERT INTO bed_height_type VALUES (2, 'Flächenpeilung');
32 INSERT INTO bed_height_type VALUES (3, 'Flächen- u. Querprofilpeilungen');
33 INSERT INTO bed_height_type VALUES (4, 'DGM');
34 INSERT INTO bed_height_type VALUES (5, 'TIN');
35 INSERT INTO bed_height_type VALUES (6, 'Modell');
36
37
38 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
39
40 CREATE TABLE bed_height_single (
41 id int NOT NULL,
42 river_id int NOT NULL,
43 year int,
44 sounding_width int,
45 type_id int NOT NULL,
46 location_system_id int NOT NULL,
47 cur_elevation_model_id int NOT NULL,
48 old_elevation_model_id int,
49 range_id int,
50 evaluation_by VARCHAR(255),
51 description VARCHAR(255),
52 PRIMARY KEY(id),
53 CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
54 CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
55 CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id),
56 CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
57 CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
58 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE
59 );
60
61
62 CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;
63
64 CREATE TABLE bed_height_epoch (
65 id int NOT NULL,
66 river_id int NOT NULL,
67 time_interval_id int NOT NULL,
68 -- sounding_with int NOT NULL,
69 -- type_id int NOT NULL,
70 cur_elevation_model_id int NOT NULL,
71 old_elevation_model_id int,
72 range_id int,
73 evaluation_by VARCHAR(255),
74 description VARCHAR(255),
75 PRIMARY KEY(id),
76 CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
77 CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
78 CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
79 CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE
80 );
81
82
83 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
84
85 CREATE TABLE bed_height_single_values (
86 id int NOT NULL,
87 bed_height_single_id int NOT NULL,
88 station NUMERIC NOT NULL,
89 height NUMERIC,
90 uncertainty NUMERIC,
91 data_gap NUMERIC,
92 sounding_width NUMERIC,
93 width NUMERIC,
94 PRIMARY KEY(id),
95 CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id) ON DELETE CASCADE
96 );
97
98
99 CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;
100
101 CREATE TABLE bed_height_epoch_values (
102 id int NOT NULL,
103 bed_height_epoch_id int NOT NULL,
104 station NUMERIC NOT NULL,
105 height NUMERIC,
106 PRIMARY KEY(id),
107 CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id) ON DELETE CASCADE
108 );
109
110
111 CREATE SEQUENCE DEPTHS_ID_SEQ;
112
113 CREATE TABLE depths (
114 id int NOT NULL,
115 lower NUMERIC NOT NULL,
116 upper NUMERIC NOT NULL,
117 PRIMARY KEY(id)
118 );
119
120
121 CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ;
122
123 CREATE TABLE sediment_density (
124 id int NOT NULL,
125 river_id int NOT NULL,
126 depth_id int NOT NULL,
127 description VARCHAR(256),
128 PRIMARY KEY(id),
129 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
130 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id)
131 );
132
133
134 CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ;
135
136 CREATE TABLE sediment_density_values (
137 id int NOT NULL,
138 sediment_density_id int NOT NULL,
139 station NUMERIC NOT NULL,
140 shore_offset NUMERIC,
141 density NUMERIC NOT NULL,
142 description VARCHAR(256),
143 year int,
144 PRIMARY KEY(id),
145 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE
146 );
147
148
149 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ;
150
151 CREATE TABLE morphologic_width (
152 id int NOT NULL,
153 river_id int NOT NULL,
154 unit_id int NOT NULL,
155 PRIMARY KEY(id),
156 CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id) ON DELETE CASCADE,
157 CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id)
158 );
159
160
161 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ;
162
163 CREATE TABLE morphologic_width_values (
164 id int NOT NULL,
165 morphologic_width_id int NOT NULL,
166 station NUMERIC NOT NULL,
167 width NUMERIC NOT NULL,
168 description VARCHAR(256),
169 PRIMARY KEY(id),
170 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE
171 );
172
173
174 CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ;
175
176 CREATE TABLE discharge_zone (
177 id int NOT NULL,
178 river_id int NOT NULL,
179 gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance
180 value NUMERIC NOT NULL,
181 lower_discharge VARCHAR(16) NOT NULL,
182 upper_discharge VARCHAR(16),
183 PRIMARY KEY(id),
184 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
185 );
186
187
188 CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ;
189
190 CREATE TABLE flow_velocity_model (
191 id int NOT NULL,
192 discharge_zone_id int NOT NULL,
193 description VARCHAR(256),
194 PRIMARY KEY (id),
195 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE
196 );
197
198
199 CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ;
200
201 CREATE TABLE flow_velocity_model_values (
202 id int NOT NULL,
203 flow_velocity_model_id int NOT NULL,
204 station NUMERIC NOT NULL,
205 q NUMERIC NOT NULL,
206 total_channel NUMERIC NOT NULL,
207 main_channel NUMERIC NOT NULL,
208 shear_stress NUMERIC NOT NULL,
209 PRIMARY KEY(id),
210 UNIQUE (station, flow_velocity_model_id),
211 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ON DELETE CASCADE
212 );
213
214
215
216 CREATE SEQUENCE FV_MEASURE_ID_SEQ;
217
218 CREATE TABLE flow_velocity_measurements (
219 id int NOT NULL,
220 river_id int NOT NULL,
221 description VARCHAR(256),
222 PRIMARY KEY (id),
223 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
224 );
225
226 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ;
227
228 CREATE TABLE flow_velocity_measure_values (
229 id int NOT NULL,
230 measurements_id int NOT NULL,
231 station NUMERIC NOT NULL,
232 datetime TIMESTAMP,
233 w NUMERIC NOT NULL,
234 q NUMERIC NOT NULL,
235 v NUMERIC NOT NULL,
236 description VARCHAR(256),
237 PRIMARY KEY (id),
238 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE
239 );
240
241
242 CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ;
243
244 CREATE TABLE grain_fraction (
245 id int NOT NULL,
246 name VARCHAR(64) NOT NULL,
247 lower NUMERIC,
248 upper NUMERIC,
249 PRIMARY KEY (id),
250 );
251
252
253 CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ;
254
255 CREATE TABLE sediment_yield (
256 id int NOT NULL,
257 river_id int NOT NULL,
258 grain_fraction_id int,
259 unit_id int NOT NULL,
260 time_interval_id int NOT NULL,
261 description VARCHAR(256),
262 PRIMARY KEY (id),
263 CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
264 CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id),
265 CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id),
266 CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
267 );
268
269
270 CREATE SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ;
271
272 CREATE TABLE sediment_yield_values (
273 id int NOT NULL,
274 sediment_yield_id int NOT NULL,
275 station NUMERIC NOT NULL,
276 value NUMERIC NOT NULL,
277 PRIMARY KEY (id),
278 CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id) ON DELETE CASCADE
279 );
280
281
282 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ;
283 CREATE TABLE measurement_station (
284 id int NOT NULL,
285 name VARCHAR(256) NOT NULL,
286 river_id int NOT NULL,
287 station NUMERIC NOT NULL,
288 range_id int,
289 measurement_type VARCHAR(64) NOT NULL,
290 riverside VARCHAR(16),
291 reference_gauge_id int,
292 -- store name of reference gauges here too, as not all are in gauges
293 reference_gauge_name VARCHAR(64),
294 observation_timerange_id int,
295 operator VARCHAR(64),
296 description VARCHAR(512),
297 PRIMARY KEY (id),
298 CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
299 CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE,
300 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id) ON DELETE CASCADE,
301 CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id)
302 );
303
304
305 CREATE SEQUENCE SQ_RELATION_ID_SEQ;
306
307 CREATE TABLE sq_relation (
308 id int NOT NULL,
309 river_id int NOT NULL,
310 time_interval_id int NOT NULL,
311 description VARCHAR(256),
312 PRIMARY KEY (id),
313 CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
314 CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
315 );
316
317
318 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ;
319
320 CREATE TABLE sq_relation_value (
321 id int NOT NULL,
322 sq_relation_id int NOT NULL,
323 measurement_station_id int NOT NULL,
324 parameter VARCHAR(1) NOT NULL,
325 a NUMERIC NOT NULL,
326 b NUMERIC NOT NULL,
327 qmax NUMERIC NOT NULL,
328 rsq NUMERIC,
329 ntot int,
330 noutl int,
331 cferguson NUMERIC,
332 cduan NUMERIC,
333 PRIMARY KEY (id),
334 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE,
335 CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id) REFERENCES measurement_station(id) ON DELETE CASCADE
336 );
337 COMMIT;
http://dive4elements.wald.intevation.org