--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/geoserver_views.sql	Thu Apr 11 12:01:27 2019 +0200
@@ -0,0 +1,141 @@
+CREATE OR REPLACE VIEW waterway.gauges_geoserver AS
+    SELECT
+        g.location,
+        isrs_asText(g.location) AS isrs_code,
+        g.objname,
+        g.geom,
+        g.applicability_from_km,
+        g.applicability_to_km,
+        g.validity,
+        g.zero_point,
+        g.geodref,
+        g.date_info,
+        g.source_organization,
+        json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'),
+                                                  r.value))
+            AS reference_water_levels
+    FROM waterway.gauges g LEFT JOIN LATERAL (
+            SELECT gauge_id, depth_reference, value
+            FROM waterway.gauges_reference_water_levels
+            ) r ON r.gauge_id = g.location
+    GROUP BY g.location;
+
+CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS
+    SELECT location_code,
+       isrs_asText(location_code) AS location,
+       geom::Geometry(POINT, 4326),
+       related_enc,
+       (location_code).hectometre
+    FROM waterway.distance_marks_virtual;
+
+CREATE OR REPLACE VIEW waterway.distance_marks_ashore_geoserver AS
+    SELECT id,
+       country,
+       geom::Geometry(POINT, 4326),
+       related_enc,
+       hectom,
+       catdis,
+       position_code
+    FROM waterway.distance_marks;
+
+CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS
+    WITH
+    fairway_availability_latest AS (
+        SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical
+            FROM waterway.fairway_availability
+            ORDER BY bottleneck_id, date_info DESC),
+    waterlevel_latest AS (
+        SELECT DISTINCT ON (fk_gauge_id) fk_gauge_id, water_level
+            FROM waterway.gauge_measurements
+            WHERE is_waterlevel AND NOT predicted
+            ORDER BY fk_gauge_id, measure_date DESC)
+    SELECT
+        b.id,
+        b.bottleneck_id,
+        b.objnam,
+        b.nobjnm,
+        b.stretch,
+        b.area,
+        b.rb,
+        b.lb,
+        b.responsible_country,
+        b.revisiting_time,
+        b.limiting,
+        b.date_info,
+        b.source_organization,
+        g.location AS gauge_isrs_code,
+        g.objname AS gauge_objname,
+        json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'),
+                                                  r.value))
+            AS reference_water_levels,
+        fal.date_info AS fa_date_info,
+        fal.critical AS fa_critical,
+        wl.water_level AS gm_waterlevel
+    FROM waterway.bottlenecks b
+        LEFT JOIN waterway.gauges g
+            ON b.fk_g_fid = g.location
+        LEFT JOIN waterway.gauges_reference_water_levels r
+            ON g.location = r.gauge_id
+        LEFT JOIN fairway_availability_latest fal
+            ON b.id = fal.bottleneck_id
+        LEFT JOIN waterlevel_latest wl
+            ON b.fk_g_fid = wl.fk_gauge_id
+    GROUP BY b.id, g.location, fal.date_info, fal.critical, wl.water_level;
+
+CREATE OR REPLACE VIEW waterway.stretches_geoserver AS
+    SELECT
+        id,
+        name,
+        (stretch).lower::varchar as lower,
+        (stretch).upper::varchar as upper,
+        area::Geometry(MULTIPOLYGON, 4326),
+        objnam,
+        nobjnam,
+        date_info,
+        source_organization,
+        (SELECT string_agg(country_code, ', ')
+            FROM waterway.stretch_countries
+            WHERE stretches_id = id) AS countries,
+        staging_done
+    FROM waterway.stretches;
+
+CREATE OR REPLACE VIEW waterway.sounding_results_contour_lines_geoserver AS
+    SELECT bottleneck_id,
+        date_info,
+        height,
+        CAST(lines AS geometry(multilinestring, 4326)) AS lines
+    FROM waterway.sounding_results_contour_lines cl
+        JOIN waterway.sounding_results sr ON sr.id = cl.sounding_result_id;
+
+CREATE OR REPLACE VIEW waterway.bottleneck_overview AS
+    SELECT
+        objnam AS name,
+        ST_Centroid(area)::Geometry(POINT, 4326) AS point,
+        (lower(stretch)).hectometre AS from,
+        (upper(stretch)).hectometre AS to,
+        sr.current::text,
+        responsible_country
+    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.id
+    ORDER BY objnam;
+
+CREATE OR REPLACE VIEW waterway.sounding_differences AS
+    SELECT
+        sd.id           AS id,
+        bn.objnam       AS objnam,
+        srm.date_info   AS minuend,
+        srs.date_info   AS subtrahend,
+        sdcl.height     AS height,
+        CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
+    FROM
+        caching.sounding_differences sd
+            JOIN caching.sounding_differences_contour_lines sdcl
+                ON sd.id = sdcl.sounding_differences_id
+            JOIN waterway.sounding_results srm
+                ON sd.minuend = srm.id
+            JOIN waterway.sounding_results srs
+                ON sd.subtrahend = srs.id
+            JOIN waterway.bottlenecks bn
+                ON srm.bottleneck_id = bn.id;