Mercurial > dive4elements > river

comparison backend/doc/schema/postgresql.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.sql@153456f84602
children 4f35b34f4efa
comparison
equal deleted inserted replaced
5837:d9901a08d0a6 5838:5aa05a7a34b7
1 BEGIN;
2
3 CREATE SEQUENCE UNITS_ID_SEQ;
4
5 CREATE TABLE units (
6 id int PRIMARY KEY NOT NULL,
7 name VARCHAR(32) NOT NULL UNIQUE
8 );
9
10 -- Gewaesser
11 CREATE SEQUENCE RIVERS_ID_SEQ;
12
13 CREATE TABLE rivers (
14 id int PRIMARY KEY NOT NULL,
15 official_number int8 UNIQUE,
16 name VARCHAR(256) NOT NULL UNIQUE,
17 km_up BOOLEAN NOT NULL DEFAULT true,
18 wst_unit_id int NOT NULL REFERENCES units(id)
19 );
20
21 -- Bruecke, Haefen, etc.
22 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
23
24 CREATE TABLE attributes (
25 id int PRIMARY KEY NOT NULL,
26 value VARCHAR(256) NOT NULL UNIQUE
27 );
28
29 -- segments from/to at a river
30 CREATE SEQUENCE RANGES_ID_SEQ;
31
32 CREATE TABLE ranges (
33 id int PRIMARY KEY NOT NULL,
34 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
35 a NUMERIC NOT NULL,
36 b NUMERIC,
37 UNIQUE (river_id, a, b)
38 );
39
40
41 -- Lage 'links', 'rechts', etc.
42 CREATE SEQUENCE POSITIONS_ID_SEQ;
43
44 CREATE TABLE positions (
45 id int PRIMARY KEY NOT NULL,
46 value VARCHAR(256) NOT NULL UNIQUE
47 );
48
49 -- Kante 'oben', 'unten'
50 CREATE SEQUENCE EDGES_ID_SEQ;
51
52 CREATE TABLE edges (
53 id int PRIMARY KEY NOT NULL,
54 top NUMERIC,
55 bottom NUMERIC
56 );
57
58 -- Types of annotatations (Hafen, Bruecke, Zufluss, ...)
59 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
60
61 CREATE TABLE annotation_types (
62 id int PRIMARY KEY NOT NULL,
63 name VARCHAR(256) NOT NULL UNIQUE
64 );
65
66 -- Some object (eg. Hafen) at a segment of river
67 -- plus its position.
68 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
69
70 CREATE TABLE annotations (
71 id int PRIMARY KEY NOT NULL,
72 range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
73 attribute_id int NOT NULL REFERENCES attributes(id),
74 position_id int REFERENCES positions(id),
75 edge_id int REFERENCES edges(id),
76 type_id int REFERENCES annotation_types(id)
77 );
78
79 -- Pegel
80 CREATE SEQUENCE GAUGES_ID_SEQ;
81
82 CREATE TABLE gauges (
83 id int PRIMARY KEY NOT NULL,
84 name VARCHAR(256) NOT NULL,
85 -- remove river id here because range_id references river already
86 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
87 station NUMERIC NOT NULL,
88 aeo NUMERIC NOT NULL,
89 official_number int8 UNIQUE,
90
91 -- Pegelnullpunkt
92 datum NUMERIC NOT NULL,
93 -- Streckengueltigkeit
94 range_id int NOT NULL REFERENCES ranges (id) ON DELETE CASCADE,
95
96 UNIQUE (name, river_id),
97 UNIQUE (river_id, station)
98 );
99
100 -- Type of a Hauptwert 'W', 'Q', 'D', etc.
101 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
102
103 CREATE TABLE main_value_types (
104 id int PRIMARY KEY NOT NULL,
105 name VARCHAR(256) NOT NULL UNIQUE
106 );
107
108 -- Named type of a Hauptwert (eg. HQ100)
109 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
110
111 CREATE TABLE named_main_values (
112 id int PRIMARY KEY NOT NULL,
113 name VARCHAR(256) NOT NULL UNIQUE,
114 type_id int NOT NULL REFERENCES main_value_types(id)
115 );
116
117 -- Table for time intervals
118 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
119
120 CREATE TABLE time_intervals (
121 id int PRIMARY KEY NOT NULL,
122 start_time TIMESTAMP NOT NULL,
123 stop_time TIMESTAMP,
124 CHECK (start_time <= stop_time)
125 );
126
127
128 -- Stammdaten
129 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
130
131 CREATE TABLE main_values (
132 id int PRIMARY KEY NOT NULL,
133 gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
134 named_value_id int NOT NULL REFERENCES named_main_values(id),
135 value NUMERIC NOT NULL,
136
137 time_interval_id int REFERENCES time_intervals(id),
138
139 -- TODO: better checks
140 UNIQUE (gauge_id, named_value_id, time_interval_id)
141 );
142
143 -- Abflusstafeln
144 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
145
146 CREATE TABLE discharge_tables (
147 id int PRIMARY KEY NOT NULL,
148 gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
149 description VARCHAR(256) NOT NULL,
150 bfg_id VARCHAR(50),
151 kind int NOT NULL DEFAULT 0,
152 time_interval_id int REFERENCES time_intervals(id)
153
154 -- TODO: better checks
155 -- UNIQUE (gauge_id, kind, time_interval_id)
156 );
157
158 -- Values of the Abflusstafeln
159 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
160
161 CREATE TABLE discharge_table_values (
162 id int PRIMARY KEY NOT NULL,
163 table_id int NOT NULL REFERENCES discharge_tables(id) ON DELETE CASCADE,
164 q NUMERIC NOT NULL,
165 w NUMERIC NOT NULL,
166
167 UNIQUE (table_id, q, w)
168 );
169
170 -- WST files
171 --lookup table for wst kinds
172 CREATE TABLE wst_kinds (
173 id int PRIMARY KEY NOT NULL,
174 kind VARCHAR(64) NOT NULL
175 );
176 INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata');
177 INSERT INTO wst_kinds (id, kind) VALUES (1, 'basedata_additionals_marks');
178 INSERT INTO wst_kinds (id, kind) VALUES (2, 'basedata_fixations_wst');
179 INSERT INTO wst_kinds (id, kind) VALUES (3, 'basedata_officials');
180 INSERT INTO wst_kinds (id, kind) VALUES (4, 'basedata_heightmarks-points-relative_points');
181 INSERT INTO wst_kinds (id, kind) VALUES (5, 'basedata_flood-protections_relative_points');
182 INSERT INTO wst_kinds (id, kind) VALUES (6, 'morpho_waterlevel-differences');
183 INSERT INTO wst_kinds (id, kind) VALUES (7, 'morpho_waterlevels');
184
185
186 CREATE SEQUENCE WSTS_ID_SEQ;
187
188 CREATE TABLE wsts (
189 id int PRIMARY KEY NOT NULL,
190 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
191 description VARCHAR(256) NOT NULL,
192 kind int NOT NULL REFERENCES wst_kinds(id) DEFAULT 0,
193 -- TODO: more meta infos
194 UNIQUE (river_id, description)
195 );
196
197 -- columns of WST files
198 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
199
200 CREATE TABLE wst_columns (
201 id int PRIMARY KEY NOT NULL,
202 wst_id int NOT NULL REFERENCES wsts(id) ON DELETE CASCADE,
203 name VARCHAR(256) NOT NULL,
204 description VARCHAR(256),
205 position int NOT NULL DEFAULT 0,
206
207 time_interval_id int REFERENCES time_intervals(id),
208
209 UNIQUE (wst_id, name),
210 UNIQUE (wst_id, position)
211 );
212
213 -- w values in WST file column
214 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
215
216 CREATE TABLE wst_column_values (
217 id int PRIMARY KEY NOT NULL,
218 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
219 position NUMERIC NOT NULL,
220 w NUMERIC NOT NULL,
221
222 UNIQUE (position, wst_column_id),
223 UNIQUE (position, wst_column_id, w)
224 );
225
226 -- bind q values to range
227 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
228
229 CREATE TABLE wst_q_ranges (
230 id int PRIMARY KEY NOT NULL,
231 range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
232 q NUMERIC NOT NULL
233 );
234
235 -- bind q ranges to wst columns
236 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
237
238 CREATE TABLE wst_column_q_ranges (
239 id int PRIMARY KEY NOT NULL,
240 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
241 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id) ON DELETE CASCADE,
242
243 UNIQUE (wst_column_id, wst_q_range_id)
244 );
245
246 CREATE VIEW wst_value_table AS
247 SELECT wcv.position AS position,
248 w,
249 (SELECT q
250 FROM wst_column_q_ranges wcqr
251 JOIN wst_q_ranges wqr
252 ON wcqr.wst_q_range_id = wqr.id
253 JOIN ranges r
254 ON r.id = wqr.range_id
255 WHERE wcqr.wst_column_id = wc.id
256 AND wcv.position BETWEEN r.a AND r.b) AS q,
257 wc.position AS column_pos,
258 w.id AS wst_id
259 FROM wst_column_values wcv
260 JOIN wst_columns wc
261 ON wcv.wst_column_id = wc.id
262 JOIN wsts w
263 ON wc.wst_id = w.id
264 ORDER BY wcv.position ASC,
265 wc.position DESC;
266
267 -- view to select the w values of a WST
268 CREATE VIEW wst_w_values AS
269 SELECT wcv."position" AS km,
270 wcv.w AS w,
271 wc."position" AS column_pos,
272 w.id AS wst_id
273 FROM wst_column_values wcv
274 JOIN wst_columns wc ON wcv.wst_column_id = wc.id
275 JOIN wsts w ON wc.wst_id = w.id
276 ORDER BY wcv."position", wc."position";
277
278 -- view to select the q values of a WST
279 CREATE VIEW wst_q_values AS
280 SELECT wc.position AS column_pos,
281 wqr.q AS q,
282 r.a AS a,
283 r.b AS b,
284 wc.wst_id AS wst_id
285 FROM wst_column_q_ranges wcqr
286 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id
287 JOIN ranges r ON wqr.range_id = r.id
288 JOIN wst_columns wc ON wcqr.wst_column_id = wc.id
289 ORDER BY wc.position, wcqr.wst_column_id, r.a;
290
291 -- data for the cross-sections
292
293 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
294
295 CREATE TABLE cross_sections (
296 id int PRIMARY KEY NOT NULL,
297 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
298 time_interval_id int REFERENCES time_intervals(id),
299 description VARCHAR(256)
300 );
301
302 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
303
304 CREATE TABLE cross_section_lines (
305 id int PRIMARY KEY NOT NULL,
306 km NUMERIC NOT NULL,
307 cross_section_id int NOT NULL REFERENCES cross_sections(id) ON DELETE CASCADE,
308 UNIQUE (km, cross_section_id)
309 );
310
311 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
312
313 CREATE TABLE cross_section_points (
314 id int PRIMARY KEY NOT NULL,
315 cross_section_line_id int NOT NULL REFERENCES cross_section_lines(id) ON DELETE CASCADE,
316 col_pos int NOT NULL,
317 x NUMERIC NOT NULL,
318 y NUMERIC NOT NULL,
319 UNIQUE (cross_section_line_id, col_pos)
320 );
321
322 -- Indices for faster access of the points
323 CREATE INDEX cross_section_lines_km_idx
324 ON cross_section_lines(km);
325 CREATE INDEX cross_section_points_line_idx
326 ON cross_section_points(cross_section_line_id);
327
328 -- Hydraulische Kenngroessen
329
330 CREATE SEQUENCE HYKS_ID_SEQ;
331
332 CREATE TABLE hyks (
333 id int PRIMARY KEY NOT NULL,
334 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
335 description VARCHAR(256) NOT NULL
336 );
337
338 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
339
340 CREATE TABLE hyk_entries (
341 id int PRIMARY KEY NOT NULL,
342 hyk_id int NOT NULL REFERENCES hyks(id) ON DELETE CASCADE,
343 km NUMERIC NOT NULL,
344 measure TIMESTAMP,
345 UNIQUE (hyk_id, km)
346 );
347
348 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
349
350 CREATE TABLE hyk_formations (
351 id int PRIMARY KEY NOT NULL,
352 formation_num int NOT NULL DEFAULT 0,
353 hyk_entry_id int NOT NULL REFERENCES hyk_entries(id) ON DELETE CASCADE,
354 top NUMERIC NOT NULL,
355 bottom NUMERIC NOT NULL,
356 distance_vl NUMERIC NOT NULL,
357 distance_hf NUMERIC NOT NULL,
358 distance_vr NUMERIC NOT NULL,
359 UNIQUE (hyk_entry_id, formation_num)
360 );
361
362 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
363
364 CREATE TABLE hyk_flow_zone_types (
365 id int PRIMARY KEY NOT NULL,
366 name VARCHAR(50) NOT NULL UNIQUE,
367 description VARCHAR(256)
368 );
369
370 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
371
372 CREATE TABLE hyk_flow_zones (
373 id int PRIMARY KEY NOT NULL,
374 formation_id int NOT NULL REFERENCES hyk_formations(id) ON DELETE CASCADE,
375 type_id int NOT NULL REFERENCES hyk_flow_zone_types(id),
376 a NUMERIC NOT NULL,
377 b NUMERIC NOT NULL,
378 CHECK (a <= b)
379 );
380
381 CREATE VIEW official_lines
382 AS
383 SELECT w.river_id AS river_id,
384 w.id AS wst_id,
385 wc.id AS wst_column_id,
386 wc.name AS name,
387 wc.position AS wst_column_pos
388 FROM wsts w
389 JOIN wst_columns wc
390 ON wc.wst_id = w.id
391 WHERE w.kind = 3;
392
393 CREATE VIEW q_main_values
394 AS
395 SELECT riv.id AS river_id,
396 g.id AS gauge_id,
397 g.name AS gauge_name,
398 r.a AS a,
399 r.b AS b,
400 REGEXP_REPLACE(
401 nmv.name, E'[:space:]*\\(.*\\)[:space:]*', '') AS name,
402 CAST(mv.value AS NUMERIC(38, 2)) AS value
403 FROM main_values mv
404 JOIN named_main_values nmv
405 ON mv.named_value_id = nmv.id
406 JOIN main_value_types mvt
407 ON nmv.type_id = mvt.id
408 JOIN gauges g
409 ON mv.gauge_id = g.id
410 JOIN ranges r
411 ON g.range_id = r.id
412 JOIN rivers riv
413 ON g.river_id = riv.id
414 WHERE mvt.name = 'Q'
415 ORDER BY g.id, CAST(mv.value AS NUMERIC(38,2));
416
417 CREATE VIEW official_q_values
418 AS
419 SELECT ol.river_id AS river_id,
420 wst_id,
421 wst_column_id,
422 gauge_id,
423 gauge_name,
424 a,
425 b,
426 ol.name,
427 value,
428 wst_column_pos
429 FROM official_lines ol
430 JOIN q_main_values qmv
431 ON ol.river_id = qmv.river_id
432 AND ol.name = qmv.name;
433
434 CREATE VIEW wst_ranges
435 AS
436 SELECT wc.id AS wst_column_id,
437 wc.wst_id AS wst_id,
438 Min(wcv.position) AS a,
439 Max(wcv.position) AS b
440 FROM wst_columns wc
441 JOIN wst_column_values wcv
442 ON wc.id = wcv.wst_column_id
443 GROUP BY wc.id,
444 wc.wst_id;
445
446 COMMIT;
http://dive4elements.wald.intevation.org