GeoServer returns "schema does not exists" when creating SQLView Layer using REST API

Question

I'm using GeoServer 2.16.2 (Kartoza/geoserver + postgis docker image) and I'm trying to create a SQLView layer using the REST interface. First I manually created the SQLView layer and copied the layer json description from the GeoServer REST interface. I then deleted the layer and tried to recreate it using a POST call to the GeoServer REST API. But this failed with the error "Schema 'chuva_munic_semana' does not exist."

The only 'complex' thing that I'm trying yo do in my SQLView is that my SQL statement contains a JOIN between two tables in PostGIS and uses a time dimension.

The funny thing is that doing a similar procedure but with a very simple SQL works. This makes me think that the problem lies with the SQL query.

Bellow is the JSON I'm sending to the REST API. GEO_HOST and GEO_PORT are parameters that are substituted prior to the POST call.

{
  "featureType": {
    "name": "chuva_munic_semana",
    "nativeName": "chuva_munic_semana",
    "namespace": {
      "name": "zarc",
      "href": "http://GEO_HOST:GEO_PORT/geoserver/rest/namespaces/zarc.json"
    },
    "title": "Chuva semanal",
    "abstract": "Chuva nos municipios",
    "keywords": {
      "string": [
        "features",
        "chuva_munic_semana"
      ]
    },
    "nativeCRS": "GEOGCS[\"WGS 84\", \n  DATUM[\"World Geodetic System 1984\", \n    SPHEROID[\"WGS 84\", 6378137.0, 298.257223563, AUTHORITY[\"EPSG\",\"7030\"]], \n    AUTHORITY[\"EPSG\",\"6326\"]], \n  PRIMEM[\"Greenwich\", 0.0, AUTHORITY[\"EPSG\",\"8901\"]], \n  UNIT[\"degree\", 0.017453292519943295], \n  AXIS[\"Geodetic longitude\", EAST], \n  AXIS[\"Geodetic latitude\", NORTH], \n  AUTHORITY[\"EPSG\",\"4326\"]]",
    "srs": "EPSG:4326",
    "nativeBoundingBox": {
      "minx": -73.9831821599999,
      "maxx": -28.8477703530605,
      "miny": -33.7511779939999,
      "maxy": 5.26958083300002,
      "crs": "EPSG:4326"
    },
    "latLonBoundingBox": {
      "minx": -73.9831821599999,
      "maxx": -28.8477703530605,
      "miny": -33.7511779939999,
      "maxy": 5.26958083300002,
      "crs": "EPSG:4326"
    },
    "projectionPolicy": "FORCE_DECLARED",
    "enabled": true,
    "metadata": {
      "entry": [
        {
          "@key": "elevation",
          "dimensionInfo": {
            "enabled": false
          }
        },
        {
          "@key": "JDBC_VIRTUAL_TABLE",
          "virtualTable": {
            "name": "chuva_munic_semana",
            "sql": "select a.geocodigo as geocodigo, nome, ano, semana, data_ini, chuva, dias, wkb_geometry from dado_semana_munic as a left join lm_municipio_a as b on a.geocodigo = b.geocodigo\n",
            "escapeSql": false,
            "geometry": {
              "name": "wkb_geometry",
              "type": "Geometry",
              "srid": 4326
            }
          }
        },
        {
          "@key": "time",
          "dimensionInfo": {
            "enabled": true,
            "attribute": "data_ini",
            "presentation": "LIST",
            "units": "ISO8601",
            "defaultValue": {
              "strategy": "MAXIMUM"
            },
            "nearestMatchEnabled": true,
            "acceptableInterval": "P1W/P0D"
          }
        }
      ]
    },
    "store": {
      "@class": "dataStore",
      "name": "zarc:gis",
      "href": "http://GEO_HOST:GEO_PORT/geoserver/rest/workspaces/zarc/datastores/gis.json"
    },
    "serviceConfiguration": false,
    "maxFeatures": 0,
    "numDecimals": 0,
    "padWithZeros": false,
    "forcedDecimal": false,
    "overridingServiceSRS": false,
    "skipNumberMatched": false,
    "circularArcPresent": false,
    "attributes": {
      "attribute": [
        {
          "name": "geocodigo",
          "minOccurs": 1,
          "maxOccurs": 1,
          "nillable": false,
          "binding": "java.lang.String"
        },
        {
          "name": "nome",
          "minOccurs": 0,
          "maxOccurs": 1,
          "nillable": true,
          "binding": "java.lang.String"
        },
        {
          "name": "ano",
          "minOccurs": 1,
          "maxOccurs": 1,
          "nillable": false,
          "binding": "java.lang.Integer"
        },
        {
          "name": "semana",
          "minOccurs": 1,
          "maxOccurs": 1,
          "nillable": false,
          "binding": "java.lang.Integer"
        },
        {
          "name": "data_ini",
          "minOccurs": 0,
          "maxOccurs": 1,
          "nillable": true,
          "binding": "java.sql.Date"
        },
        {
          "name": "chuva",
          "minOccurs": 0,
          "maxOccurs": 1,
          "nillable": true,
          "binding": "java.lang.Integer"
        },
        {
          "name": "dias",
          "minOccurs": 0,
          "maxOccurs": 1,
          "nillable": true,
          "binding": "java.lang.Integer"
        },
        {
          "name": "wkb_geometry",
          "minOccurs": 0,
          "maxOccurs": 1,
          "nillable": true,
          "binding": "org.locationtech.jts.geom.Geometry"
        }
      ]
    }
  }
}

