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/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en