GeoServer SQLServer plugin cannot calculate BBOX for spatial tables with custom schema

[Pekka Hurskainen]

Dear list,

I think I have stumbled upon a bug in the current SQL Server plugin.
Here is what I did:

1) uploaded a polygon shapefile with Morten Nielsen’s shp2sql with
SRID constraint 21037
2) changed the schema from default “dbo” to a custom schema “LandInfo”
in SQL Server Management Studio (Alter Schema LandInfo TRANSFER
dbo.parcels_geom). I can also see the SRID constraint
([geom].[STSrid]=(21037))
3) Tried to create a layer of it in Geoserver 2.0.0, but both Native
SRS and Declared SRS are empty
4) If I manually enter “EPSG:21037” in Declared SRS and try to
calculate the BBOX (Compute from data), I get the following error
message. This also happens if I dont enter any SRS value.

java.lang.RuntimeException: java.io.IOException: Error occured
calculating bounds at
org.geotools.jdbc.JDBCFeatureSource.getBoundsInternal(JDBCFeatureSource.java:481)
at org.geotools.data.store.ContentFeatureSource.getBounds(ContentFeatureSource.java:366)
at org.geotools.data.store.ContentFeatureSource.getBounds(ContentFeatureSource.java:338)
at org.vfny.geoserver.global.GeoServerFeatureSource.getBounds(GeoServerFeatureSource.java:534)
at org.geoserver.catalog.CatalogBuilder.getNativeBounds(CatalogBuilder.java:456)
at org.geoserver.web.data.resource.BasicResourceConfig$1.onSubmit(BasicResourceConfig.java:123)
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.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
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:73)
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:128)
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:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
at java.lang.Thread.run(Unknown Source) Caused by:
java.io.IOException: Error occured calculating bounds at
org.geotools.jdbc.JDBCDataStore.getBounds(JDBCDataStore.java:1010) at
org.geotools.jdbc.JDBCFeatureSource.getBoundsInternal(JDBCFeatureSource.java:474)
... 74 more Caused by:
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name
'parcels_geom'. at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown
Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown
Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown
Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown
Source) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown
Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown
Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown
Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown
Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown
Source) at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at org.geotools.jdbc.JDBCDataStore.getBounds(JDBCDataStore.java:963)
... 75 more

However, If I follow the exact same steps with the same shapefile BUT
keep the default ”dbo” schema, Geoserver recognizes the Native SRS
immediately and lets me calculate both Native and Lat/Lon BBOX. I can
see the data in the demo page (although for some of my data, I still
get the wrong BBOX and my data is cut away in the East, as I already
reported on the list a couple of weeks ago).

Can anybody replicate this?

-Pekka

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Geoserver-users mailing list
Geoserver-users@...
https://lists.sourceforge.net/lists/listinfo/geoserver-users

[Andrea Aime-4]

Pekka Hurskainen ha scritto:

> Dear list,
>
> I think I have stumbled upon a bug in the current SQL Server plugin.
> Here is what I did:
>
> 1) uploaded a polygon shapefile with Morten Nielsen’s shp2sql with
> SRID constraint 21037
> 2) changed the schema from default “dbo” to a custom schema “LandInfo”
> in SQL Server Management Studio (Alter Schema LandInfo TRANSFER
> dbo.parcels_geom). I can also see the SRID constraint
> ([geom].[STSrid]=(21037))
> 3) Tried to create a layer of it in Geoserver 2.0.0, but both Native
> SRS and Declared SRS are empty
> 4) If I manually enter “EPSG:21037” in Declared SRS and try to
> calculate the BBOX (Compute from data), I get the following error
> message. This also happens if I dont enter any SRS value.

I've never used the sql server datastore, but trying a wild guess, how
did you configure the datastore? Did you specify a target schema among
the connection parameters?
If not, try setting that up

Cheers
Andrea


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

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Geoserver-users mailing list
Geoserver-users@...
https://lists.sourceforge.net/lists/listinfo/geoserver-users

[Pekka Hurskainen]

Doh,

forgot to check the datastore settings. After adding my custom schema
in the connection parameters and adding a new layer, I can see only
the tables that are under that schema, as it should be. If I dont
specify any schema, I can see all tables from all schemas. This is OK.

When creating new layer with my custom schema specified in the
datastore, Native SRS and Declared SRS appear still empty, but it lets
me calculate bbox from data and the lat/lon bbox IF I enter the
EPSG:21037 manually on the Declared SRS. The demo works OK.

