Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql.sql @ 3962:d609fd83310a

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