Bellow is the (cliped) GeoServer log messages (logging level up to GEOTOOLS-DEV). I tryed to create the SQLView at 2020-06-10 13:50:33,211. The full log can be found at https://pastebin.com/y7NdLUur

2020-06-10 13:47:44,518 INFO [geoserver.logging] - Suppressing StdOut logging.  If you want to see GeoServer logs, be sure to look in '/opt/geoserver/data_dir/logs/geoserver.log'
2020-06-10 13:47:44,596 WARN [geoserver.security] - Failed login, user admin from 172.21.0.1
2020-06-10 13:47:44,597 INFO [geoserver.security] - Brute force attack prevention, delaying login for 3594ms
2020-06-10 13:48:01,669 INFO [rest.catalog] - Added workspace zarc
2020-06-10 13:48:01,670 INFO [rest.catalog] - POST workspace zarc
2020-06-10 13:48:05,956 INFO [rest.catalog] - POST data store gis
2020-06-10 13:48:08,900 WARN [geotools.xsd] - Sax parser property 'http://www.oracle.com/xml/jaxp/properties/entityExpansionLimit' not recognized.  Xerces version is incompatible.
2020-06-10 13:48:09,564 INFO [rest.catalog] - POST Style chuva_semanal
2020-06-10 13:48:10,302 WARN [geotools.xsd] - Sax parser property 'http://www.oracle.com/xml/jaxp/properties/entityExpansionLimit' not recognized.  Xerces version is incompatible.
2020-06-10 13:48:10,329 INFO [rest.catalog] - POST Style dias_sem_chuva_semanal
2020-06-10 13:48:53,633 WARN [catalog.impl] - Failed to load actual store for FeatureTypeInfoImpl[bc250_munic]
2020-06-10 13:48:54,762 INFO [rest.catalog] - POST feature typegis,bc250_munic
2020-06-10 13:48:54,799 WARN [catalog.impl] - Failed to load actual store for FeatureTypeInfoImpl[bc250_uf]
2020-06-10 13:48:54,878 INFO [rest.catalog] - POST feature typegis,bc250_uf
2020-06-10 13:49:12,356 WARN [geoserver.security] - Failed login, user admin from 172.21.0.1
2020-06-10 13:49:12,356 INFO [geoserver.security] - Brute force attack prevention, delaying login for 2000ms
2020-06-10 13:50:33,211 WARN [rest.catalog] - Unable to fill in metadata from underlying feature source
java.io.IOException: Schema 'chuva_munic_semana' does not exist.
    at org.geotools.data.store.ContentDataStore.ensureEntry(ContentDataStore.java:533)
    at org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:331)
    at org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:592)
    at org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:97)
    at org.geoserver.rest.catalog.FeatureTypeController.featureTypePost(FeatureTypeController.java:246)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:207)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:800)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1038)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:998)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:901)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:875)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
    at 
<content clipped - too long>

org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:69)
    at org.geoserver.flow.controller.IpBlacklistFilter.doFilter(IpBlacklistFilter.java:89)
    at org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:66)
    at org.geoserver.flow.ControlFlowCallback.doFilter(ControlFlowCallback.java:260)
    at org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:66)
    at org.geoserver.wms.animate.AnimatorFilter.doFilter(AnimatorFilter.java:73)
    at org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:66)
    at org.geoserver.filters.SpringDelegatingFilter.doFilter(SpringDelegatingFilter.java:41)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.geoserver.platform.AdvancedDispatchFilter.doFilter(AdvancedDispatchFilter.java:37)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:70)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:74)
    at org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:91)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:70)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:74)
    at org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:91)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.geoserver.security.filter.GeoServerAnonymousAuthenticationFilter.doFilter(GeoServerAnonymousAuthenticationFilter.java:51)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:70)
    at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:215)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:74)
    at org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:91)
    at org.geoserver.security.filter.GeoServerBasicAuthenticationFilter.doFilter(GeoServerBasicAuthenticationFilter.java:81)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:70)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
    at org.geoserver.security.filter.GeoServerSecurityContextPersistenceFilter$1.doFilter(GeoServerSecurityContextPersistenceFilter.java:52)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:74)
    at org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:91)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178)
    at org.geoserver.security.GeoServerSecurityFilterChainProxy.doFilter(GeoServerSecurityFilterChainProxy.java:142)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:357)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:270)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at 

