[Geoserver-devel] SQLView and Oracle SDO Function in SQL

Senthil · July 14, 2010, 8:57am

Hi Andrea

I had few issues with complex feature and now a days exploring the feasibility of SQLView to satisfy the requirement. SQLView works well with one or more tables with normal SQL statements.

Today i was try to use SQL statement with SDO_AGGR_UNION to combine all the polygons which are related and when i did refresh link to load fields in configuration page, i got the exception given below. I’m going to have look the code for this issue and possible fixes for that. it looks issue with SQL conversion. However your advice/guidelines on this issue is much appreciated.

Thanks

regards
Senthil

SQL Query used in SQLView:
SELECT b.group_Id, SDO_AGGR_UNION(SDOAGGRTYPE(a.LOCATION, 0.005)) FROM GIS.PART_TABLE a, GIS.MAP_TABLE b WHERE a.PART_ID=b.PART_ID Group by b.group_Id

14 Jul 16:53:45 DEBUG [org.geotools.jdbc] - SELECT * FROM (SELECT SDO_AGGR_UNION(SDOAGGRTYPE(A.L as SDO_AGGR_UNION(SDOAGGRTYPE(A.L FROM (SELECT b.group_Id, SDO_AGGR_UNION(SDOAGGRTY
PE(a.LOCATION, 0.005)) FROM GIS.PART_TABLE a, GIS.MAP_TABLE b WHERE a.PART_ID=b.PART_ID Group by b.group_Id) VTABLE) WHERE ROWNUM <= 1
14 Jul 16:53:45 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
14 Jul 16:53:45 ERROR [org.apache.wicket.RequestCycle] - java.io.IOException
java.lang.RuntimeException: java.io.IOException
at org.geotools.data.store.ContentFeatureCollection.features(ContentFeatureCollection.java:212)
at org.geoserver.web.data.layer.SQLViewAbstractPage.guessFeatureType(SQLViewAbstractPage.java:373)
at org.geoserver.web.data.layer.SQLViewAbstractPage.testViewDefinition(SQLViewAbstractPage.java:306)
at org.geoserver.web.data.layer.SQLViewAbstractPage$8.onClick(SQLViewAbstractPage.java:269)
at org.geoserver.web.wicket.GeoServerAjaxFormLink.onSubmit(GeoServerAjaxFormLink.java:39)
at org.apache.wicket.ajax.markup.html.form.AjaxSubmitLink$1.onSubmit(AjaxSubmitLink.java:94)
at org.apache.wicket.ajax.form.AjaxFormSubmitBehavior.onEvent(AjaxFormSubmitBehavior.java:128)
at org.apache.wicket.ajax.AjaxEventBehavior.respond(AjaxEventBehavior.java:163)
at org.apache.wicket.ajax.AbstractDefaultAjaxBehavior.onRequest(AbstractDefaultAjaxBehavior.java:297)
at org.apache.wicket.request.target.component.listener.BehaviorRequestTarget.processEvents(BehaviorRequestTarget.java:104)
at org.apache.wicket.request.AbstractRequestCycleProcessor.processEvents(AbstractRequestCycleProcessor.java:91)
at org.apache.wicket.RequestCycle.processEventsAndRespond(RequestCycle.java:1239)
at org.apache.wicket.RequestCycle.step(RequestCycle.java:1316)
at org.apache.wicket.RequestCycle.steps(RequestCycle.java:1418)
at org.apache.wicket.RequestCycle.request(RequestCycle.java:532)
at org.apache.wicket.protocol.http.WicketFilter.doGet(WicketFilter.java:356)
at org.apache.wicket.protocol.http.WicketServlet.doPost(WicketServlet.java:145)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.springframework.web.servlet.mvc.ServletWrappingController.handleRequestInternal(ServletWrappingController.java:158)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:809)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:511)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.geoserver.filters.ThreadLocalsCleanupFilter.doFilter(ThreadLocalsCleanupFilter.java:23)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.geoserver.platform.AdvancedDispatchFilter.doFilter(AdvancedDispatchFilter.java:48)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:109)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:265)
at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107)
at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:124)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.ui.rememberme.RememberMeProcessingFilter.doFilter(RememberMeProcessingFilter.java:142)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.wrapper.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:81)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:271)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.ui.logout.LogoutFilter.doFilter(LogoutFilter.java:110)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:249)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:149)
at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.geoserver.filters.LoggingFilter.doFilter(LoggingFilter.java:71)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.geoserver.filters.ReverseProxyFilter.doFilter(ReverseProxyFilter.java:183)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.geoserver.filters.GZIPFilter.doFilter(GZIPFilter.java:41)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:859)
at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579)
at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1555)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.io.IOException
at org.geotools.jdbc.JDBCFeatureSource.getReaderInternal(JDBCFeatureSource.java:558)
at org.geotools.data.store.ContentFeatureSource.getReader(ContentFeatureSource.java:483)
at org.geotools.data.store.ContentFeatureCollection.features(ContentFeatureCollection.java:209)
… 79 more
Caused by: java.sql.SQLException: ORA-00907: missing right parenthesis

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.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1037)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3361)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.geotools.jdbc.JDBCFeatureReader.(JDBCFeatureReader.java:158)
at org.geotools.jdbc.JDBCFeatureSource.getReaderInternal(JDBCFeatureSource.java:546)
… 81 more
14 Jul 16:53:45 WARN [org.geotools.jdbc] - There is code leaving feature readers/iterators open, this is leaking statements and connections!

