[Geoserver-users] SQL data display through Geoserver not visable in openlayers

GeoServer GeoServer User
1

I am at my wits end with this one as I know it will be something small.

I have connected up to a sql database and managed to add a layer by creating a new sql view.

I can output the view using the gml preview but kml fails with a java.lang.NullPointerException

null:

gml:featureMember<TrackYou:Current_Gritting_Activity fid=“Current_Gritting_Activity.5”>TrackYou:registrationGL66</TrackYou:registration>TrackYou:activityControl Box On</TrackYou:activity>TrackYou:datetime2013-10-28T08:24:00</TrackYou:datetime>TrackYou:latitude53.1601166670</TrackYou:latitude>TrackYou:longitude-1.5915388890</TrackYou:longitude>TrackYou:geom<gml:Point srsName=“http://www.opengis.net/gml/srs/epsg.xml#4326”><gml:coordinates decimal=“.” cs=“,” ts=" ">-1.59153889,53.16011667</gml:coordinates></gml:Point></TrackYou:geom></TrackYou:Current_Gritting_Activity></gml:featureMember>

I can download a shapefile and the points work but when looking at the Open Layers preview or using Leaflet the tile images do load but they are blank are blank.

Any ideas?

Cheers

David

Think before you print! Save energy and paper. Do you really need to print this email?

Derbyshire County Council works to improve the lives of local people by delivering high quality services. You can find out more about us by visiting ‘www.derbyshire.gov.uk’. If you want to work for us go to our job pages on ‘www.derbyshire.gov.uk/jobs’. You can register for e-mail alerts, download job packs and apply on-line.

Please Note
This email is confidential, may be legally privileged and may contain personal views that are not the views of Derbyshire County Council. It is intended solely for the addressee. If this email was sent to you in error please notify us by replying to the email. Once you have done this please delete the email and do not disclose, copy, distribute, or rely on it.
Under the Data Protection Act 1998 and the Freedom of Information Act 2000 the contents of this email may be disclosed.

Derbyshire County Council reserves the right to monitor both sent and received emails.

2

On Wed, Oct 30, 2013 at 10:03 PM, Alvey,David (Chief Executives) <
David.Alvey@anonymised.com> wrote:

I am at my wits end with this one as I know it will be something small.

I have connected up to a sql database and managed to add a layer by
creating a new sql view.

I can output the view using the gml preview but kml fails with a
java.lang.NullPointerException

null:

<gml:featureMember><TrackYou:Current_Gritting_Activity
fid="Current_Gritting_Activity.5"><TrackYou:registration>GL66</TrackYou:registration><TrackYou:activity>Control
Box
On</TrackYou:activity><TrackYou:datetime>2013-10-28T08:24:00</TrackYou:datetime><TrackYou:latitude>53.1601166670</TrackYou:latitude><TrackYou:longitude>-1.5915388890</TrackYou:longitude><TrackYou:geom><gml:Point
srsName="http://www.opengis.net/gml/srs/epsg.xml#4326&quot;&gt;&lt;gml:coordinates
decimal="." cs="," ts="
">-1.59153889,53.16011667</gml:coordinates></gml:Point></TrackYou:geom></TrackYou:Current_Gritting_Activity></gml:featureMember>

I can download a shapefile and the points work but when looking at the
Open Layers preview or using Leaflet the tile images do load but they are
blank are blank.

So outputs using the style break, whilst WFS outputs not using any style
work.
It seems there is a relationship. Have you tried looking into the style, or
checking how things work using the point style?
(assuming, of course, that the point style is still the default one)

Cheers
Andrea

--

Our support, Your Success! Visit http://opensdi.geo-solutions.it for more
information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

-------------------------------------------------------

3

On Thu, Oct 31, 2013 at 1:55 PM, Alvey,David (Chief Executives) <
David.Alvey@anonymised.com> wrote:

****

I am actually using Leaflet to display the data in the final application.
****

** **

This is the address of a tile which should have points generated using the
SQL query.****

** **

https://wms2.derbyshire.gov.uk/wms?SERVICE=WMS&REQUEST=GetMap&VERSION=1.1.1&LAYERS=TrackYou%3ACurrent_Gritting_Activity&STYLES=&FORMAT=image%2Fpng&TRANSPARENT=true&HEIGHT=256&WIDTH=256&SRS=EPSG%3A3857&BBOX=-176110.91316904608,6995516.8286593305,-166326.97354854352,7005300.768279833