< content clipped - too long >

java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.base/java.lang.Thread.run(Thread.java:834)
2020-06-10 13:50:33,231 WARN [geoserver.catalog] - Error while getting feature type, flushing cache and retrying: Schema 'chuva_munic_semana' does not exist.
2020-06-10 13:50:33,235 WARN [geoserver.catalog] - Unable to flush 'http://zarc:chuva_munic_semana
java.io.IOException: Schema 'chuva_munic_semana' does not exist.
    at org.geotools.data.store.ContentDataStore.ensureEntry(ContentDataStore.java:533)
    at org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:331)
    at org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:303)
    at org.geoserver.catalog.ResourcePool.flushState(ResourcePool.java:2679)
    at org.geoserver.catalog.ResourcePool.flushDataStore(ResourcePool.java:2652)
    at org.geoserver.catalog.ResourcePool.getFeatureType(ResourcePool.java:935)
    at org.geoserver.catalog.ResourcePool.getFeatureType(ResourcePool.java:921)
    at org.geoserver.catalog.impl.FeatureTypeInfoImpl.getFeatureType(FeatureTypeInfoImpl.java:123)
    at org.geoserver.catalog.CatalogBuilder.getDefaultStyle(CatalogBuilder.java:1567)
    at org.geoserver.catalog.CatalogBuilder.buildLayer(CatalogBuilder.java:1516)
    at org.geoserver.rest.catalog.FeatureTypeController.featureTypePost(FeatureTypeController.java:284)
    at 
< content clipped ...>

    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.base/java.lang.Thread.run(Thread.java:834)
2020-06-10 13:50:33,242 ERROR [geoserver.rest] - Schema 'chuva_munic_semana' does not exist.
java.io.IOException: Schema 'chuva_munic_semana' does not exist.
    at org.geotools.data.store.ContentDataStore.ensureEntry(ContentDataStore.java:533)
    at org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:331)
    at org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:303)

<content clipped -- was too long>

2020-06-10 13:50:33,244 WARN [annotation.ExceptionHandlerExceptionResolver] - Resolved [java.io.IOException: Schema 'chuva_munic_semana' does not exist.]

I also found this error coming from PostGIS docker, not sure if it's related

db_1         | 2020-06-10 13:48:53.664 UTC [348] docker@gis ERROR:  relation "public.gt_pk_metadata" does not exist at character 15
db_1         | 2020-06-10 13:48:53.664 UTC [348] docker@gis STATEMENT:  SELECT * FROM "public".GT_PK_METADATA WHERE 1 = 0

Please look in the GeoServer log files and add any relevant error messages to your question using the edit button. You may need to turn the logging level up to GEOTOOLS-DEV to see exactly what is going on — Ian Turton, Jun 10, 2020 at 7:27
Thanks Ian. I added the log messages. Just noticed that I'm seeing 2020-06-10 13:48:53,633 WARN [catalog.impl] - Failed to load actual store for FeatureTypeInfoImpl[bc250_munic] but those layers are created without any issues. And the SQLView layer that I'm trying to create uses one of those layers — Daniel, Jun 10, 2020 at 14:24

Daniel · Accepted Answer · 2020-07-15 01:29:20Z

So it turned out that the problem was how we choose to send the payload to GeoServer. We are using an R Script to open the JSON payload file (jsonlite package) and post it to the server (httr package). Our post call was:

res = POST(url = url, config = auth, body = fromJSON(payload), encode = "json")

Somehow, the fromJSON(payload) was OK for simple JSON files but, when we tried it whit a more complex payload (SQL View Layer), the content got messed up. A friend speculates that it could be related to boxing/unboxing ([]) of some parts of the file.

Anyway, we changed our POST line and now use

res = POST(url = url, config = auth, body = upload_file(payloadFile), encode = 'json')

And all works fine. So the trick was changing from fromJSON() to upload_file()

Stack Exchange Network

current community

your communities

more stack exchange communities

GeoServer returns "schema does not exists" when creating SQLView Layer using REST API

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged postgis geoserver geoserver-rest-api or ask your own question.

Hot Network Questions

GeoServer returns "schema does not exists" when creating SQLView Layer using REST API

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged postgis geoserver geoserver-rest-api or ask your own question.

Related

Hot Network Questions