Still, if I try to create a layer which is in the default schema
"dbo", I get both Native and Declared SRS automatically, regardless if
I define the schema "dbo" in the datastore connection parameters or
leave it empty.

Summa summarum, it works after all, but for some reason GS cannot read
the SRID if using a custom schema. Just a minor issue.

Thanks!

Pekka

On Thu, Nov 5, 2009 at 12:44 AM, Andrea Aime <aaime@...> wrote:

> Pekka Hurskainen ha scritto:
>>
>> Dear list,
>>
>> I think I have stumbled upon a bug in the current SQL Server plugin.
>> Here is what I did:
>>
>> 1)      uploaded a polygon shapefile with Morten Nielsen’s shp2sql with
>> SRID constraint 21037
>> 2)      changed the schema from default “dbo” to a custom schema
>> “LandInfo”
>> in SQL Server Management Studio (Alter Schema LandInfo TRANSFER
>> dbo.parcels_geom). I can also see the SRID constraint
>> ([geom].[STSrid]=(21037))
>> 3)      Tried to create a layer of it in Geoserver 2.0.0, but both Native
>> SRS and Declared SRS are empty
>> 4)      If I manually enter “EPSG:21037” in Declared SRS and try to
>> calculate the BBOX (Compute from data), I get the following error
>> message. This also happens if I dont enter any SRS value.
>
> I've never used the sql server datastore, but trying a wild guess, how did
> you configure the datastore? Did you specify a target schema among
> the connection parameters?
> If not, try setting that up
>
> Cheers
> Andrea
>
>
> --
> Andrea Aime
> OpenGeo - http://opengeo.org
> Expert service straight from the developers.
>

--
Pekka Hurskainen
[phone]
pekka.hurskainen@...

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Geoserver-users mailing list
Geoserver-users@...
https://lists.sourceforge.net/lists/listinfo/geoserver-users

[Justin Deoliveira-6]

Hi Pekka,

Thanks for digging deeper. Can you open an issue report for this. Thanks.

-Justin

Pekka Hurskainen wrote:

> Doh,
>
> forgot to check the datastore settings. After adding my custom schema
> in the connection parameters and adding a new layer, I can see only
> the tables that are under that schema, as it should be. If I dont
> specify any schema, I can see all tables from all schemas. This is OK.
>
> When creating new layer with my custom schema specified in the
> datastore, Native SRS and Declared SRS appear still empty, but it lets
> me calculate bbox from data and the lat/lon bbox IF I enter the
> EPSG:21037 manually on the Declared SRS. The demo works OK.
>
> Still, if I try to create a layer which is in the default schema
> "dbo", I get both Native and Declared SRS automatically, regardless if
> I define the schema "dbo" in the datastore connection parameters or
> leave it empty.
>
> Summa summarum, it works after all, but for some reason GS cannot read
> the SRID if using a custom schema. Just a minor issue.
>
> Thanks!
>
> Pekka
>
> On Thu, Nov 5, 2009 at 12:44 AM, Andrea Aime <aaime@...> wrote:
>> Pekka Hurskainen ha scritto:
>>> Dear list,
>>>
>>> I think I have stumbled upon a bug in the current SQL Server plugin.
>>> Here is what I did:
>>>
>>> 1)      uploaded a polygon shapefile with Morten Nielsen’s shp2sql with
>>> SRID constraint 21037
>>> 2)      changed the schema from default “dbo” to a custom schema
>>> “LandInfo”
>>> in SQL Server Management Studio (Alter Schema LandInfo TRANSFER
>>> dbo.parcels_geom). I can also see the SRID constraint
>>> ([geom].[STSrid]=(21037))
>>> 3)      Tried to create a layer of it in Geoserver 2.0.0, but both Native
>>> SRS and Declared SRS are empty
>>> 4)      If I manually enter “EPSG:21037” in Declared SRS and try to
>>> calculate the BBOX (Compute from data), I get the following error
>>> message. This also happens if I dont enter any SRS value.
>> I've never used the sql server datastore, but trying a wild guess, how did
>> you configure the datastore? Did you specify a target schema among
>> the connection parameters?
>> If not, try setting that up
>>
>> Cheers
>> Andrea
>>
>>
>> --
>> Andrea Aime
>> OpenGeo - http://opengeo.org
>> Expert service straight from the developers.
>>
>
>
>

--
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Geoserver-users mailing list
Geoserver-users@...
https://lists.sourceforge.net/lists/listinfo/geoserver-users