[Geoserver-users] Does SQL Server extension work

keith0 · April 28, 2009, 3:35pm

I don't know if the SQL Server extension work or not. I posted a message for
the issue in creating FeatureType that couldn't populate SRS, Min/Max Long,
Min/Max Lat. After entering them maually, cannot
see map in preview.

keith
--
View this message in context: http://www.nabble.com/Does-SQL-Server-extension-work-tp23279594p23279594.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

Andrea_Aime3 · April 30, 2009, 3:19pm

keith_c888 ha scritto:

I don't know if the SQL Server extension work or not. I posted a message for
the issue in creating FeatureType that couldn't populate SRS, Min/Max Long,
Min/Max Lat. After entering them maually, cannot see map in preview.

It certainly worked on some sample data set and some specific SQLServer versions on top of which it was developed, but it cannot compare to
the support with provide for PostGIS, that devs use day in day out
instead.

You should consider looking into helping out the development of that
extension so that it works within your environment, or have someone
do it for you.

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Custom development for your GeoServer needs

Justin_Deoliveira · May 4, 2009, 5:27pm

Andrea Aime wrote:

keith_c888 ha scritto:

I don't know if the SQL Server extension work or not. I posted a message for
the issue in creating FeatureType that couldn't populate SRS, Min/Max Long,
Min/Max Lat. After entering them maually, cannot see map in preview.

It certainly worked on some sample data set and some specific SQLServer versions on top of which it was developed, but it cannot compare to
the support with provide for PostGIS, that devs use day in day out
instead.

You should consider looking into helping out the development of that
extension so that it works within your environment, or have someone
do it for you.

Yes it works, and I tested it with some of the cencus data that you have had issues with. To help us diagnose your problem, please:

1) include a full stack trace of any errors you are getting
2) include complete versions of gs and the sql server extensions
3) the platform you are running on

Thanks.

-Justin

Cheers
Andrea

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

Justin_Deoliveira · May 7, 2009, 2:33pm

Hi Keith,

Sorry for the delayed reply. But I was able to reproduce the problem. The issue is as the error states, with the srs. I have opened a jira report for this.

http://jira.codehaus.org/browse/GEOS-2997

If you want to work around it in the short term, specify the 4326 srs in the shape2sql shapefile importer when you create the table. Then it should work fine.

-Justin

keith chan wrote:

Hi Justin,

    Windows 2003 Server with sp2 SQL Server 2008 Enterprise edition

I did followings
1. Download and install JDK 6 Update 13 with Java EE

2. Download geoserver-1.7.4.exe and install GeoServer 1.7.4

3. Download SQL Server extension geoserver-1.7.4-sqlserver-plugin.zip Unzip then zip file and copy gt-jdbc-sqlserver-2.5.5.jar, gt-jdbc-core-2.5.5-test.jar, gt-jdbc-core-2.5.5.jar to C:\Program Files\GeoServer 1.7.4\webapps\geoserver\WEB-INF\lib

4. Download Microsoft JDBC driver sqljdbc_1.2.2828.100_enu.exe and run it. Copy sqljdbc.jar to the same directory as above.

5. Download st99_d00.shp, st99_d00.shx, st99_d00.dbf from http://www.census.gov/>

6. Download SqlSpatialTools.zip and unzip it.

7. Run Shape2Sql.exe and import data from st99_d00.shp to a database in SQL Server 2008 with table names us_states

8. Start geoServer and launch I.E browser and type http://localhost:8080/geoserver

9. Click Data, click Namespace, and create a Namespace, e.g. Test

10. Click DataStores, click New, and select Microsoft SQL Server from Feature Data Set Description dropdown list, and enter ID1 for Feature Data Set ID.
Select namespace just created, enter host, port, user, password, then click Submit, Apply, Save buttons.
Note: here I didn’t enter database name for database field, otherwise, it says Unable to obtain connection.

11. Click FeatureTypes, click New. I can see ID1::us_states in Feature Type Name dropdown list. Select it and click New button.
In FeatureType Editor, SRS field has value 0 (should be populated with 4326). If clicking Lookup SRS button, it populated UNKNOWN for SRS field. I entered 4326 for SRS and click Generate button for Bounding Box field and it said

  GeoServer - Exception

The following exception was thrown:

    * class java.lang.RuntimeException: java.io.IOException
    * class java.io.IOException:
    * class org.opengis.referencing.NoSuchAuthorityCodeException: No
      code "EPSG:0" from authority "European Petroleum Survey Group"
      found for object of type "IdentifiedObject".

Details:

