Nick’s Blog

Archive for December, 2010

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.BeginFigure(point.X, point.Y);
   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";

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 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!