Andrea_Aime3 · July 14, 2010, 9:13am

Senthil ha scritto:

Hi Andrea

I had few issues with complex feature and now a days exploring the feasibility of SQLView to satisfy the requirement. SQLView works well with one or more tables with normal SQL statements.

Today i was try to use SQL statement with SDO_AGGR_UNION to combine all the polygons which are related and when i did refresh link to load fields in configuration page, i got the exception given below. I'm going to have look the code for this issue and possible fixes for that. it looks issue with SQL conversion. However your advice/guidelines on this issue is much appreciated.

I haven't tested anything beyond plain sql queries.
In any case the sql view works without understanding the query you typed
(no need to write a different parser for each possible database and
sql sytanx variations) by using the query you provided as a subquery
in the from clause:

select <list of atts coming from the wms/wfs requests>
from (<your sql view definition) as vtable
where <filter conditions coming from the wms/wfs request>

I don't know why it's not working in your particular case, but I'm sure
interested in a patch to handle your case as well.

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

Senthil · July 15, 2010, 8:13am

Hi Andrea,

I was able to find a fix/workaround with SQL than do any code changes with comparing generated SQL.

Previous SQL:
SELECT b.group_Id, SDO_AGGR_UNION(SDOAGGRTYPE(a.LOCATION, 0.005)) FROM GIS.PART_TABLE a, GIS.MAP_TABLE b WHERE a.PART_ID=b.PART_ID Group by b.group_Id

Changed as :
SELECT b.group_Id as GRP_ID, SDO_AGGR_UNION(SDOAGGRTYPE(a.LOCATION, 0.005)) as GRP_LOCATION FROM GIS.PART_TABLE a, GIS.MAP_TABLE b WHERE a.PART_ID=b.PART_ID Group by b.group_Id

Then It was converted SQL as below and able to create SQLView for that:

SELECT * FROM (SELECT GRP_ID as GRP_ID FROM (SELECT b.group_Id as GRP_ID, SDO_AGGR_UNION(SDOAGGRTYPE(a.LOCATION, 0.005)) as GRP_LOCATION FROM GIS.PART_TABLE a, GIS.MAP_TABLE b WHERE a.PART_ID=b.PART_ID Group by b.group_Id) VTABLE) WHERE ROWNUM <= 1

It may be good to update SQLView document page regarding this. In meantime, it came across another issue with Oracle SDO_FILTER and i will create another mail thread to discuss in developer mailing list.

Regards
Senthil

On Wed, Jul 14, 2010 at 9:13 PM, Andrea Aime <aaime@anonymised.com> wrote:

Senthil ha scritto:

Hi Andrea

I had few issues with complex feature and now a days exploring the feasibility of SQLView to satisfy the requirement. SQLView works well with one or more tables with normal SQL statements.

Today i was try to use SQL statement with SDO_AGGR_UNION to combine all the polygons which are related and when i did refresh link to load fields in configuration page, i got the exception given below. I’m going to have look the code for this issue and possible fixes for that. it looks issue with SQL conversion. However your advice/guidelines on this issue is much appreciated.