java.lang.RuntimeException: java.io.IOException
  at org.geotools.jdbc.JDBCFeatureSource.getBoundsInternal(JDBCFeatureSource.java:440)
  at org.geotools.jdbc.JDBCFeatureStore.getBoundsInternal(JDBCFeatureStore.java:141)
  at org.geotools.data.store.ContentFeatureSource.getBounds(ContentFeatureSource.java:357)
  at org.geotools.data.store.ContentFeatureSource.getBounds(ContentFeatureSource.java:329)
  at org.geoserver.feature.FeatureSourceUtils.getBoundingBoxEnvelope(FeatureSourceUtils.java:44)
  at org.vfny.geoserver.util.DataStoreUtils.getBoundingBoxEnvelope(DataStoreUtils.java:318)
  at org.vfny.geoserver.action.data.TypesEditorAction.executeBBox(TypesEditorAction.java:236)
  at org.vfny.geoserver.action.data.TypesEditorAction.execute(TypesEditorAction.java:138)
  at org.vfny.geoserver.action.ConfigAction.execute(ConfigAction.java:101)
  at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
  at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
  at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
  at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
  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.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
  at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
  at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:264)
  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:274)
  at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)
  at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
  at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125)
  at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
  at org.acegisecurity.ui.rememberme.RememberMeProcessingFilter.doFilter(RememberMeProcessingFilter.java:142)
  at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
  at org.acegisecurity.wrapper.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:81)
  at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
  at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:217)
  at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
  at org.acegisecurity.ui.logout.LogoutFilter.doFilter(LogoutFilter.java:106)
  at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
  at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:229)
  at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
  at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148)
  at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98)
  at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
  at org.geoserver.filters.LoggingFilter.doFilter(LoggingFilter.java:73)
  at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
  at org.geoserver.filters.ReverseProxyFilter.doFilter(ReverseProxyFilter.java:163)
  at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
  at org.geoserver.filters.GZIPFilter.doFilter(GZIPFilter.java:41)
  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
  at org.geotools.data.sqlserver.SQLServerDialect.decodeGeometry(SQLServerDialect.java:188)
  at org.geotools.data.sqlserver.SQLServerDialect.decodeGeometryEnvelope(SQLServerDialect.java:212)
  at org.geotools.jdbc.JDBCDataStore.getBounds(JDBCDataStore.java:852)
  at org.geotools.jdbc.JDBCFeatureSource.getBoundsInternal(JDBCFeatureSource.java:433)
  ... 61 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:949)
  at org.geotools.referencing.factory.PropertyAuthorityFactory.getWKT(PropertyAuthorityFactory.java:314)
  at org.geotools.referencing.factory.PropertyAuthorityFactory.createCoordinateReferenceSystem(PropertyAuthorityFactory.java:388)
  at org.geotools.referencing.factory.BufferedAuthorityFactory.createCoordinateReferenceSystem(BufferedAuthorityFactory.java:781)
  at org.geotools.referencing.factory.AuthorityFactoryAdapter.createCoordinateReferenceSystem(AuthorityFactoryAdapter.java:798)
  at org.geotools.referencing.factory.FallbackAuthorityFactory.createCoordinateReferenceSystem(FallbackAuthorityFactory.java:638)
  at org.geotools.referencing.factory.AuthorityFactoryAdapter.createCoordinateReferenceSystem(AuthorityFactoryAdapter.java:798)
  at org.geotools.referencing.factory.ThreadedAuthorityFactory.createCoordinateReferenceSystem(ThreadedAuthorityFactory.java:729)
  at org.geotools.referencing.DefaultAuthorityFactory.createCoordinateReferenceSystem(DefaultAuthorityFactory.java:179)
  at org.geotools.referencing.CRS.decode(CRS.java:420)
  at org.geotools.referencing.CRS.decode(CRS.java:348)
  at org.geotools.data.sqlserver.SQLServerDialect.decodeGeometry(SQLServerDialect.java:185)
  ... 64 more

I entered Min Long=-179.147354125977, Max Long=179.778472900391, Min Lat=17.8848114013672, and Max Lat=71.3525619506836 manually which should be populated automatically (I got the values by FeatureType created by PostGIS datastore).
Then click Submit, Apply, Save buttons.
Note: I imported same data from the shape file to a database in PostgreSQL. Then create datastore and FeatureType. It populated SRS and Long and Lat.

12. Click OpenLayers client at first page of GeoServer, I can see the FeatureType just created by SQL Server datastore. Click it and it shows zoom icon, Scale, coordinator without state map. I pressed left mouse button inside map display window, dragged and moved. After playing it several times, it said

java.lang.RuntimeException: java.lang.RuntimeException: java.io.IOException java.lang.RuntimeException: java.io.IOException java.io.IOException null No code "EPSG:0" from authority "European Petroleum Survey Group" found for object of type "IdentifiedObject".

13. I did similar thing by using PostgreSQL/PostGIS, it shows state map.
I got same result when working with geoserver 1.7.2 and 1.7.3.
Can you tell me your environment and what you did?
Thanks
keith

  > Date: Mon, 4 May 2009 11:27:46 -0600
> From: jdeolive@anonymised.com
> To: aaime@anonymised.com
> CC: keith_c888@anonymised.com; geoserver-users@lists.sourceforge.net
> Subject: Re: [Geoserver-users] Does SQL Server extension work
>
> Andrea Aime wrote:
> > keith_c888 ha scritto:
> >> I don't know if the SQL Server extension work or not. I posted a message for
> >> the issue in creating FeatureType that couldn't populate SRS, Min/Max Long,
> >> Min/Max Lat. After entering them maually, cannot
> >> see map in preview.
> >
> > It certainly worked on some sample data set and some specific SQLServer
> > versions on top of which it was developed, but it cannot compare to
> > the support with provide for PostGIS, that devs use day in day out
> > instead.
> >
> > You should consider looking into helping out the development of that
> > extension so that it works within your environment, or have someone
> > do it for you.
> Yes it works, and I tested it with some of the cencus data that you have
> had issues with. To help us diagnose your problem, please:
>
> 1) include a full stack trace of any errors you are getting
> 2) include complete versions of gs and the sql server extensions
> 3) the platform you are running on
>
> Thanks.
>
> -Justin
> >
> > Cheers
> > Andrea
> >
>
> --
> Justin Deoliveira
> OpenGeo - http://opengeo.org
> Enterprise support for open source geospatial.

------------------------------------------------------------------------
Create a cool, new character for your Windows Live™ Messenger. Check it out <http://go.microsoft.com/?linkid=9656621>

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