Yep, I see. There is reprojection going on here, can you change the
projection handling to force declared?
"Keep native" is one setting that is hardly ever used and has no tests in
the GeoServer code base.

****

** **

Previously I have been using java properties files to load in the data but
that is very inefficient as thousands of points will need to be rendered.
This image has been generated using the old method at the same location
using the same data.****

** **

http://wms.derbyshire.gov.uk/geoserver/wms?SERVICE=WMS&REQUEST=GetMap&VERSION=1.1.1&LAYERS=TrackYou%3Aall&STYLES=&FORMAT=image%2Fpng&TRANSPARENT=true&HEIGHT=256&WIDTH=256&SRS=EPSG%3A3857&BBOX=-176110.91316904608,6995516.8286593305,-166326.97354854352,7005300.768279833
****

** **

I just tried clicking the Compute from data for the bounding box and got
the following error:****

** **

java.lang.RuntimeException: java.io.IOException: Error occured calculating
bounds at
org.geotools.jdbc.JDBCFeatureSource.getBoundsInternal(JDBCFeatureSource.java:508)
at
org.geotools.data.store.ContentFeatureSource.getBounds(ContentFeatureSource.java:426)
at
org.geotools.data.store.ContentFeatureSource.getBounds(ContentFeatureSource.java:363)
at
org.vfny.geoserver.global.GeoServerFeatureSource.getBounds(GeoServerFeatureSource.java:591)
at
org.geoserver.catalog.CatalogBuilder.getNativeBounds(CatalogBuilder.java:556)
at
org.geoserver.catalog.CatalogBuilder.getNativeBounds(CatalogBuilder.java:538)
at
org.geoserver.web.data.resource.BasicResourceConfig$1.onSubmit(BasicResourceConfig.java:115)
at
org.apache.wicket.ajax.markup.html.form.AjaxSubmitLink$1.onSubmit(AjaxSubmitLink.java:68)
at
org.apache.wicket.ajax.form.AjaxFormSubmitBehavior.onEvent(AjaxFormSubmitBehavior.java:143)
at
org.apache.wicket.ajax.AjaxEventBehavior.respond(AjaxEventBehavior.java:177)
at
org.apache.wicket.ajax.AbstractDefaultAjaxBehavior.onRequest(AbstractDefaultAjaxBehavior.java:300)
at
org.apache.wicket.request.target.component.listener.BehaviorRequestTarget.processEvents(BehaviorRequestTarget.java:119)
at
org.apache.wicket.request.AbstractRequestCycleProcessor.processEvents(AbstractRequestCycleProcessor.java:92)
at
org.apache.wicket.RequestCycle.processEventsAndRespond(RequestCycle.java:1250)
at org.apache.wicket.RequestCycle.step(RequestCycle.java:1329) at
org.apache.wicket.RequestCycle.steps(RequestCycle.java:1436) at
org.apache.wicket.RequestCycle.request(RequestCycle.java:545) at
org.apache.wicket.protocol.http.WicketFilter.doGet(WicketFilter.java:484)
at
org.apache.wicket.protocol.http.WicketServlet.doPost(WicketServlet.java:160)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727) at
javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at
org.springframework.web.servlet.mvc.ServletWrappingController.handleRequestInternal(ServletWrappingController.java:159)
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:923)
at
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
at
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
at
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727) at
javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at
org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487) at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1093)
at
org.geoserver.filters.ThreadLocalsCleanupFilter.doFilter(ThreadLocalsCleanupFilter.java:27)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at
org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:74)
at org.geoserver.monitor.MonitorFilter.doFilter(MonitorFilter.java:88) at
org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:70)
at
org.geoserver.filters.SpringDelegatingFilter.doFilter(SpringDelegatingFilter.java:45)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at
org.geoserver.platform.AdvancedDispatchFilter.doFilter(AdvancedDispatchFilter.java:49)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at
org.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:109)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:311)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:68)
at
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:116)
at
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:72)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:91)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:68)
at
org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:72)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:91)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.geoserver.security.filter.GeoServerAnonymousAuthenticationFilter.doFilter(GeoServerAnonymousAuthenticationFilter.java:53)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:68)
at
org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:182)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:72)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:91)
at
org.geoserver.security.filter.GeoServerUserNamePasswordAuthenticationFilter.doFilter(GeoServerUserNamePasswordAuthenticationFilter.java:115)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:68)
at
org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:146)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:72)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:91)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:68)
at
org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
at
org.geoserver.security.filter.GeoServerSecurityContextPersistenceFilter$1.doFilter(GeoServerSecurityContextPersistenceFilter.java:46)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:72)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:91)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:173)
at
org.geoserver.security.GeoServerSecurityFilterChainProxy.doFilter(GeoServerSecurityFilterChainProxy.java:103)
at
org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
at
org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at org.geoserver.filters.LoggingFilter.doFilter(LoggingFilter.java:75) at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at org.geoserver.filters.GZIPFilter.doFilter(GZIPFilter.java:42) at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at
org.geoserver.filters.SessionDebugFilter.doFilter(SessionDebugFilter.java:47)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
at
org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726)
at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405) at
org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206)
at
org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at org.mortbay.jetty.Server.handle(Server.java:324) at
org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505) at
org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:842)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:648) at
org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211) at
org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380) at
org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
at
org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:450)
Caused by: java.io.IOException: Error occured calculating bounds at
org.geotools.jdbc.JDBCDataStore.getBounds(JDBCDataStore.java:1168) at
org.geotools.jdbc.JDBCFeatureSource.getBoundsInternal(JDBCFeatureSource.java:501)
... 100 more Caused by: java.io.IOException at
org.geotools.data.sqlserver.SQLServerDialect.decodeGeometry(SQLServerDialect.java:469)
at
org.geotools.data.sqlserver.SQLServerDialect.decodeGeometryEnvelope(SQLServerDialect.java:493)
at org.geotools.jdbc.JDBCDataStore.getBounds(JDBCDataStore.java:1154) ...
101 more Caused by: org.opengis.referencing.NoSuchAuthorityCodeException:
No code "EPSG:0" from authority "European Petroleum Survey Group" found for
object of type "IdentifiedObject". at
org.geotools.referencing.factory.AbstractAuthorityFactory.noSuchAuthorityCode(AbstractAuthorityFactory.java:951)
at
org.geotools.referencing.factory.PropertyAuthorityFactory.getWKT(PropertyAuthorityFactory.java:316)
at
org.geotools.referencing.factory.PropertyAuthorityFactory.createCoordinateReferenceSystem(PropertyAuthorityFactory.java:390)
at
org.geotools.referencing.factory.BufferedAuthorityFactory.createCoordinateReferenceSystem(BufferedAuthorityFactory.java:783)
at
org.geotools.referencing.factory.AuthorityFactoryAdapter.createCoordinateReferenceSystem(AuthorityFactoryAdapter.java:801)
at
org.geotools.referencing.factory.FallbackAuthorityFactory.createCoordinateReferenceSystem(FallbackAuthorityFactory.java:640)
at
org.geotools.referencing.factory.AuthorityFactoryAdapter.createCoordinateReferenceSystem(AuthorityFactoryAdapter.java:801)
at
org.geotools.referencing.factory.ThreadedAuthorityFactory.createCoordinateReferenceSystem(ThreadedAuthorityFactory.java:731)
at
org.geotools.referencing.DefaultAuthorityFactory.createCoordinateReferenceSystem(DefaultAuthorityFactory.java:179)
at org.geotools.referencing.CRS.decode(CRS.java:489) at
org.geotools.referencing.CRS.decode(CRS.java:417) at
org.geotools.data.sqlserver.SQLServerDialect.decodeGeometry(SQLServerDialect.java:466)
... 103 more

Ah, SQLServer. The issue is that you loaded the geometries in EPSG code 0,
you should have assigned one instead.
It's also possible (but haven't checked) that SQLServer is refusing to
return any result because of the mismatch between
the data (in epsg 0) and the spatial filters it's getting (in epsg 4326).

Then again, it's just a guess, might be something else entirely

Cheers
Andrea

--

Our support, Your Success! Visit http://opensdi.geo-solutions.it for more
information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

-------------------------------------------------------