Mercurial > dive4elements > river
comparison flys-backend/doc/schema/postgresql.sql @ 3335:c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
flys-backend/trunk@4834 c6561f87-3c4e-4783-a992-168aeb5c3f6f
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Fri, 29 Jun 2012 11:24:50 +0000 |
| parents | 056b3a5aa181 |
| children | 790c12c55abb |
comparison
equal
deleted
inserted
replaced
| 3334:2ae732e2c65c | 3335:c3e049961685 |
|---|---|
| 351 a NUMERIC NOT NULL, | 351 a NUMERIC NOT NULL, |
| 352 b NUMERIC NOT NULL, | 352 b NUMERIC NOT NULL, |
| 353 CHECK (a <= b) | 353 CHECK (a <= b) |
| 354 ); | 354 ); |
| 355 | 355 |
| 356 CREATE VIEW official_lines | |
| 357 AS | |
| 358 SELECT w.river_id AS river_id, | |
| 359 w.id AS wst_id, | |
| 360 wc.id AS wst_column_id, | |
| 361 wc.name AS name | |
| 362 FROM wsts w | |
| 363 JOIN wst_columns wc | |
| 364 ON wc.wst_id = w.id | |
| 365 WHERE w.kind = 3; | |
| 366 | |
| 367 CREATE VIEW q_main_values | |
| 368 AS | |
| 369 SELECT riv.id AS river_id, | |
| 370 g.id AS gauge_id, | |
| 371 g.name AS gauge_name, | |
| 372 r.a AS a, | |
| 373 r.b AS b, | |
| 374 REGEXP_REPLACE( | |
| 375 nmv.name, E'[:space:]*\\(.*\\)[:space:]*', '') AS name, | |
| 376 CAST(mv.value AS NUMERIC(38, 2)) AS value | |
| 377 FROM main_values mv | |
| 378 JOIN named_main_values nmv | |
| 379 ON mv.named_value_id = nmv.id | |
| 380 JOIN main_value_types mvt | |
| 381 ON nmv.type_id = mvt.id | |
| 382 JOIN gauges g | |
| 383 ON mv.gauge_id = g.id | |
| 384 JOIN ranges r | |
| 385 ON g.range_id = r.id | |
| 386 JOIN rivers riv | |
| 387 ON g.river_id = riv.id | |
| 388 WHERE mvt.name = 'Q' | |
| 389 ORDER BY g.id, CAST(mv.value AS NUMERIC(38,2)); | |
| 390 | |
| 391 CREATE VIEW official_q_values | |
| 392 AS | |
| 393 SELECT ol.river_id AS river_id, | |
| 394 wst_id, | |
| 395 wst_column_id, | |
| 396 gauge_id, | |
| 397 gauge_name, | |
| 398 a, | |
| 399 b, | |
| 400 ol.name, | |
| 401 value | |
| 402 FROM official_lines ol | |
| 403 JOIN q_main_values qmv | |
| 404 ON ol.river_id = qmv.river_id | |
| 405 AND ol.name = qmv.name; | |
| 406 | |
| 356 COMMIT; | 407 COMMIT; |