How to find the distance between two places in SQL Server

    You don't need to use any third party product to calculate the distance between two places. SQL Server can do that if you have the latitude and longitude of the locations.Let's say you need to find the distance between your shop and the users location. You can use the HTML5 Geolocation to retrieve your users location. You know where your shop is too. All you need is some kind of service which is going to take this two locations and give you the distance between your user and your shop in miles or kilometers.

   SQL Server can handle that. SQL Server has built in spatial functions to help you to find data in spatial objects. First we need to create spatial object for source location and the destination location. I am going to find the distance between Washington State Convention center and Microsoft HQ in the following example. First we need to find longitude and latitude of the locations. I used itouchmap to find the coordination of both places.

Washington State Convention Center

Microsoft HQ

   SQL Server uses its own unit called Point instead of longitude and latitude. First we need to convert the long/lat coordination to Point by using geography::Point function.

Declare @source geography = geography::Point(47.639322, -122.128383, 4326)
Declare @destination geography= geography::Point(47.611727, -122.332914, 4326)

    source parameter is the coordination of Washington State Convention Center, and destination is the Microsoft HQ coordination. Now we need to call the function called STDistance(). This function accepts another geography object and it returns the shortest line between two geography types. Returned data is in meters. If you like to convert meters to kilometers you need to divide the result by 1000, if you need the result in miles then you need to divide the result by 1609.344

Declare @source geography = geography::Point(47.639322, -122.128383, 4326)
Declare @destination geography= geography::Point(47.611727, -122.332914, 4326)

Select @source.STDistance(@destination) as Meters
Select @source.STDistance(@destination) / 1000 as Kilometers
Select @source.STDistance(@destination) / 1609.344 as Miles

Here is the results