I have a working setup with a complex type of HeartStarter with a list of AvailableTimes thanks to
Rini Angreani and Ben Caradoc-Davies. I can request the data over both WFS och WMS.
As I have alot of features I tried to enable joining to get better performance but when I did it broke.
The Heartstarter type is read from a view that joins the HeartStarter table with another table containing the geometry and some attributes.
AvailableTime are read from a table and all data sits in the same Sql Server database.
I enabled the GeoTools logging and found that the following SQL-query are executed when a GetMap is requested:
SELECT “HjaertstartarIntegrationsVy”.“Geometri”.STAsBinary() as “Geometri” FROM “HjaertstartarIntegrationsVy” INNER JOIN ( SELECT DISTINCT FROM “HjaertstartarIntegrationsVy” WHERE “Geometri”.F
ilter(geometry::STGeomFromText(‘POLYGON ((-1969834.7690915626 5498574.065957437, -1969834.7690915626 13090911.210410563, 5622502.375361563 13090911.210410563, 5622502.375361563 5498574.065957437, -1969834.7690915626 5498574.065957437))’,
900913)) = 1 ) temp_alias_used_for_filter ON ( )
And it fails with the following exception:
java.lang.RuntimeException: java.io.IOException
at org.geotools.data.store.ContentFeatureCollection.features(ContentFeatureCollection.java:241)
at org.geotools.data.store.ContentFeatureCollection.features(ContentFeatureCollection.java:68)
at org.geotools.data.complex.DataAccessMappingFeatureIterator.initialiseSourceFeatures(DataAccessMappingFeatureIterator.java:382)
at org.geotools.data.complex.AbstractMappingFeatureIterator.(AbstractMappingFeatureIterator.java:161)
at org.geotools.data.complex.DataAccessMappingFeatureIterator.(DataAccessMappingFeatureIterator.java:162)
at org.geotools.data.complex.DataAccessMappingFeatureIterator.(DataAccessMappingFeatureIterator.java:137)
at org.geotools.data.complex.MappingFeatureIteratorFactory.getInstance(MappingFeatureIteratorFactory.java:178)
at org.geotools.data.complex.MappingFeatureCollection.iterator(MappingFeatureCollection.java:264)
at org.geotools.renderer.lite.StreamingRenderer.drawPlain(StreamingRenderer.java:2495)
at org.geotools.renderer.lite.StreamingRenderer.processStylers(StreamingRenderer.java:2012)
at org.geotools.renderer.lite.StreamingRenderer.paint(StreamingRenderer.java:813)
at org.geoserver.wms.map.RenderedImageMapOutputFormat.produceMap(RenderedImageMapOutputFormat.java:490)
at org.geoserver.wms.map.RenderedImageMapOutputFormat.produceMap(RenderedImageMapOutputFormat.java:254)
at org.geoserver.wms.map.RenderedImageMapOutputFormat.produceMap(RenderedImageMapOutputFormat.java:126)
at org.geoserver.wms.GetMap.executeInternal(GetMap.java:465)
at org.geoserver.wms.GetMap.run(GetMap.java:201)
at org.geoserver.wms.GetMap.run(GetMap.java:111)
at org.geoserver.wms.DefaultWebMapService.getMap(DefaultWebMapService.java:353)
at sun.reflect.GeneratedMethodAccessor165.invoke(Unknown Source)
…
at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:891)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:690)
at java.lang.Thread.run(Unknown Source)
Caused by: java.io.IOException
at org.geotools.jdbc.JoiningJDBCFeatureSource.getJoiningReaderInternal(JoiningJDBCFeatureSource.java:633)
at org.geotools.jdbc.JoiningJDBCFeatureSource.getReaderInternal(JoiningJDBCFeatureSource.java:641)
at org.geotools.data.store.ContentFeatureSource.getReader(ContentFeatureSource.java:562)
at org.geotools.data.store.ContentFeatureCollection.features(ContentFeatureCollection.java:238)
… 116 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword ‘FROM’.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:616)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at org.geotools.jdbc.JDBCFeatureReader.(JDBCFeatureReader.java:140)
at org.geotools.jdbc.JoiningJDBCFeatureSource.getJoiningReaderInternal(JoiningJDBCFeatureSource.java:627)
… 119 more
15 Jan 17:27:54 DEBUG [org.geotools.rendering] - Style cache hit ratio: NaN , hits 0, requests 0
15 Jan 17:27:54 ERROR [org.geoserver.ows] -
org.geoserver.platform.ServiceException: Rendering process failed.
It seems to be that Geoserver constructs a query that are not syntactically correct.
When requesting a GetFeature with WFS a FeatureCollection is returned but the content of the AvailableTimes are not included in the result (only a empty element heart:AvailableTimes for those heartstarters that have one ore more times associated).
Could this be because I’m using a view instead of a table?
Thank you
Lukas
Lukas Bergliden
Civilingenjör
Decerno AB
Electrum 234, SE-164 40 KISTA
Besöksadress: Kistagången 16, 7 tr
Tel: +46 8 630 75 00
Mobil: +46 70 644 96 61