THIS POST REFERS TO THE NOVEMBER 2007 CTP (CTP 5) OF SQL SERVER 2008
Following from my previous posts (Getting started with Spatial Data in SQL Server 2008, Spatial Data in a .NET application) on the new spatial features of SQL Server 2008 I’ve been looking at how to get spatial data into SQL Server from a .NET application. This has not been as easy as expected.
I suspect I just have not found the right piece of documentation because my eventual solution isn’t one I’m entirely happy with.
I was unable to add a SqlGeometry as a parameter to the collection of parameters on the SqlCommand object in my .NET application. The SqlGeometry does not appear in the enumeration for SQL Server data types. My first thought was to put the data through as binary as I vaguely remember reading something about using binary in something I read recently, but I couldn’t quite remember where. However, when I created the geometry object in .NET then used .STAsBinary() on it so the parameter object would accept it the INSERT failed. The exception message was:
A .NET Framework error occurred during execution of user defined routine or aggregate 'geometry': System.FormatException: One of the identified items was in an invalid format. System.FormatException: at Microsoft.SqlServer.Types.GeometryData.Read(BinaryReader r) at Microsoft.SqlServer.Types.SqlGeometry.Read(BinaryReader r) at SqlGeometry::.DeserializeValidate(IntPtr , Int32 ) . The statement has been terminated.
Yes, that was the MESSAGE from the exception. The stack trace above comes from within SQL Server itself. There is a separate stack track for my application. (I’m guessing if you are familiar with CLR stored procedures you may have seen this sort of thing before. I’ve not used the CLR code in SQL Server before, so it is a new experience)
The solution I found was to mark the parameter as an NVarChar, skip the creation of an SqlGeometry object and use a string containing the WKT (Well Known Text) representation. For example:
cmd.Parameters.Add("@Point", SqlDbType.NVarChar) = "POINT (312500 791500)";
I mentioned earlier that I’m not all that happy with this solution. That is because if I already have an SqlGeometry object I don’t want to have to convert it to a human readable format and have SQL Server parse it back again. Human Readable formats tend not to be the most efficient way of representing data and the geometry type already has a more efficient (as I understand it) format. Although in this case I bypassed the creation of an SqlGeometry object in my code, I can foresee cases where I might have created an SqlGeometry object through various operations and would have produced a fairly complex object. In those cases I wouldn’t want the additional overhead formatting it into WKT, passing it over the network and parsing it on the other side.
If I find a better solution I will post it.
Hi, did you manage to find a solution to your problem.I have just used the SqlGeometryBuilder to create my SqlGeometry object, i dont want to pass this to the stored proc as a WKT representation.Did you find a better way to do this?CheersRohan
Every UserDefinedType has Read and Write methods that server uses to serialize the type for storage or transfer.Try using the SqlGeometry.Write(BinaryWriter) method.It will serialize the geometry using server binary serialization format (different from WellKnownBinary!).To insert geometry (or any other UserDefinedType):INSERT table1 (id, geom) VALUES (1, 0x12345….)Where 0x12345…. is hexadecimal representation of geometry created using the Write method above.
Hi, I have a geometry of type SqlGeometry and then I convert it into varbinary.I then use a BinaryReader to read the bytes.BinaryReader r = new BinaryReader(new MemoryStream(byteGeomIn));Then I use SqlGeometry’s read method to read the binary.sqlGeom.Read(r);It should be working properly,but I am getting an error at the Read statement. It says Invalid Format or Spatial Reference Id should be between 0-9999. But while creating the geometry type in Sql, i have given the SRID as 0. I dont understand what the problem could be. Can anyone help me out?
When you convert the geometry to VarBinary are you converting it to WKB (Well Known Binary) or just a straight conversion of bytes. I suspect (but since I don’t have SQL Server 2008 in front of me, cannot confirm) that your storing the internal binary format. However, when interacting between the SqlGeometry objects and a binary representation you need to use WKB.Like I said, I can’t confirm this, so I may be completely wrong. Also, it has been a while since I did any spatial stuff in SQL Server.