Calculating Distance using SQL Server Spatial CLR Types
by Howard van Rooijen
I created a quick sample to solve a problem today and thought I’d post the code as it took me a while to track down all the relevant bits of information.
The problem I wanted to solve was that I needed to be able to geocode a user’s postcode and then calculate the distance between their location and another. There are plenty of paid for services out there but I remembered reading a blog post by Steve Wright about manipulating spatial data to get it into SQL Server.
The following sample utilises the Sql Server Spatial Types Assembly, which can be found on NuGet. Essentially it allows you to create a SqlGeography type from two strings that represent Longitude and Latitude, utilising the SqlGeography.STGeomFromText method and then calculate the Distance using the STDistance method (on the SqlGeography instance) to calculate the distance (in meters) between the two points. There’s a simple helper method to calculate the distance into Miles.
Howard spent 10 years as a technology consultant helping some of the UK's best known organisations work smarter, before founding endjin in 2010. He's a Microsoft Accelerator Mentor, and a Microsoft Azure MVP. You can follow him on Twitter via @HowardvRooijen