Saturday, 24 January 2009

Calculating distance in C# (with SqlServer.Types)

WiredPrairie's post on Calculating the distance between two Zip Codes in C# starts with the line
Unfortunately, there are many classes built into the .NET framework, but a zip code distance calculator isn’t included.
which is true -- however with the release of SQL Server 2008 Microsoft has made available the redistributable Microsoft.SqlServer.Types assembly that contains the SqlGeography and SqlGeometry types. Within SQL Server 2008 this enables the new geospatial functions, but these types can also be used in C#. I thought I'd add the SQL Server types to his example, just for fun...

I've modified WiredPrairie's code to use Microsoft.SqlServer.Types.SqlGeography in place of manually calculating Radians and using the Haversine formula... only a few minor changes were required (view source):

0. Reference Microsoft.SqlServer.Types assembly


1. Replace the Distance() method completely
I haven't implemented miles here - just kilometres

The important bits being:
string lineString = String.Format("LINESTRING({0} {1}, {2} {3})"
, compare.Longitude, compare.Latitude
, this.Longitude, this.Latitude);
var g = Microsoft.SqlServer.Types.SqlGeography.STGeomFromText(
new System.Data.SqlTypes.SqlChars(new System.Data.SqlTypes.SqlString(
lineString
))
, 4326); // IMPORTANT for distance calc
geoDistance = Convert.ToDouble(g.STLength().ToString()) / 1000; // to kilometers

2. Remove the conversion to Radians
The SqlGeography type uses Latitude and Longitude in degrees.


3. Minor text changes (miles to kilometres)


4. and finally, change the input from '25 miles' to '40 kilometres'
Here is a comparison of the results -- there appears to minor differences in the calculation results - I assume due to rounding...


Of course, WiredPrairie's solution would run anywhere (including Silverlight) whereas adding a dependency to Microsoft.SqlServer.Types means you can only use this code where the SqlServer Types redistributable has been installed. It's still interesting to see the additional features provided by SQL Server 2008 - I hope some spatial types (without the SQL dependency) make their way into a future core .NET Framework release (and Silverlight 3.0??).

3 comments:

  1. Very cool addition/modification to my code.

    ReplyDelete
  2. really very good and informative. I decided to copy his code snippet and make sure it works in .NET 2.0 and also convert it to JavaScript. So below are both my C# 2.0 and JavaScript snippets for calculating the distance between two geocodes.


    distance calcualtor

    ReplyDelete
  3. Do you have a link to download your source for this? Thanks.

    ReplyDelete