Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql.sql @ 3807:d73c43798a99 pre2.6-2011-11-04

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged flys-backend/pre2.6-2011-11-04
author Thomas Arendsen Hein <thomas@intevation.de>
date Fri, 28 Sep 2012 12:14:51 +0200
parents 0acf28a3d28a
children fe89d6cf55fb
comparison
equal deleted inserted replaced
3806:881fcd01e056 3807:d73c43798a99
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
87 -- Pegelnullpunkt
88 datum NUMERIC NOT NULL,
89 -- Streckengueltigkeit
90 range_id int NOT NULL REFERENCES ranges (id),
91
92 UNIQUE (name, river_id),
93 UNIQUE (river_id, station)
94 );
95
96 -- Type of a Hauptwert 'W', 'Q', 'D', etc.
97 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
98
99 CREATE TABLE main_value_types (
100 id int PRIMARY KEY NOT NULL,
101 name VARCHAR(256) NOT NULL UNIQUE
102 );
103
104 -- Named type of a Hauptwert (eg. HQ100)
105 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
106
107 CREATE TABLE named_main_values (
108 id int PRIMARY KEY NOT NULL,
109 name VARCHAR(256) NOT NULL UNIQUE,
110 type_id int NOT NULL REFERENCES main_value_types(id),
111 UNIQUE (name, type_id)
112 );
113
114 -- Table for time intervals
115 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
116
117 CREATE TABLE time_intervals (
118 id int PRIMARY KEY NOT NULL,
119 start_time TIMESTAMP NOT NULL,
120 stop_time TIMESTAMP,
121 CHECK (start_time <= stop_time)
122 );
123
124 -- Stammdaten
125 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
126
127 CREATE TABLE main_values (
128 id int PRIMARY KEY NOT NULL,
129 gauge_id int NOT NULL REFERENCES gauges(id),
130 named_value_id int NOT NULL REFERENCES named_main_values(id),
131 value NUMERIC NOT NULL,
132
133 time_interval_id int REFERENCES time_intervals(id),
134
135 -- TODO: better checks
136 UNIQUE (gauge_id, named_value_id, time_interval_id)
137 );
138
139 -- Abflusstafeln
140 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
141
142 CREATE TABLE discharge_tables (
143 id int PRIMARY KEY NOT NULL,
144 gauge_id int NOT NULL REFERENCES gauges(id),
145 description VARCHAR(256) NOT NULL,
146 kind int NOT NULL DEFAULT 0,
147 time_interval_id int REFERENCES time_intervals(id)
148
149 -- TODO: better checks
150 -- UNIQUE (gauge_id, kind, time_interval_id)
151 );
152
153 -- Values of the Abflusstafeln
154 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
155
156 CREATE TABLE discharge_table_values (
157 id int PRIMARY KEY NOT NULL,
158 table_id int NOT NULL REFERENCES discharge_tables(id),
159 q NUMERIC NOT NULL,
160 w NUMERIC NOT NULL,
161
162 UNIQUE (table_id, q, w)
163 );
164
165 -- WST files
166 CREATE SEQUENCE WSTS_ID_SEQ;
167
168 CREATE TABLE wsts (
169 id int PRIMARY KEY NOT NULL,
170 river_id int NOT NULL REFERENCES rivers(id),
171 description VARCHAR(256) NOT NULL,
172 kind int NOT NULL DEFAULT 0,
173 -- TODO: more meta infos
174 UNIQUE (river_id, description)
175 );
176
177 -- columns of WST files
178 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
179
180 CREATE TABLE wst_columns (
181 id int PRIMARY KEY NOT NULL,
182 wst_id int NOT NULL REFERENCES wsts(id),
183 name VARCHAR(256) NOT NULL,
184 description VARCHAR(256),
185 position int NOT NULL DEFAULT 0,
186
187 time_interval_id int REFERENCES time_intervals(id),
188
189 UNIQUE (wst_id, name),
190 UNIQUE (wst_id, position)
191 );
192
193 -- w values in WST file column
194 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
195
196 CREATE TABLE wst_column_values (
197 id int PRIMARY KEY NOT NULL,
198 wst_column_id int NOT NULL REFERENCES wst_columns(id),
199 position NUMERIC NOT NULL,
200 w NUMERIC NOT NULL,
201
202 UNIQUE (position, wst_column_id),
203 UNIQUE (position, wst_column_id, w)
204 );
205
206 -- bind q values to range
207 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
208
209 CREATE TABLE wst_q_ranges (
210 id int PRIMARY KEY NOT NULL,
211 range_id int NOT NULL REFERENCES ranges(id),
212 q NUMERIC NOT NULL
213 );
214
215 -- bind q ranges to wst columns
216 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
217
218 CREATE TABLE wst_column_q_ranges (
219 id int PRIMARY KEY NOT NULL,
220 wst_column_id int NOT NULL REFERENCES wst_columns(id),
221 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id),
222
223 UNIQUE (wst_column_id, wst_q_range_id)
224 );
225
226 CREATE VIEW wst_value_table AS
227 SELECT wcv.position AS position,
228 w,
229 (SELECT q
230 FROM wst_column_q_ranges wcqr
231 JOIN wst_q_ranges wqr
232 ON wcqr.wst_q_range_id = wqr.id
233 JOIN ranges r
234 ON r.id = wqr.range_id
235 WHERE wcqr.wst_column_id = wc.id
236 AND wcv.position BETWEEN r.a AND r.b) AS q,
237 wc.position AS column_pos,
238 w.id AS wst_id
239 FROM wst_column_values wcv
240 JOIN wst_columns wc
241 ON wcv.wst_column_id = wc.id
242 JOIN wsts w
243 ON wc.wst_id = w.id
244 ORDER BY wcv.position ASC,
245 wc.position DESC;
246
247 -- view to select the w values of a WST
248 CREATE VIEW wst_w_values AS
249 SELECT wcv."position" AS km,
250 wcv.w AS w,
251 wc."position" AS column_pos,
252 w.id AS wst_id
253 FROM wst_column_values wcv
254 JOIN wst_columns wc ON wcv.wst_column_id = wc.id
255 JOIN wsts w ON wc.wst_id = w.id
256 ORDER BY wcv."position", wc."position";
257
258 -- view to select the q values of a WST
259 CREATE VIEW wst_q_values AS
260 SELECT wc.position AS column_pos,
261 wqr.q AS q,
262 r.a AS a,
263 r.b AS b,
264 wc.wst_id AS wst_id
265 FROM wst_column_q_ranges wcqr
266 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id
267 JOIN ranges r ON wqr.range_id = r.id
268 JOIN wst_columns wc ON wcqr.wst_column_id = wc.id
269 ORDER BY wc.position, wcqr.wst_column_id, r.a;
270
271 -- data for the cross-sections
272
273 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
274
275 CREATE TABLE cross_sections (
276 id int PRIMARY KEY NOT NULL,
277 river_id int NOT NULL REFERENCES rivers(id),
278 time_interval_id int REFERENCES time_intervals(id),
279 description VARCHAR(256)
280 );
281
282 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
283
284 CREATE TABLE cross_section_lines (
285 id int PRIMARY KEY NOT NULL,
286 km NUMERIC NOT NULL,
287 cross_section_id int NOT NULL REFERENCES cross_sections(id),
288 UNIQUE (km, cross_section_id)
289 );
290
291 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
292
293 CREATE TABLE cross_section_points (
294 id int PRIMARY KEY NOT NULL,
295 cross_section_line_id int NOT NULL REFERENCES cross_section_lines(id),
296 col_pos int NOT NULL,
297 x NUMERIC NOT NULL,
298 y NUMERIC NOT NULL,
299 UNIQUE (cross_section_line_id, col_pos)
300 );
301
302 -- Hydraulische Kenngroessen
303
304 CREATE SEQUENCE HYKS_ID_SEQ;
305
306 CREATE TABLE hyks (
307 id int PRIMARY KEY NOT NULL,
308 river_id int NOT NULL REFERENCES rivers(id),
309 description VARCHAR(256) NOT NULL
310 );
311
312 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
313
314 CREATE TABLE hyk_entries (
315 id int PRIMARY KEY NOT NULL,
316 hyk_id int NOT NULL REFERENCES hyks(id),
317 km NUMERIC NOT NULL,
318 measure TIMESTAMP,
319 UNIQUE (hyk_id, km)
320 );
321
322 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
323
324 CREATE TABLE hyk_formations (
325 id int PRIMARY KEY NOT NULL,
326 formation_num int NOT NULL DEFAULT 0,
327 hyk_entry_id int NOT NULL REFERENCES hyk_entries(id),
328 top NUMERIC NOT NULL,
329 bottom NUMERIC NOT NULL,
330 distance_vl NUMERIC NOT NULL,
331 distance_hf NUMERIC NOT NULL,
332 distance_vr NUMERIC NOT NULL,
333 UNIQUE (hyk_entry_id, formation_num)
334 );
335
336 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
337
338 CREATE TABLE hyk_flow_zone_types (
339 id int PRIMARY KEY NOT NULL,
340 name VARCHAR(50) NOT NULL UNIQUE,
341 description VARCHAR(256)
342 );
343
344 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
345
346 CREATE TABLE hyk_flow_zones (
347 id int PRIMARY KEY NOT NULL,
348 formation_id int NOT NULL REFERENCES hyk_formations(id),
349 type_id int NOT NULL REFERENCES hyk_flow_zone_types(id),
350 a NUMERIC NOT NULL,
351 b NUMERIC NOT NULL,
352 CHECK (a <= b)
353 );
354
355 COMMIT;
http://dive4elements.wald.intevation.org