Unable to use SharpMapMsSqlSpatialDemoDb.bak using SharpMap.Data.Providers.SqlServer2008

Topics: Data Access, SharpMap v0.9 / v1.x
Oct 6, 2011 at 9:37 AM

Hello All,

I am new to SharpMap, I am planning to integrate SharpMap in MOSS2007(Sharepoint).

we have sqlserver 2008 as backend database.

I am using V0.9 for SharpMap API, I have ran into problem in accessing the data which was restored using the  SharpMapMsSqlSpatialDemoDb.bak.

I am getting following strange error

Cannot find either column "shape" or the user-defined function or aggregate "shape.STIntersects", or the name is ambiguous.

I am using following code to access the data from sqlserver 2008

VectorLayer layCities = new VectorLayer("Cities");

//Set the datasource to a shapefile in the App_data folder 

 

 

layCities.DataSource =new SharpMap.Data.Providers.SqlServer2008(connectionString, "Cities", "oid");

error occuring in SQLServer2008.cs

 

using (SqlCommand command = new SqlCommand(strSQL, conn))

{

conn.Open();

using (SqlDataReader dr = command.ExecuteReader()) 

 the SQL look like this

SELECT g.shape.STAsBinary()  FROM Countries g WHERE shape.STIntersects(geometry::STGeomFromText('POLYGON ((43.265306122449 13.3333333333334, 56.5986394557823 13.3333333333334, 56.5986394557823 26.6666666666667, 43.265306122449 26.6666666666667, 43.265306122449 13.3333333333334))', 4326)) = 1

I abserve one thing in the SQLDatabase table dbo.cities has a column Geometry and it's datatype is 'varbinary(MAX)' , is this suppose to be 'geometry' ?

I am thing the problem could be my sqlserver restore?

Plase advise me.

Regards

Andrew

Coordinator
Oct 6, 2011 at 9:48 AM
Edited Oct 6, 2011 at 9:49 AM

Hello Andrew,

the SharpMapMsSqlSpatialDemoDb.bak relies on MsSqlSpatial, a spatial extension for SQLServer 2005. There is no demo db for SqlServer2008, but you can set it up fairly easy by just importing the countries-, cities- and rivers-shapefiles.

Hth FObermaier

Oct 6, 2011 at 10:49 AM

Thank you very much for quick response. Yes you spot on.

then how to import shape files in to sqlserver 2008?

I tried using http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx , but it working fine for cities.SHP but not working for other shape file which are in demo.

I am getting following errors

--------------------------
Error inserting row
---------------------------
Could not insert row #1:
NAME: Luxembourg
POPDENS: 149

System.DllNotFoundException: Unable to load DLL 'SqlServerSpatial.dll': Exception from HRESULT: 0xE06D7363

Select OK to ignore or Cancel to abort
---------------------------
OK   Cancel  
---------------------------

Coordinator
Oct 6, 2011 at 11:28 AM

Maybe you can use ogr2ogr (http://www.gdal.org/ogr/drv_mssqlspatial.html).

Do you meet the requirements, ie do you have a local sqlserver 2008 or have Microsoft SQL Server System CLR Types installed?

Is your SqlServer 2008 maybe a R2?

Hth FObermaier