Update (7/1/2011): I wrote this post early in the SQL 2008 CTP. I no longer use the method described here. If you found this post via StackOverflow or some other Q&A site, I highly recommend this post by Vish as a more efficient way of doing things: http://viswaug.wordpress.com/2008/09/29/inserting-spatial-data-in-sql-server-2008/
I’m in the process of using SharpMap to write a utility to load spatial data into my SQL Server 2008 instance. A couple of the SharpMap data providers have a static method to load data from a shapefile (really any other SharpMap data provider, but I digress). The method creates a table and uses an ADO.Net command object (SqlCommand in this case) load individual records into the table. I started using the existing MsSql provider as a template, which load the geometry’s WKB into an image column.
The problem with SQL 2008 comes in when you add a parameter to the command. You must specify the data type of the parameter by passing it a SqlDbType enumeration. Currently, there is no enumeration that corresponds to geometry (or geography for that matter). My first attempt was to leave the parameter defined as the type SqlDbType.VarBinary and set the value as such:
[sourcecode language=”csharp”]
//define parameter
command.Parameters.Add("@shape", SqlDbType.VarBinary);
//
//code in between omitted
//
//set value of parameter
command.Parameters["@shape"].Value = feature.Geometry.AsBinary();
[/sourcecode]
In this case, feature.Geometry is of type SharpMap.Geometries.Geometry and AsBinary returns the WKB representation of the shape. This code failed when attempting to execute the command, throwing an exception indicating that the table column (type geometry) was not a valid binary data type.
So I next tried defining the data type of the parameter as SqlDbType.Udt (user-defined type) and setting the UdtTypeName property to “geometry”. This failed also.
On a whim (I wish I could say it was something more scientific than a desire to leave no stone unturned), I then tried working with text and set it up this way:
[sourcecode language=”csharp”]
//define parameter
command.Parameters.Add("@shape", SqlDbType.NVarChar);
//
//code in between omitted
//
//set value of parameter
command.Parameters["@shape"].Value = feature.Geometry.AsText();
[/sourcecode]
In this case, AsText returns the WKT representation of the shape. This worked! I have not dug into the details of why it worked but it seems the underlying spatial objects in SQL 2008 have some built-in default behaviours that coerce WKT. I am now loading data via a WinForms app with the aid of SharpMap. So, if you’re trying to update geometry from within your code, the above example is what worked for me.
I’ll probably post the code to the data loader a little bit later after I incorporate SRID and geometry type constraints and the like.
Hey, thanks! Are you sitting near me today? I literally pulled this back up today to tackle the same thing. 😀
Good catch!
I used your code as a sample to get started with a similar insert, but was able to figure out how to do a binary insert and thought I would share that code in case you find it useful. I even got this to work with adhoc queries passing in the binary as a hex string.
When I was using the AsBinary() method I was not getting values that matched what was stored in SQL Server if I created the same geometry object in the management studio. I could not find any of the AsXYZ methods that returned the internal binary representation with the SRID attached.
I was finally able to get the internal binary representation using the following code where Lon and Lat are variables of tyep double.
System.IO.MemoryStream ms = new System.IO.MemoryStream();
System.IO.BinaryWriter bw = new System.IO.BinaryWriter(ms);
Microsoft.SqlServer.Types.SqlGeometry.Point(Lon, Lat, 4326).Write(bw);
return ms.ToArray();
The Write() method got identical binary and from then on out I was able to get udpates and inserts to start working.
I originally tried that but I got a type mismatch when trying to specify the data type of the parameter because it expects a SqlDbType. Is there something I missed?
Why don’t you just use the two types Microsoft.SqlServer.Types.SqlGeography and Microsoft.SqlServer.Types.SqlGeometry ?
Yes, that is a very meaty discussion. I am with you in that I think they should pick x,y and run with it. The idea that the non-professional “thinks” in lat/long is not particularly compelling because few of them will be examining WKT output from geography objects. If they need to see it that way for a given location, that’s a simple switch in the presentation tier. The ones who can make sense of WKT can probably handle the mental math.
they should just switch to what everyone else expects (regardless of the fact that the OGC botched the spec by not including a specific order for WKT)
good stuff here, I’m sure you’ve read it already but some of your readers might not have
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2431933&SiteID=1
cheers
brian
Brian,
Thanks for the tip! I’ll keep that in mind as I test the data loader. I was just happy to see some data actually go in!
Right now, my code uses the geometry data type because that works with the WKT generated by SharpMap. If I implement support for the geography type, I’ll have to handle the x,y switching.
I think they need to be consistent here. This could be a real pain.
Bill
good stuff bill! don’t forget the x,y axis switch issue with MS’s version of WKT (versus everyone elses)…it does affect polygon winding order for the geometry type
cheers
brian