I haven’t tested anything beyond plain sql queries.
In any case the sql view works without understanding the query you typed
(no need to write a different parser for each possible database and
sql sytanx variations) by using the query you provided as a subquery
in the from clause:

select <list of atts coming from the wms/wfs requests>
from (<your sql view definition) as vtable
where <filter conditions coming from the wms/wfs request>

I don’t know why it’s not working in your particular case, but I’m sure
interested in a patch to handle your case as well.

Cheers
Andrea

–
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

Andrea_Aime3 · July 15, 2010, 8:34am

Senthil ha scritto:

Hi Andrea,

I was able to find a fix/workaround with SQL than do any code changes with comparing generated SQL.

Previous SQL: *SELECT b.group_Id, SDO_AGGR_UNION(SDOAGGRTYPE(a.LOCATION, 0.005)) FROM GIS.PART_TABLE a, GIS.MAP_TABLE b WHERE a.PART_ID=b.PART_ID Group by b.group_Id*
*
Changed as* :*
*SELECT b.group_Id* as GRP_ID, *SDO_AGGR_UNION(SDOAGGRTYPE(a.LOCATION, 0.005))* as GRP_LOCATION *FROM GIS.PART_TABLE a, GIS.MAP_TABLE b WHERE a.PART_ID=b.PART_ID Group by b.group_Id*
Then It was converted SQL as below and able to create SQLView for that:
SELECT * FROM (SELECT GRP_ID as GRP_ID FROM (SELECT b.group_Id as GRP_ID, SDO_AGGR_UNION(SDOAGGRTYPE(a.LOCATION, 0.005)) as GRP_LOCATION FROM GIS.PART_TABLE a, GIS.MAP_TABLE b WHERE a.PART_ID=b.PART_ID Group by b.group_Id) VTABLE) WHERE ROWNUM <= 1

It may be good to update SQLView document page regarding this.

It's first necessary to understand what fixed the problem.
Is it just adding the aliases? I guess it makes sense, the feature
creation machinery requires attribute names and Oracle was not providing
any name for the aggreate union one?
Does it work if you don't alias the first attribute, b.groupId?

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

Senthil · July 15, 2010, 8:45am

Hi Andrea

Yes. Alias solved the issue… First alias used for (b.group_Id) not necessary to change… Only for any SDO function or even PL/SQL function with parameter need to use alias to avoid that problem. There is no option available for machine generated SQL to find suitable name, i agreed with you… I donot think any code fixes necessary for this.

regards
Senthil

On Thu, Jul 15, 2010 at 8:34 PM, Andrea Aime <aaime@anonymised.com> wrote:

Senthil ha scritto:

Hi Andrea,

I was able to find a fix/workaround with SQL than do any code changes with comparing generated SQL.

Previous SQL: SELECT b.group_Id, SDO_AGGR_UNION(SDOAGGRTYPE(a.LOCATION, 0.005)) FROM GIS.PART_TABLE a, GIS.MAP_TABLE b WHERE a.PART_ID=b.PART_ID Group by b.group_Id
*
*
Changed as* :*
SELECT b.group_Id as GRP_ID, SDO_AGGR_UNION(SDOAGGRTYPE(a.LOCATION, 0.005)) as GRP_LOCATION FROM GIS.PART_TABLE a, GIS.MAP_TABLE b WHERE a.PART_ID=b.PART_ID Group by b.group_Id
Then It was converted SQL as below and able to create SQLView for that:
SELECT * FROM (SELECT GRP_ID as GRP_ID FROM (SELECT b.group_Id as GRP_ID, SDO_AGGR_UNION(SDOAGGRTYPE(a.LOCATION, 0.005)) as GRP_LOCATION FROM GIS.PART_TABLE a, GIS.MAP_TABLE b WHERE a.PART_ID=b.PART_ID Group by b.group_Id) VTABLE) WHERE ROWNUM <= 1

It may be good to update SQLView document page regarding this.

It’s first necessary to understand what fixed the problem.
Is it just adding the aliases? I guess it makes sense, the feature
creation machinery requires attribute names and Oracle was not providing
any name for the aggreate union one?
Does it work if you don’t alias the first attribute, b.groupId?

Cheers
Andrea

–
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.