Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql.sql @ 5379:61bf64b102bc mapgenfix

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merge with default branch
author Christian Lins <christian.lins@intevation.de>
date Fri, 22 Mar 2013 11:25:54 +0100
parents 6dd354e7abfc
children 88cbe798cbab
comparison
equal deleted inserted replaced
5175:cfc5540a4eec 5379:61bf64b102bc
29 -- segments from/to at a river 29 -- segments from/to at a river
30 CREATE SEQUENCE RANGES_ID_SEQ; 30 CREATE SEQUENCE RANGES_ID_SEQ;
31 31
32 CREATE TABLE ranges ( 32 CREATE TABLE ranges (
33 id int PRIMARY KEY NOT NULL, 33 id int PRIMARY KEY NOT NULL,
34 river_id int NOT NULL REFERENCES rivers(id), 34 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
35 a NUMERIC NOT NULL, 35 a NUMERIC NOT NULL,
36 b NUMERIC, 36 b NUMERIC,
37 UNIQUE (river_id, a, b) 37 UNIQUE (river_id, a, b)
38 ); 38 );
39
39 40
40 -- Lage 'links', 'rechts', etc. 41 -- Lage 'links', 'rechts', etc.
41 CREATE SEQUENCE POSITIONS_ID_SEQ; 42 CREATE SEQUENCE POSITIONS_ID_SEQ;
42 43
43 CREATE TABLE positions ( 44 CREATE TABLE positions (
66 -- plus its position. 67 -- plus its position.
67 CREATE SEQUENCE ANNOTATIONS_ID_SEQ; 68 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
68 69
69 CREATE TABLE annotations ( 70 CREATE TABLE annotations (
70 id int PRIMARY KEY NOT NULL, 71 id int PRIMARY KEY NOT NULL,
71 range_id int NOT NULL REFERENCES ranges(id), 72 range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
72 attribute_id int NOT NULL REFERENCES attributes(id), 73 attribute_id int NOT NULL REFERENCES attributes(id),
73 position_id int REFERENCES positions(id), 74 position_id int REFERENCES positions(id),
74 edge_id int REFERENCES edges(id), 75 edge_id int REFERENCES edges(id),
75 type_id int REFERENCES annotation_types(id) 76 type_id int REFERENCES annotation_types(id)
76 ); 77 );
79 CREATE SEQUENCE GAUGES_ID_SEQ; 80 CREATE SEQUENCE GAUGES_ID_SEQ;
80 81
81 CREATE TABLE gauges ( 82 CREATE TABLE gauges (
82 id int PRIMARY KEY NOT NULL, 83 id int PRIMARY KEY NOT NULL,
83 name VARCHAR(256) NOT NULL, 84 name VARCHAR(256) NOT NULL,
84 river_id int NOT NULL REFERENCES rivers(id), 85 -- remove river id here because range_id references river already
85 station NUMERIC NOT NULL UNIQUE, 86 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
87 station NUMERIC NOT NULL,
86 aeo NUMERIC NOT NULL, 88 aeo NUMERIC NOT NULL,
87 official_number int8 UNIQUE, 89 official_number int8 UNIQUE,
88 90
89 -- Pegelnullpunkt 91 -- Pegelnullpunkt
90 datum NUMERIC NOT NULL, 92 datum NUMERIC NOT NULL,
91 -- Streckengueltigkeit 93 -- Streckengueltigkeit
92 range_id int REFERENCES ranges (id), 94 range_id int NOT NULL REFERENCES ranges (id) ON DELETE CASCADE,
93 95
94 UNIQUE (name, river_id), 96 UNIQUE (name, river_id),
95 UNIQUE (river_id, station) 97 UNIQUE (river_id, station)
96 ); 98 );
97 99
121 start_time TIMESTAMP NOT NULL, 123 start_time TIMESTAMP NOT NULL,
122 stop_time TIMESTAMP, 124 stop_time TIMESTAMP,
123 CHECK (start_time <= stop_time) 125 CHECK (start_time <= stop_time)
124 ); 126 );
125 127
128
126 -- Stammdaten 129 -- Stammdaten
127 CREATE SEQUENCE MAIN_VALUES_ID_SEQ; 130 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
128 131
129 CREATE TABLE main_values ( 132 CREATE TABLE main_values (
130 id int PRIMARY KEY NOT NULL, 133 id int PRIMARY KEY NOT NULL,
131 gauge_id int NOT NULL REFERENCES gauges(id), 134 gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
132 named_value_id int NOT NULL REFERENCES named_main_values(id), 135 named_value_id int NOT NULL REFERENCES named_main_values(id),
133 value NUMERIC NOT NULL, 136 value NUMERIC NOT NULL,
134 137
135 time_interval_id int REFERENCES time_intervals(id), 138 time_interval_id int REFERENCES time_intervals(id),
136 139
141 -- Abflusstafeln 144 -- Abflusstafeln
142 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; 145 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
143 146
144 CREATE TABLE discharge_tables ( 147 CREATE TABLE discharge_tables (
145 id int PRIMARY KEY NOT NULL, 148 id int PRIMARY KEY NOT NULL,
146 gauge_id int NOT NULL REFERENCES gauges(id), 149 gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
147 description VARCHAR(256) NOT NULL, 150 description VARCHAR(256) NOT NULL,
148 bfg_id VARCHAR(50), 151 bfg_id VARCHAR(50),
149 kind int NOT NULL DEFAULT 0, 152 kind int NOT NULL DEFAULT 0,
150 time_interval_id int REFERENCES time_intervals(id) 153 time_interval_id int REFERENCES time_intervals(id)
151 154
156 -- Values of the Abflusstafeln 159 -- Values of the Abflusstafeln
157 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; 160 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
158 161
159 CREATE TABLE discharge_table_values ( 162 CREATE TABLE discharge_table_values (
160 id int PRIMARY KEY NOT NULL, 163 id int PRIMARY KEY NOT NULL,
161 table_id int NOT NULL REFERENCES discharge_tables(id), 164 table_id int NOT NULL REFERENCES discharge_tables(id) ON DELETE CASCADE,
162 q NUMERIC NOT NULL, 165 q NUMERIC NOT NULL,
163 w NUMERIC NOT NULL, 166 w NUMERIC NOT NULL,
164 167
165 UNIQUE (table_id, q, w) 168 UNIQUE (table_id, q, w)
166 ); 169 );
167 170
168 -- WST files 171 -- WST files
172 --lookup table for wst kinds
173 CREATE TABLE wst_kinds (
174 id int PRIMARY KEY NOT NULL,
175 kind VARCHAR(64) NOT NULL
176 );
177 INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata');
178 INSERT INTO wst_kinds (id, kind) VALUES (1, 'basedata_additionals_marks');
179 INSERT INTO wst_kinds (id, kind) VALUES (2, 'basedata_fixations_wst');
180 INSERT INTO wst_kinds (id, kind) VALUES (3, 'basedata_officials');
181 INSERT INTO wst_kinds (id, kind) VALUES (4, 'basedata_heightmarks-points-relative_points');
182 INSERT INTO wst_kinds (id, kind) VALUES (5, 'basedata_flood-protections_relative_points');
183 INSERT INTO wst_kinds (id, kind) VALUES (6, 'morpho_waterlevel-differences');
184 INSERT INTO wst_kinds (id, kind) VALUES (7, 'morpho_waterlevels');
185
186
169 CREATE SEQUENCE WSTS_ID_SEQ; 187 CREATE SEQUENCE WSTS_ID_SEQ;
170 188
171 CREATE TABLE wsts ( 189 CREATE TABLE wsts (
172 id int PRIMARY KEY NOT NULL, 190 id int PRIMARY KEY NOT NULL,
173 river_id int NOT NULL REFERENCES rivers(id), 191 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
174 description VARCHAR(256) NOT NULL, 192 description VARCHAR(256) NOT NULL,
175 kind int NOT NULL DEFAULT 0, 193 kind int NOT NULL REFERENCES wst_kinds(id) DEFAULT 0,
176 -- TODO: more meta infos 194 -- TODO: more meta infos
177 UNIQUE (river_id, description) 195 UNIQUE (river_id, description)
178 ); 196 );
179 197
180 -- columns of WST files 198 -- columns of WST files
181 CREATE SEQUENCE WST_COLUMNS_ID_SEQ; 199 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
182 200
183 CREATE TABLE wst_columns ( 201 CREATE TABLE wst_columns (
184 id int PRIMARY KEY NOT NULL, 202 id int PRIMARY KEY NOT NULL,
185 wst_id int NOT NULL REFERENCES wsts(id), 203 wst_id int NOT NULL REFERENCES wsts(id) ON DELETE CASCADE,
186 name VARCHAR(256) NOT NULL, 204 name VARCHAR(256) NOT NULL,
187 description VARCHAR(256), 205 description VARCHAR(256),
188 position int NOT NULL DEFAULT 0, 206 position int NOT NULL DEFAULT 0,
189 207
190 time_interval_id int REFERENCES time_intervals(id), 208 time_interval_id int REFERENCES time_intervals(id),
196 -- w values in WST file column 214 -- w values in WST file column
197 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; 215 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
198 216
199 CREATE TABLE wst_column_values ( 217 CREATE TABLE wst_column_values (
200 id int PRIMARY KEY NOT NULL, 218 id int PRIMARY KEY NOT NULL,
201 wst_column_id int NOT NULL REFERENCES wst_columns(id), 219 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
202 position NUMERIC NOT NULL, 220 position NUMERIC NOT NULL,
203 w NUMERIC NOT NULL, 221 w NUMERIC NOT NULL,
204 222
205 UNIQUE (position, wst_column_id), 223 UNIQUE (position, wst_column_id),
206 UNIQUE (position, wst_column_id, w) 224 UNIQUE (position, wst_column_id, w)
209 -- bind q values to range 227 -- bind q values to range
210 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; 228 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
211 229
212 CREATE TABLE wst_q_ranges ( 230 CREATE TABLE wst_q_ranges (
213 id int PRIMARY KEY NOT NULL, 231 id int PRIMARY KEY NOT NULL,
214 range_id int NOT NULL REFERENCES ranges(id), 232 range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
215 q NUMERIC NOT NULL 233 q NUMERIC NOT NULL
216 ); 234 );
217 235
218 -- bind q ranges to wst columns 236 -- bind q ranges to wst columns
219 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; 237 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
220 238
221 CREATE TABLE wst_column_q_ranges ( 239 CREATE TABLE wst_column_q_ranges (
222 id int PRIMARY KEY NOT NULL, 240 id int PRIMARY KEY NOT NULL,
223 wst_column_id int NOT NULL REFERENCES wst_columns(id), 241 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
224 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id), 242 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id) ON DELETE CASCADE,
225 243
226 UNIQUE (wst_column_id, wst_q_range_id) 244 UNIQUE (wst_column_id, wst_q_range_id)
227 ); 245 );
228 246
229 CREATE VIEW wst_value_table AS 247 CREATE VIEW wst_value_table AS
275 293
276 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; 294 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
277 295
278 CREATE TABLE cross_sections ( 296 CREATE TABLE cross_sections (
279 id int PRIMARY KEY NOT NULL, 297 id int PRIMARY KEY NOT NULL,
280 river_id int NOT NULL REFERENCES rivers(id), 298 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
281 time_interval_id int REFERENCES time_intervals(id), 299 time_interval_id int REFERENCES time_intervals(id),
282 description VARCHAR(256) 300 description VARCHAR(256)
283 ); 301 );
284 302
285 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ; 303 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
286 304
287 CREATE TABLE cross_section_lines ( 305 CREATE TABLE cross_section_lines (
288 id int PRIMARY KEY NOT NULL, 306 id int PRIMARY KEY NOT NULL,
289 km NUMERIC NOT NULL, 307 km NUMERIC NOT NULL,
290 cross_section_id int NOT NULL REFERENCES cross_sections(id), 308 cross_section_id int NOT NULL REFERENCES cross_sections(id) ON DELETE CASCADE,
291 UNIQUE (km, cross_section_id) 309 UNIQUE (km, cross_section_id)
292 ); 310 );
293 311
294 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; 312 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
295 313
296 CREATE TABLE cross_section_points ( 314 CREATE TABLE cross_section_points (
297 id int PRIMARY KEY NOT NULL, 315 id int PRIMARY KEY NOT NULL,
298 cross_section_line_id int NOT NULL REFERENCES cross_section_lines(id), 316 cross_section_line_id int NOT NULL REFERENCES cross_section_lines(id) ON DELETE CASCADE,
299 col_pos int NOT NULL, 317 col_pos int NOT NULL,
300 x NUMERIC NOT NULL, 318 x NUMERIC NOT NULL,
301 y NUMERIC NOT NULL, 319 y NUMERIC NOT NULL,
302 UNIQUE (cross_section_line_id, col_pos) 320 UNIQUE (cross_section_line_id, col_pos)
303 ); 321 );
312 330
313 CREATE SEQUENCE HYKS_ID_SEQ; 331 CREATE SEQUENCE HYKS_ID_SEQ;
314 332
315 CREATE TABLE hyks ( 333 CREATE TABLE hyks (
316 id int PRIMARY KEY NOT NULL, 334 id int PRIMARY KEY NOT NULL,
317 river_id int NOT NULL REFERENCES rivers(id), 335 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
318 description VARCHAR(256) NOT NULL 336 description VARCHAR(256) NOT NULL
319 ); 337 );
320 338
321 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ; 339 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
322 340
323 CREATE TABLE hyk_entries ( 341 CREATE TABLE hyk_entries (
324 id int PRIMARY KEY NOT NULL, 342 id int PRIMARY KEY NOT NULL,
325 hyk_id int NOT NULL REFERENCES hyks(id), 343 hyk_id int NOT NULL REFERENCES hyks(id) ON DELETE CASCADE,
326 km NUMERIC NOT NULL, 344 km NUMERIC NOT NULL,
327 measure TIMESTAMP, 345 measure TIMESTAMP,
328 UNIQUE (hyk_id, km) 346 UNIQUE (hyk_id, km)
329 ); 347 );
330 348
331 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ; 349 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
332 350
333 CREATE TABLE hyk_formations ( 351 CREATE TABLE hyk_formations (
334 id int PRIMARY KEY NOT NULL, 352 id int PRIMARY KEY NOT NULL,
335 formation_num int NOT NULL DEFAULT 0, 353 formation_num int NOT NULL DEFAULT 0,
336 hyk_entry_id int NOT NULL REFERENCES hyk_entries(id), 354 hyk_entry_id int NOT NULL REFERENCES hyk_entries(id) ON DELETE CASCADE,
337 top NUMERIC NOT NULL, 355 top NUMERIC NOT NULL,
338 bottom NUMERIC NOT NULL, 356 bottom NUMERIC NOT NULL,
339 distance_vl NUMERIC NOT NULL, 357 distance_vl NUMERIC NOT NULL,
340 distance_hf NUMERIC NOT NULL, 358 distance_hf NUMERIC NOT NULL,
341 distance_vr NUMERIC NOT NULL, 359 distance_vr NUMERIC NOT NULL,
352 370
353 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ; 371 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
354 372
355 CREATE TABLE hyk_flow_zones ( 373 CREATE TABLE hyk_flow_zones (
356 id int PRIMARY KEY NOT NULL, 374 id int PRIMARY KEY NOT NULL,
357 formation_id int NOT NULL REFERENCES hyk_formations(id), 375 formation_id int NOT NULL REFERENCES hyk_formations(id) ON DELETE CASCADE,
358 type_id int NOT NULL REFERENCES hyk_flow_zone_types(id), 376 type_id int NOT NULL REFERENCES hyk_flow_zone_types(id),
359 a NUMERIC NOT NULL, 377 a NUMERIC NOT NULL,
360 b NUMERIC NOT NULL, 378 b NUMERIC NOT NULL,
361 CHECK (a <= b) 379 CHECK (a <= b)
362 ); 380 );
http://dive4elements.wald.intevation.org