Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql.sql @ 1259:54365104835c 2.5

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