MsSQLSpatial in SharpMap v2

Topics: Data Access, SharpMap v2.0
May 6, 2009 at 1:57 PM
Hi,
First i like to say that the SharpMap project is very interesting and I have plans of contributing to this project in the future.

I have set up Microsoft SQL Server 2005 Express with MsSQLSpatial (v.0.1.1) deployed to a database (mssql_test). I then added some .shp files with the command line tool like:
msscmd -server=localhost\SQLEXPRESS -db=mssql_test -table=test -import=shp -shp_filename="a.shp"
The "a.shp" file comes from sharpmapv2.googlecode.com /svn/trunk/NetTopologySuite.Samples.Shapefiles. So far so good.

I then try adding the a new layer from mssqlspatial data source in the SharpMap.Demo.MapViewer application. But I get an exception on line 498 in SpatialDbProviderBase.cs (table.Merge(dt);).. ? I've tried different shp data from different table in my database. All the .shp files are viewable from SharpMap.Demo.MapViewer if loaded from file.

I then tried to find other software using MsSQLSpatial but I've had no luck. The AutoCad Map 3D have upgraded to 2010 and the provider from http://www.codeplex.com/MsSqlSpatial/Wiki/View.aspx?title=FAQ&referringTitle=Home (King.MsSqlSpatial) doesn't work anymore so I can't verify if it's something wrong with my database. GIS Explorer homepage is down..

So I have two questions:

A. What am I doing wrong with SharpMap?

B. Is MsSQLSpatial out of date? Should I use MS SQL Server 2008 Spatial instead?

/Best regards
Jonas Nilsson
Coordinator
May 6, 2009 at 2:36 PM
Hi Jonas, what is the datatype of the primary key column in the db? I think early on MsSqlSpatial used int keys which subsequently changed to long (bigint). The provider assumes that they are long. You could try building your own MsSqlSpatial (that would definately use long keys) make sure you deploy with external_access or unsafe permission.

The question about whether to use MsSqlSpatial or Sql Server 2008 is down to environment as much as anything - if you have access to sql server 2008 go for it you will benefit from spatial indexes; if you have to use Sql Server 2005 then you have to use MsSqlSpatial. Sql 2008 doesn't currently have any coordinate transformation inbuilt - so if you need transformation that may be a concern - though you can convert between Sql2008 and MsSqlSpatial to do your transformation or do it outside the db..

hth jd
May 7, 2009 at 6:51 AM
Hi!
Thanks for the fast reply. I checked the primary key column and it sure was of type int. I downloaded the MsSqlSpatial release from the download section and I didn't notice it was released in 2007 (version 0.1.1). The svn has a version 1.7.x !!

I will try to use MsSqlSpatial togehter with Sql Server 2005 Express for two reasons. The first being that it's free :). The second reason is that I may have to hack in some extra features in MsSqlSpatial and SharpMap in order to get my application to work the way i want. I'm imagining that it would be easier with an open source provider like MsSqlSpatial.

/Jonas
Coordinator
May 7, 2009 at 9:00 AM
Hi Jonas, please be aware that the 1.7.x branch breaks ILMerge so the constituent dlls are installed independently into the database, this makes the install/uninstall/reinstall much dirtier than the trunk version (which is the recommended one for the time being).. hth jd
May 7, 2009 at 9:17 AM
I finally got the MsSqlSpatial v. 1.7.x compiled and deployed to my database. I then imported some .shp files again (after some more problems http://mssqlspatial.codeplex.com/Thread/View.aspx?ThreadId=50750).

Now I get an Exception at line 1380 in MapPresenter2D.cs when adding MsSqlSpatial connection in the SharpMap.Demo.MapViewer application. The Envelope seems to be null. I can't really figure this out.

Btw, where do I find the "trunk version" of MsSqlSpatial? I used the Subversion URL: https://MsSqlSpatial.svn.codeplex.com/svn and compiled the Branches/MsSqlSpatial1.7.xTemp version because the /src/ version didn't build and I didn't understand what version that one was anyway.

johndiss: thanks for the fast reply. I'd be very grateful for any tips.

/Best regards
Jonas Nilsson
Coordinator
May 7, 2009 at 9:47 AM
Edited May 7, 2009 at 9:50 AM
Hi Jonas, by trunk I mean the stuff in the src dir (brain overload :s). It should build.. make sure you have installed ILMerge from http://www.microsoft.com/downloads/details.aspx?FamilyID=22914587-B4AD-4EAE-87CF-B14AE6A939B0&displaylang=en and copied the Ilmerge.CSharp.targets file to the appropriate dir.
The 1.7.x branch was a quick fix to a missing transformation in the ProjNet version that the 'src' version is built against - however it involved a lot of changes since ProjNet, NTS and GeoAPI had moved on. Somewhere in there, something breaks ILMerge and hence the issues with managing deployment of it.

Can you trace the db and see what is going in and out? cheers jd
May 8, 2009 at 7:41 AM

Hi again,

I installed the ILMerge plugin and recompiled the /src version of MsSqlSpatial. I then deleted my old database and started all over. But when i try to deploy i get this error:

C:\CVS\mssqlspatial.codeplex.com\MsSqlSpatial-2.0>msscmd -deploy -server=localho
st\SQLEXPRESS -db=mssql_test -deploypermission=EXTERNALACCESS
MsSqlSpatial Command Line Tool (Build 0.1.3415.13982)
Copyright © Ricardo Stuven 2006-2007
http://www.codeplex.com/MsSqlSpatial

Deploying to database 'mssql_test' at localhost\SQLEXPRESS:

Creating scripts...
Establishing connection...
Dropping assembly dependencies...
Running script 1 of 116...
Running script 2 of 116...
Running script 3 of 116...

Ohanterat undantag: System.ApplicationException: Exception occured executing scr
ipt:

IF (@@TRANCOUNT > 0)
                AND NOT EXISTS (SELECT name FROM sys.assemblies WHERE name=N'MsS
qlSpatialLibrary')
BEGIN
EXEC sp_executesql N'
        CREATE ASSEMBLY [MsSqlSpatialLibrary] AUTHORIZATION [public]
                FROM N''C:\CVS\mssqlspatial.codeplex.com\MsSqlSpatial-2.0\MsSqlS
patialLibrary.dll''
                WITH PERMISSION_SET = SAFE'
IF (@@ERROR <> 0)
                ROLLBACK TRANSACTION

END
 ---> System.Data.SqlClient.SqlException: CREATE ASSEMBLY for assembly 'MsSqlSpa
tialLibrary' failed because assembly 'MsSqlSpatialLibrary' failed verification.
Check if the referenced assemblies are up-to-date and trusted (for external_acce
ss or unsafe) to execute in the database. CLR Verifier error messages if any wil
l follow this message

I've tried -undeploy to clean things up but that didnt help.  I also tried to deply it as UNSAFE as I did earlier with the 1.7.x version. I think I got the same problem as described in http://mssqlspatial.codeplex.com/Thread/View.aspx?ThreadId=51327 .

/Jonas Nilsson

Coordinator
May 8, 2009 at 8:17 AM
Hi Jonas, from memory the switch is -deploy_permission=external_access hth jd
May 8, 2009 at 8:34 AM

Hi, sorry missed that, but I still get the same error