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
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