--- a/schema/gemma.sql	Fri Mar 01 15:52:53 2019 +0100
+++ b/schema/gemma.sql	Fri Mar 01 16:28:24 2019 +0100
@@ -252,6 +252,28 @@
         value int NOT NULL
     )
 
+    CREATE VIEW 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 gauges g LEFT JOIN LATERAL (
+            SELECT gauge_id, depth_reference, value
+            FROM gauges_reference_water_levels
+            ) r ON r.gauge_id = g.location
+    GROUP BY g.location
+
     CREATE TABLE gauge_measurements (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
         fk_gauge_id isrs NOT NULL REFERENCES gauges,
@@ -630,6 +652,7 @@
 
 -- Configure primary keys for geoserver views
 INSERT INTO waterway.gt_pk_metadata VALUES
+  ('waterway', 'gauges_geoserver', 'location'),
   ('waterway', 'distance_marks_geoserver', 'location_code'),
   ('waterway', 'distance_marks_ashore_geoserver', 'id'),
   ('waterway', 'bottlenecks_geoserver', 'id'),