Find the distance between two coordinates using SQL and C#

Imagine you’re in the middle of Millennium Park in Chicago. You want some coffee, but want a good deal on it. You open an app and see the nearest coffee shops offering deals to anyone with this app.

How did this app know the distance from you to the coffee shops? This article will show you how to calculate the distance between two coordinates by using built-in functions in SQL and C#.

Haversine formula

Before jumping into the code, it’s important to note that the distance calculation is done using the Haversine formula. Of course you could implement this formula in your language of choice. However, since SQL and C# both have built-in functions to calculate the distance, I would recommend using them.

As a general principle, it’s a good idea to use built-in functionality instead of rolling your own.

Using SQL

Use the geography data type and the STDistance() function.

Here is a working example of how to query to find all locations within 0.10 miles.

1 – Add a column with type = geography

2 – Insert coordinates data into the table

INSERT INTO [dbo].[Stores]
           ([StoreID]
           ,[StoreName]
           ,[City]
           ,[LatLong])
     VALUES
           (1
           ,'Wildberry Pancakes & Cafe'
           ,'Chicago'
           ,geography::Point(41.884675, -87.622883, 4326))

INSERT INTO [dbo].[Stores]
           ([StoreID]
           ,[StoreName]
           ,[City]
           ,[LatLong])
     VALUES
           (2
           ,'Intelligentsia Coffee Millennium Park Coffeebar'
           ,'Chicago'
           ,geography::Point(41.884287, -87.625776, 4326))

INSERT INTO [dbo].[Stores]
           ([StoreID]
           ,[StoreName]
           ,[City]
           ,[LatLong])
     VALUES
           (3
           ,'Toni Patisserie & Cafe'
           ,'Chicago'
           ,geography::Point(41.883120, -87.625476, 4326))


INSERT INTO [dbo].[Stores]
           ([StoreID]
           ,[StoreName]
           ,[City]
           ,[LatLong])
     VALUES
           (4
           ,'Nutella Cafe Chicago'
           ,'Chicago'
           ,geography::Point(41.885580, -87.624252, 4326))
Code language: SQL (Structured Query Language) (sql)

3 – Query for all locations within 0.11 miles

Note: STDistance() returns the number of meters. There are 1609.344 meters / mile.

DECLARE @city nvarchar(50) = 'Chicago'
DECLARE @currentLocation geography = geography::Point(41.883541, -87.623508, 4326)
DECLARE @METERS_PER_MILE numeric(19,5) = 1609.344

SELECT StoreID, StoreName, LatLong.STDistance(@currentLocation)/@METERS_PER_MILE as [miles] 
FROM Stores
WHERE City = @city AND LatLong.STDistance(@currentLocation)/@METERS_PER_MILE < 0.11
ORDER BY  LatLong.STDistance(@currentLocation)  

Code language: SQL (Structured Query Language) (sql)

The results of the query:

Using C#

Use the GeoCoordinate class and the DistanceTo() function.

Here’s a working example.

1 – Add a reference to System.Device

2 – Find all GeoCoordinate objects with DistanceTo() within 0.11 miles

using System;
using System.Collections.Generic;
using System.Device.Location;
using System.Linq;

namespace FindNearestStores
{
    public class Program
    {
        static void Main(string[] args)
        {

            var stores = new StoreRepository()
            {
                new Store("Wildberry Pancakes & Cafe", 41.884675, -87.622883),
                new Store("Intelligentsia Coffee Millennium Park Coffeebar", 41.884287, -87.625776),
                new Store("Toni Patisserie & Cafe", 41.883120, -87.625476),
                new Store("Nutella Cafe Chicago", 41.885580, -87.624252)
            };
            foreach(var store in stores.GetStoresWithinXMiles(41.883541, -87.623508, 0.11))
            {
                Console.WriteLine(store.StoreName);
            }
        }
    }

    public class Store
    {
        public string StoreName { get; set; }
        public GeoCoordinate LatLong { get; set; }
        public Store(string storeName, double latitude, double longitude)
        {
            StoreName = storeName;
            LatLong = new GeoCoordinate(latitude, longitude);
        }
    }

    public class StoreRepository : List<Store>
    {
        private const double METERS_PER_MILE = 1609.344;

        public IEnumerable<Store> GetStoresWithinXMiles(double latitude, double longitude, double miles)
        {
            var userCoords = new GeoCoordinate(latitude, longitude);
            return this.Where(s => (s.LatLong.GetDistanceTo(userCoords) / METERS_PER_MILE) <= miles);
        }
    }
}

Code language: C# (cs)

The results of running this:

Wildberry Pancakes & Cafe
Toni Patisserie & CafeCode language: plaintext (plaintext)