Vishful thinking…

Inserting spatial data in SQL Server 2008

Posted in .NET, C#, GIS by viswaug on September 29, 2008

I have been working on inserting data from FeatureClasses into SQL Server 2008 tables. There are a lot of examples online showing how spatial data can be inserted into SQL Server 2008 tables. Here is one from Hannes.

I took from cue from there and proceeded to do what every good programmer should do…parameterize the INSERT query. Here is a simplified version of the scenario I was trying to tackle…

sqlCommand.CommandText = INSERT INTO foo (id, geom) VALUES(1,@g);

sqlCommand.InsertParameter(“@g”, ‘POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))’);

The above INSERT command worked fine. But I also wanted to insert the geometry or geography with the WKID value from the FeatureClass. So I changed the above INSERT statement to use the geometry’s static method and provided the WKID as below

sqlCommand.InsertParameter(“@g”, ‘geometry::STGeomFromText(POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0)), 0)’);

But that didn’t go quite as expected. The INSERT statement was throwing an error saying that it did not recognize ‘geometry::STGeomFromText’ and was expecting ‘POINT, POLYGON, LINESTRING…’ etc.

System.FormatException: 24114: The label geometry::STGeomFrom in the input well-known text (WKT) is not valid. Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, or GEOMETRYCOLLECTION.

This led me to use the Udt SqlParamter type to get around the problem like below. This way I could use the SqlGeometry type in the ‘Microsoft.SqlServer.Types’ namespace and set the SRID directly on its ‘STSRID’ property. The ‘SqlGeometry’ and ‘SqlGeography’ types can be found in the Feature Pack for SQL Server 2008 that can be found here. The SqlGeometry reference can then be used as the value for the SqlParameter.

SqlParameter parameter = new SqlParameter( parameterName, value );

parameter.Direction = ParameterDirection.Input;
parameter.ParameterName = parameterName;
parameter.SourceColumn = sourceColumn;
parameter.SqlDbType = SqlDbType.Udt;
parameter.UdtTypeName = “GEOMETRY”;
parameter.SourceVersion = DataRowVersion.Current;

command.Parameters.Add( parameter );

The ‘UdtTypeName’ should be set to ‘GEOGRAPHY’ for inserting geography fields.

And it worked like a charm.

Update: Heard from Morten saying that using the SqlGeography and SqlGeometry types are almost twice as fast as using WKB (Well Known Binary). Using WKB is by itself faster than using WKT (Well Known Text).

Tagged with:

6 Responses

Subscribe to comments with RSS.

  1. yudy said, on November 7, 2008 at 8:12 am

    Yes, thanks for pointing that SqlParameter.
    But in my case there’s also requirements that utilize enterprise library to run the insertion.
    like below example:

    MainDatabase.AddInParameter(cmd, “Geography”, DbType.String, domain.Geography.ToString());

    MainDatabase is an object of Microsoft.Practices.EnterpriseLibrary.Data.Database

    This can work. but the srid value always set back to 4326, while I need different SRID that’s already in the domain.Geography field.

    As i’ve run to look in the Database methods, and there’s no overload to pass SqlParameter. Any ideas?

  2. viswaug said, on November 10, 2008 at 1:21 pm

    Hi Yudy,

    I am not quite sure I understand your problem completely. But just to double check, are you setting the WKID value on the geography ‘value’ that you are creating the sql parameter with?

    Thank You,

  3. Emmanuel said, on January 18, 2011 at 11:55 am

    HI guys,

    I get this error message after implementing similar solution.

    Specified type is not registered on the target server.System.String, mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089.

    Any ideas?

    • Behr said, on October 15, 2012 at 9:23 pm

      I had the same issue. I removed the UDT stuff. Just made the sql column type nvarchar and it fixed the issue.

  4. […] 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: […]

  5. […] an SQLParameter that will handle a geography data type. To do this I referenced a blog post at Vishful thinking… This blog details create a geography/geometry SQLParameter to pass along to SQL […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: