Nick’s Blog

Tag: SQL Server 2008

Inserting a geometry into a SQL Server 2008 Database

by Nick on Dec.09, 2010, under GIS

I’m not sure why but it seems that there is a lack of documentation surrounding how you get spatial data into and out of a SQL server 2008 database. Some people resort to using WKT (well known text) others serialise to a binary format. I was not happy with either approach, I mean inserting data into a database should be relatively straightforward.

So before we go anything further. Here is the code I created to create a SQL Server 2008 point geometry and insert it into a table with two columns. The first column was an auto increment ID and is not shown, the second is called LOCATION and is defined as a Geometry as far as SQL Server is concerned.

private void Add(Point point)
{
   SqlGeometryBuilder builder = new SqlGeometryBuilder();
   builder.SetSrid(900913);
   builder.BeginGeometry(OpenGisGeometryType.Point);
   builder.BeginFigure(point.X, point.Y);
   builder.EndFigure();
   builder.EndGeometry();
   SqlGeometry geom = builder.ConstructedGeometry;
   SqlCommand cmd = _dbConnection.CreateCommand();
   cmd.CommandText = "INSERT INTO READING (LOCATION) VALUES (@geometry)";
   SqlParameter param = cmd.Parameters.AddWithValue("geometry", geom);
   param.UdtTypeName = "Geometry";
   cmd.ExecuteNonQuery();
}

The Point type that is passed in is my own and simply contains an X and a Y. The types SqlGeometryBuilder and SqlGeometry are defined in Microsoft.SqlServer.Types which requires that you install Microsoft SQL Server System CLR Types which you can find via http://www.microsoft.com/downloads/en/details.aspx?FamilyID=228de03f-3b5a-428a-923f-58a033d316e1&DisplayLang=en. Something that fooled me was that after installing this I expected the reference to be in the GAC. Well it isn’t, or at least it isn’t on my machine and instead I had to browse to C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dll. 

The lines to note are

   SqlParameter param = cmd.Parameters.AddWithValue(“geometry”, geom);
   param.UdtTypeName = “Geometry”;

The first adds the Geometry object as a parameter and the second tells SQL Server what kind of UDT (or user defined type) we are using. Strange that it was not possible to devise a way to make Geometry and Geography fully fledged types but the extra work is very little.

Leave a Comment :, , , more...

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!