23 Jan 2009

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 3rd 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

19 Jan 2009

Business Intelligence with SQL Server 2008 Geometry Data Type.

I am currently working on a project which is automating a business intelligence process base upon images and image recognition. The process is as follows:

  • Take a photography
  • Put the photography through the image recognition software and record the recognition data.
  • Process the recognition data into meaningful business metrics
  • Produce a report on metrics

The part of the process, from above, that I am helping with is processing the recognition data (the hit data) into meaningful business data. This process simply takes the hit data (which is a point), then creates some square blocks, which I use to group the hits data and perform some metric operations. The metrics currently use data about the area and the density of hits within the area. To help process this data I decided to make use of the SQL Server 2008 spatial type: Geometry.

Firstly I would like to point out that I didn’t have to use the geometry type. As I am currently dealing with square areas, I can group or locate all the hits that fall within area by using the following filter clause: HitY between MinY and MaxY and HitX between MinX and MaxX. However by reading the filter alone can cause misunderstanding on what the filter is suppose to be doing. As I am using the geometry data type that filter is replaced by something which does state what the filter is doing, like so: area.STIntersects(HitPoint) = 1 with the added bonus of less code being written and read as well as less chance of a bug being created.

Another point I would to make is that by using the geometry type my code is adaptable to changes. Currently our process is working to square areas and in the future that might change. Using the standard SQL filters that would mean all the filter clauses of the data would have to be updated. Also I would need to be more sophisticated, which could take a longer development and testing cycle which could increase the risk of issues. With the use of the geometry type, only the way that the area polygon is created would need to be updated which will help in reducing the development and testing cycle which interns reduces the risk of issues.

Another benefit of having some of the data stored as the geometry spatial data type is that I was able to evaluate or debug the steps of the process far easier. This is due to the way that SQL Server Management Studio display spatial data as shown below.

SpatialGrid