CREATE OR REPLACE VIEW waterway.sounding_results_areas_geoserver AS
  SELECT bottleneck_id,
         date_info,
         height,
         CAST(areas AS geometry(multipolygon, 4326)) as areas
  FROM waterway.sounding_results_iso_areas ia
  JOIN waterway.sounding_results sr ON sr.id = ia.sounding_result_id;

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,
        sdia.height     AS height,
        CAST(sdia.areas AS geometry(multipolygon, 4326)) AS areas
    FROM caching.sounding_differences sd
        JOIN caching.sounding_differences_iso_areas sdia
            ON sd.id = sdia.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.bottleneck_id
                AND srm.date_info::timestamptz <@ bn.validity;

GRANT SELECT ON ALL TABLES IN SCHEMA public, users, waterway TO waterway_user;