Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql.sql @ 3820:8a75cf0841b1 pre2.7-2012-03-16

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged flys-backend/pre2.7-2012-03-16
author Thomas Arendsen Hein <thomas@intevation.de>
date Fri, 28 Sep 2012 12:14:59 +0200
parents 056b3a5aa181
children c3e049961685
comparison
equal deleted inserted replaced
3818:dc18457b1cef 3820:8a75cf0841b1
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 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
284
285 CREATE TABLE cross_section_lines (
286 id int PRIMARY KEY NOT NULL,
287 km NUMERIC NOT NULL,
288 cross_section_id int NOT NULL REFERENCES cross_sections(id),
289 UNIQUE (km, cross_section_id)
290 );
291
292 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
293
294 CREATE TABLE cross_section_points (
295 id int PRIMARY KEY NOT NULL,
296 cross_section_line_id int NOT NULL REFERENCES cross_section_lines(id),
297 col_pos int NOT NULL,
298 x NUMERIC NOT NULL,
299 y NUMERIC NOT NULL,
300 UNIQUE (cross_section_line_id, col_pos)
301 );
302
303 -- Hydraulische Kenngroessen
304
305 CREATE SEQUENCE HYKS_ID_SEQ;
306
307 CREATE TABLE hyks (
308 id int PRIMARY KEY NOT NULL,
309 river_id int NOT NULL REFERENCES rivers(id),
310 description VARCHAR(256) NOT NULL
311 );
312
313 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
314
315 CREATE TABLE hyk_entries (
316 id int PRIMARY KEY NOT NULL,
317 hyk_id int NOT NULL REFERENCES hyks(id),
318 km NUMERIC NOT NULL,
319 measure TIMESTAMP,
320 UNIQUE (hyk_id, km)
321 );
322
323 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
324
325 CREATE TABLE hyk_formations (
326 id int PRIMARY KEY NOT NULL,
327 formation_num int NOT NULL DEFAULT 0,
328 hyk_entry_id int NOT NULL REFERENCES hyk_entries(id),
329 top NUMERIC NOT NULL,
330 bottom NUMERIC NOT NULL,
331 distance_vl NUMERIC NOT NULL,
332 distance_hf NUMERIC NOT NULL,
333 distance_vr NUMERIC NOT NULL,
334 UNIQUE (hyk_entry_id, formation_num)
335 );
336
337 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
338
339 CREATE TABLE hyk_flow_zone_types (
340 id int PRIMARY KEY NOT NULL,
341 name VARCHAR(50) NOT NULL UNIQUE,
342 description VARCHAR(256)
343 );
344
345 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
346
347 CREATE TABLE hyk_flow_zones (
348 id int PRIMARY KEY NOT NULL,
349 formation_id int NOT NULL REFERENCES hyk_formations(id),
350 type_id int NOT NULL REFERENCES hyk_flow_zone_types(id),
351 a NUMERIC NOT NULL,
352 b NUMERIC NOT NULL,
353 CHECK (a <= b)
354 );
355
356 COMMIT;
http://dive4elements.wald.intevation.org