How to load spatial data into SQL Server 2008 from .Net

I have been working on a project which made use of the spatial data type geography within SQL Server 2008.

An issue that I had was how to load the geography data from a KML file into SQL Server 2008. Currently there is no out of the box tools to do this. There is a 3^rd party tool, Safe FME, which offer either their own tool or components which extend integration services. This was overkill for my issues as I only had to do it once.

So I wrote a console application which parses the KML file to extract the point data convert it to a SqlGeography type and store it in the database.

To use the SqlGeography C# type you need to add reference to the following name space: Microsoft.SqlServer.Types. This can be found in the following dll: Microsoft.SqlServer.Types.dll
Then use the following code to create the c# sqlgeography type:

   1:  // use SqlGeographyBuilder to help create the SqlGeography type 

   2:  SqlGeographyBuilder geographyBuilder = new SqlGeographyBuilder(); 

   3:  string[] longLat; 

   4:  SqlGeography geography; 

   5:   

   6:  // gets the co-ordinates 

   7:  XElement coOrdinates = element.Element(ns + "Point").Element(ns + "coordinates"); 

   8:   

   9:  // set the Spatial Reference Identifiers that will used to create the point 

  10:  geographyBuilder.SetSrid(4326); 

  11:   

  12:  // state what type of geography object that I to create 

  13:  geographyBuilder.BeginGeography(OpenGisGeographyType.Point); 

  14:   

  15:  longLat = coOrdinates.Value.Split(new char[1] {','}); 

  16:   

  17:  // add the frist figure lat long point 

  18:  geographyBuilder.BeginFigure(Convert.ToDouble(longLat[1]), Convert.ToDouble(longLat[0])); 

  19:   

  20:  // close the figure and geography class 

  21:  geographyBuilder.EndFigure(); 

  22:  geographyBuilder.EndGeography(); 

  23:   

  24:  // get the geography builder to return the sqlgeography type 

  25:  geography = geographyBuilder.ConstructedGeography; 

  26:   

  27:  return geography; 

After creating the data type I then needed to write the code send the data to the SQL server which as follows:

   1:  // set the command text 

   2:  string sqlCommandText = "insert into [dbo].[Location]([Location],[CoOrdinates]) Values(@pLocation,@pCoOrdinates)"; 

   3:   

   4:  // create the command object and set which command type 

   5:  SqlCommand sqlCommand = new SqlCommand(sqlCommandText, sqlConnection); 

   6:  sqlCommand.CommandType = CommandType.Text; 

   7:   

   8:  // create and add the paramter for standard sql data type 

   9:  sqlCommand.Parameters.Add(new SqlParameter("@pLocation", name.Value)); 

  10:   

  11:  // create and add the paramter for sql geography data type 

  12:  // as I am using and system CLR type have to say what data type name the parameter is 

  13:  sqlCommand.Parameters.Add(new SqlParameter("@pCoOrdinates", geography) {UdtTypeName = "Geography"}); 

  14:   

  15:  // execute the command 

  16:  sqlCommand.ExecuteNonQuery(); 

Like the report viewer control the Microsoft.SqlServer.Types namespace is not installed with .Net. For the application to work on another computer, without having to install SQL server or their client tools, a Redistributable package would need to be installed.

The SQL Server System CLR Types package contains the components implementing the new geometry, geography, and hierarchyid types in SQL Server 2008. This component can be installed separately from the server to allow client applications to use these types outside of the server.

A version can be found here:

http://www.microsoft.com/en-gb/download/details.aspx?id=27596