Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql.sql @ 3344:cb376f48dd37 2.8

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