Mercurial > gemma
comparison schema/gemma.sql @ 1085:72cad6277e3a
Added waterway.bottleneck_overview view to database schema.
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Mon, 29 Oct 2018 10:44:51 +0100 |
| parents | 875b0a9a3c92 |
| children | 2e6b47cdb2ca |
comparison
equal
deleted
inserted
replaced
| 1084:4a0b62876a5f | 1085:72cad6277e3a |
|---|---|
| 498 OR forecast_generation_time IS NOT NULL), | 498 OR forecast_generation_time IS NOT NULL), |
| 499 value_lifetime timestamp with time zone, | 499 value_lifetime timestamp with time zone, |
| 500 CHECK(measure_type = 'minimum guaranteed' | 500 CHECK(measure_type = 'minimum guaranteed' |
| 501 OR value_lifetime IS NOT NULL) | 501 OR value_lifetime IS NOT NULL) |
| 502 ) | 502 ) |
| 503 | |
| 504 CREATE VIEW waterway.bottleneck_overview AS | |
| 505 SELECT | |
| 506 objnam AS name, | |
| 507 ST_Centroid(area)::Geometry AS point, | |
| 508 (lower(stretch)).hectometre AS from, | |
| 509 (upper(stretch)).hectometre AS to, | |
| 510 sr.current | |
| 511 FROM waterway.bottlenecks bn LEFT JOIN ( | |
| 512 SELECT bottleneck_id, max(date_info) AS current FROM | |
| 513 waterway.sounding_results | |
| 514 GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.bottleneck_id | |
| 515 ORDER BY objnam | |
| 503 ; | 516 ; |
| 504 | 517 |
| 505 -- | 518 -- |
| 506 -- Import queue and respective logging | 519 -- Import queue and respective logging |
| 507 -- | 520 -- |