Mercurial > gemma
changeset 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 | 4a0b62876a5f |
children | 9361f39c5f68 |
files | schema/gemma.sql |
diffstat | 1 files changed, 13 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Mon Oct 29 09:52:53 2018 +0100 +++ b/schema/gemma.sql Mon Oct 29 10:44:51 2018 +0100 @@ -500,6 +500,19 @@ CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL) ) + + CREATE VIEW waterway.bottleneck_overview AS + SELECT + objnam AS name, + ST_Centroid(area)::Geometry AS point, + (lower(stretch)).hectometre AS from, + (upper(stretch)).hectometre AS to, + sr.current + FROM waterway.bottlenecks bn LEFT JOIN ( + SELECT bottleneck_id, max(date_info) AS current FROM + waterway.sounding_results + GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.bottleneck_id + ORDER BY objnam ; --