Mercurial > gemma

changeset 1929:f538d9a23329

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Better suited GeoServer view for waterway.stretches.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Mon, 21 Jan 2019 11:44:31 +0100
parents 76ca071cb006
children 1aa864cccddc
files schema/demo-data/published_services.sql schema/gemma.sql
diffstat 2 files changed, 19 insertions(+), 1 deletions(-) [+]
line wrap: on
line diff
--- a/schema/demo-data/published_services.sql	Mon Jan 21 11:37:24 2019 +0100
+++ b/schema/demo-data/published_services.sql	Mon Jan 21 11:44:31 2019 +0100
@@ -12,7 +12,7 @@
 --  * Tom Gottfried <tom@intevation.de>
 
 INSERT INTO sys_admin.published_services (name) VALUES
-    ('waterway.stretches'),
+    ('waterway.stretches_geoserver'),
     ('waterway.fairway_dimensions'),
     ('waterway.distance_marks_geoserver'),
     ('waterway.sounding_results_contour_lines_geoserver'),
--- a/schema/gemma.sql	Mon Jan 21 11:37:24 2019 +0100
+++ b/schema/gemma.sql	Mon Jan 21 11:44:31 2019 +0100
@@ -370,6 +370,24 @@
         UNIQUE(stretches_id, country_code)
     )
 
+    -- Published view for GeoServer
+    CREATE VIEW stretches_geoserver AS SELECT
+        id,
+        name,
+        (stretch).lower::varchar as lower,
+        (stretch).upper::varchar as upper,
+        geom::Geometry(POLYGON, 4326),
+        objnam,
+        nobjnam,
+        date_info,
+        source_organization,
+        (SELECT string_agg(country_code, ', ')
+            FROM stretch_countries
+            WHERE stretches_id = id) AS countries,
+        staging_done
+    FROM stretches
+
+
     CREATE TRIGGER sections_stretches_date_info
         BEFORE UPDATE ON stretches
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()