Calculating Distance using SQL Server Spatial CLR Types

By Howard van Rooijen Co-Founder 20 October 2012

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.

namespace Endjin.Sql.Spatial.Example
{
    #region Using Directives

    using System;
    using System.Data.SqlTypes;

    using Microsoft.SqlServer.Types;

    #endregion 

    /// <summary>
    /// Sample app that uses SQL Spatial Types to calculate the distance between two locations.
    /// </summary>
    /// <remarks>
    /// If you want to find our a specific lon / lat use http://www.doogal.co.uk/LatLong.php
    /// </remarks>
    public class Program
    {
        public static void Main(string[] args)
        {
            const int WorldGeodeticSystemId = 4326;

            var firstlocationLonLat = new Tuple<string, string>("-0.081389", "51.502195");
            var secondlocationLonLat = new Tuple<string, string>("-0.185348", "51.410933");

            var firstLocationAsPoint = string.Format("POINT({0} {1})", firstlocationLonLat.Item1, firstlocationLonLat.Item2);
            var secondLocationAsPoint = string.Format("POINT({0} {1})", secondlocationLonLat.Item1, secondlocationLonLat.Item2);

            var firstLocation = SqlGeography.STGeomFromText(new SqlChars(firstLocationAsPoint), WorldGeodeticSystemId);
            var secondLocation = SqlGeography.STGeomFromText(new SqlChars(secondLocationAsPoint), WorldGeodeticSystemId);

            var distance = firstLocation.STDistance(secondLocation);

            Console.WriteLine("First Location is " + MetersToMiles((double)distance).ToString("0") + " miles from Second Location");
            Console.ReadKey();
        }

        public static double MetersToMiles(double? meters)
        {
            if (meters == null)
            {
                return 0F;
            }

            return meters.Value * 0.000621371192;
        }
    }
}

The solution is available from the endjin Samples repo on GitHub.

@HowardvRooijen