Hi. I've been struggling with Oracle datastore for a few days.
My problem is: I've a table with a TIMESTAMP column (of type DATE) and a
SDO_GEOMETRY column. The DATE type in Oracle can also contain
hours-minutes-seconds.
If I want to filter (CQL) using that TIMESTAMP column, geoserver gives me
this:
-----
2008-07-07 12:41:59,810 ERROR [data.jdbc] - Error Performing SQL query:
SELECT "ID", "TIMESTAMP", "TIMESTAMP", "GEOM" FROM "GPSLOG" WHERE ((1 = 1
AND ("ID" = 2392299 AND ("TIMESTAMP" > '2008-07-06' AND "TIMESTAMP" <
'2008-07-07'))) AND
SDO_RELATE("GEOM",MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRA
Y(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-72.23157287597657,-34.42786743164063,-
70.68112731933594,-33.39240600585938)),'mask=anyinteract querytype=WINDOW')
= 'TRUE' )
java.sql.SQLException: ORA-01861: el literal no coincide con la cadena de
formato
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
at
oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:790)
at
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java
:1038)
at
oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:830)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java
:1133)
at
oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1273)
at
org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement
.java:208)
at
org.geotools.data.jdbc.JDBC1DataStore.executeQuery(JDBC1DataStore.java:905)
at
org.geotools.data.jdbc.JDBC1DataStore.getFeatureReader(JDBC1DataStore.java:6
27)
at
org.geotools.data.jdbc.JDBCFeatureCollection.reader(JDBCFeatureCollection.ja
va:83)
at
org.geotools.data.store.DataFeatureCollection.openIterator(DataFeatureCollec
tion.java:241)
at
org.geotools.data.store.DataFeatureCollection.iterator(DataFeatureCollection
.java:211)
at
org.geotools.renderer.lite.StreamingRenderer.processStylers(StreamingRendere
r.java:1585)
at
org.geotools.renderer.lite.StreamingRenderer.paint(StreamingRenderer.java:64
1)
at
org.geotools.renderer.shape.ShapefileRenderer.renderWithStreamingRenderer(Sh
apefileRenderer.java:1461)
at
org.geotools.renderer.shape.ShapefileRenderer.paint(ShapefileRenderer.java:1
331)
at
org.geotools.renderer.shape.ShapefileRenderer.paint(ShapefileRenderer.java:2
62)
at
org.vfny.geoserver.wms.responses.DefaultRasterMapProducer.produceMap(Default
RasterMapProducer.java:315)
at
org.vfny.geoserver.wms.responses.GetMapResponse.execute(GetMapResponse.java:
424)
at
org.geoserver.ows.adapters.ResponseAdapter.getMimeType(ResponseAdapter.java:
48)
at org.geoserver.ows.Dispatcher.response(Dispatcher.java:640)
at
org.geoserver.ows.Dispatcher.handleRequestInternal(Dispatcher.java:192)
at
org.springframework.web.servlet.mvc.AbstractController.handleRequest(Abstrac
tController.java:139)
at
org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(Si
mpleControllerHandlerAdapter.java:44)
at
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServl
et.java:684)
at
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServle
t.java:625)
at
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkSer
vlet.java:392)
at
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java
:347)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
FilterChain.java:269)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
ain.java:188)
at
org.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetCharacterE
ncodingFilter.java:108)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
FilterChain.java:215)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
ain.java:188)
at
org.geoserver.filters.ReverseProxyFilter.doFilter(ReverseProxyFilter.java:17
0)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
FilterChain.java:215)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
ain.java:188)
at
org.geoserver.filters.LoggingFilter.doFilter(LoggingFilter.java:73)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
FilterChain.java:215)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
ain.java:188)
at org.geoserver.filters.GZIPFilter.doFilter(GZIPFilter.java:41)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
FilterChain.java:215)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
ain.java:188)
at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterCh
ainProxy.java:264)
at
org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecur
ityInterceptor.java:107)
at
org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSec
urityInterceptor.java:72)
at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterCh
ainProxy.java:274)
at
org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslatio
nFilter.java:110)
at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterCh
ainProxy.java:274)
at
org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(Ano
nymousProcessingFilter.java:125)
at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterCh
ainProxy.java:274)
at
org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicProcessin
gFilter.java:178)
at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterCh
ainProxy.java:274)
at
org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpS
essionContextIntegrationFilter.java:229)
at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterCh
ainProxy.java:274)
at
org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148)
at
org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
FilterChain.java:215)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
ain.java:188)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.ja
va:213)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.ja
va:174)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127
)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117
)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java
:108)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:874)
at
org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processC
onnection(Http11BaseProtocol.java:665)
at
org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.jav
a:528)
at
org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWo
rkerThread.java:81)
at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.jav
a:689)
at java.lang.Thread.run(Thread.java:619)
2008-07-07 12:41:59,828 ERROR [geotools.rendering] - Could not aquire
feature:org.geotools.data.DataSourceException: Error Performing SQL query:
SELECT "ID", "TIMESTAMP", "TIMESTAMP", "GEOM" FROM "GPSLOG" WHERE ((1 = 1
AND ("ID" = 2392299 AND ("TIMESTAMP" > '2008-07-06' AND "TIMESTAMP" <
'2008-07-07'))) AND
SDO_RELATE("GEOM",MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRA
Y(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-72.23157287597657,-34.42786743164063,-
70.68112731933594,-33.39240600585938)),'mask=anyinteract querytype=WINDOW')
= 'TRUE' )
java.util.NoSuchElementException: Could not aquire
feature:org.geotools.data.DataSourceException: Error Performing SQL query:
SELECT "ID", "TIMESTAMP", "TIMESTAMP", "GEOM" FROM "GPSLOG" WHERE ((1 = 1
AND ("ID" = 2392299 AND ("TIMESTAMP" > '2008-07-06' AND "TIMESTAMP" <
'2008-07-07'))) AND
SDO_RELATE("GEOM",MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRA
Y(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-72.23157287597657,-34.42786743164063,-
70.68112731933594,-33.39240600585938)),'mask=anyinteract querytype=WINDOW')
= 'TRUE' )
at
org.geotools.data.store.NoContentIterator.next(NoContentIterator.java:53)
at
org.geotools.renderer.lite.StreamingRenderer.processStylers(StreamingRendere
r.java:1599)
at
org.geotools.renderer.lite.StreamingRenderer.paint(StreamingRenderer.java:64
1)
at
org.geotools.renderer.shape.ShapefileRenderer.renderWithStreamingRenderer(Sh
apefileRenderer.java:1461)
at
org.geotools.renderer.shape.ShapefileRenderer.paint(ShapefileRenderer.java:1
331)
at
org.geotools.renderer.shape.ShapefileRenderer.paint(ShapefileRenderer.java:2
62)
at
org.vfny.geoserver.wms.responses.DefaultRasterMapProducer.produceMap(Default
RasterMapProducer.java:315)
at
org.vfny.geoserver.wms.responses.GetMapResponse.execute(GetMapResponse.java:
424)
at
org.geoserver.ows.adapters.ResponseAdapter.getMimeType(ResponseAdapter.java:
48)
at org.geoserver.ows.Dispatcher.response(Dispatcher.java:640)
at
org.geoserver.ows.Dispatcher.handleRequestInternal(Dispatcher.java:192)
at
org.springframework.web.servlet.mvc.AbstractController.handleRequest(Abstrac
tController.java:139)
at
org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(Si
mpleControllerHandlerAdapter.java:44)
at
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServl
et.java:684)
at
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServle
t.java:625)
at
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkSer
vlet.java:392)
at
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java
:347)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
FilterChain.java:269)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
ain.java:188)
at
org.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetCharacterE
ncodingFilter.java:108)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
FilterChain.java:215)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
ain.java:188)
at
org.geoserver.filters.ReverseProxyFilter.doFilter(ReverseProxyFilter.java:17
0)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
FilterChain.java:215)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
ain.java:188)
at
org.geoserver.filters.LoggingFilter.doFilter(LoggingFilter.java:73)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
FilterChain.java:215)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
ain.java:188)
at org.geoserver.filters.GZIPFilter.doFilter(GZIPFilter.java:41)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
FilterChain.java:215)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
ain.java:188)
at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterCh
ainProxy.java:264)
at
org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecur
ityInterceptor.java:107)
at
org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSec
urityInterceptor.java:72)
at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterCh
ainProxy.java:274)
at
org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslatio
nFilter.java:110)
at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterCh
ainProxy.java:274)
at
org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(Ano
nymousProcessingFilter.java:125)
at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterCh
ainProxy.java:274)
at
org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicProcessin
gFilter.java:178)
at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterCh
ainProxy.java:274)
at
org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpS
essionContextIntegrationFilter.java:229)
at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterCh
ainProxy.java:274)
at
org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148)
at
org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
FilterChain.java:215)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
ain.java:188)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.ja
va:213)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.ja
va:174)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127
)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117
)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java
:108)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:874)
at
org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processC
onnection(Http11BaseProtocol.java:665)
at
org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.jav
a:528)
at
org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWo
rkerThread.java:81)
at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.jav
a:689)
at java.lang.Thread.run(Thread.java:619)
-----
It seems that Geoserver (Geotools?) can't filter against DATE
columns. When I first declared the feature on Geoserver, it gives a type of
"(xml fragment)" for the DATE column, and:
<!-- definition for class java.sql.Date -->
As the fragment. I changed that to dateTime and it doesn't work. This works
properly on PostGIS, using the same table structure.
Any hints/ideas?
Thanks,
Ivan Toledo
MovilData Chile