Mercurial > gemma

changeset 4611:b5aa1eb83bb0 geoserver_sql_views

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add possibility to configure SRS for GeoServer SQL view Automatic detection of spatial reference system for SQL views in GeoServer does not always find the correct SRS.
author Tom Gottfried <tom@intevation.de>
date Fri, 06 Sep 2019 11:58:03 +0200
parents 1b928c0a0894
children a5dccbc5920d
files pkg/geoserver/boot.go pkg/models/intservices.go schema/gemma.sql schema/updates/1112/01.add_view_def.sql
diffstat 4 files changed, 12 insertions(+), 2 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/geoserver/boot.go	Thu Sep 05 17:41:43 2019 +0200
+++ b/pkg/geoserver/boot.go	Fri Sep 06 11:58:03 2019 +0200
@@ -74,6 +74,7 @@
 	XMLName  xml.Name `xml:"featureType"`
 	Name     string   `xml:"name"`
 	Title    string   `xml:"title"`
+	SRS      *string  `xml:"srs,omitempty"`
 	Metadata ftMetadata
 }
 
@@ -328,6 +329,7 @@
 			ft := ftXML{
 				Name:  table,
 				Title: table,
+				SRS:   tables[i].SRS,
 				Metadata: ftMetadata{
 					Entry: ftMetadataEntry{
 						Key: "JDBC_VIRTUAL_TABLE",
--- a/pkg/models/intservices.go	Thu Sep 05 17:41:43 2019 +0200
+++ b/pkg/models/intservices.go	Fri Sep 06 11:58:03 2019 +0200
@@ -31,6 +31,7 @@
 	Schema string  `json:"schema"`
 	Name   string  `json:"name"`
 	SQL    *string `json:"sql"`
+	SRS    *string `json:"srs"`
 	Style  bool    `json:"style"`
 	WMS    bool    `json:"wms"`
 	WFS    bool    `json:"wfs"`
@@ -43,8 +44,11 @@
 
 const (
 	selectServicesSQL = `
-SELECT schema, name, view_def, style IS NOT NULL, as_wms, as_wfs
+SELECT schema, name,
+  view_def, auth_name || ':' || auth_srid,
+  style IS NOT NULL, as_wms, as_wfs
 FROM sys_admin.published_services
+  LEFT JOIN spatial_ref_sys USING (srid)
 WHERE schema = $1
 ORDER by name`
 
@@ -137,7 +141,7 @@
 				var entry IntEntry
 				if err := rows.Scan(
 					&entry.Schema, &entry.Name,
-					&entry.SQL, &entry.Style,
+					&entry.SQL, &entry.SRS, &entry.Style,
 					&entry.WMS, &entry.WFS,
 				); err != nil {
 					return err
--- a/schema/gemma.sql	Thu Sep 05 17:41:43 2019 +0200
+++ b/schema/gemma.sql	Fri Sep 06 11:58:03 2019 +0200
@@ -297,6 +297,9 @@
         PRIMARY KEY (schema, name),
         -- SQL statement used for an SQL view in GeoServer:
         view_def text CHECK (is_valid_from_item(view_def)),
+        -- SRID to be used with SQL view:
+        srid int REFERENCES spatial_ref_sys,
+        -- SLD style document:
         style xml CHECK(style IS DOCUMENT),
         as_wms boolean NOT NULL DEFAULT TRUE,
         as_wfs boolean NOT NULL DEFAULT TRUE,
--- a/schema/updates/1112/01.add_view_def.sql	Thu Sep 05 17:41:43 2019 +0200
+++ b/schema/updates/1112/01.add_view_def.sql	Fri Sep 06 11:58:03 2019 +0200
@@ -23,5 +23,6 @@
     DROP CONSTRAINT published_services_pkey,
     ADD PRIMARY KEY (schema, name),
     ADD view_def text CHECK (is_valid_from_item(view_def)),
+    ADD srid int REFERENCES spatial_ref_sys,
     ADD CHECK (to_regclass(schema || '.' || name) IS NOT NULL
             OR view_def IS NOT NULL);