[HOWTO] Calculate distance between two points (coordinates) on Microsoft SQL Server

Did you know that Microsoft SQL Server (versions 2016, 2017, 2019 and 2022) provides a method that returns the shortest distance between two points in a geography instance? I didn’t until now. There are many threads on StackOverflow supposing to use common algorithms to calculate the distance between two points in a coordination system using SQL. However the method provided by Microsoft SQL Server seems to be much more convenient.

The method is called STDistance. Let’s have a look at it.

According to the docs it returns the shortest distance (approximate) between two geography types. The deviation on common earth model from exact geodesic distance is less than 0.25% – so pretty exact for most use cases.

I was confronted with the situation of calculating the shortest distance (as the crow flies) between two coordinates (latitude / longitude). Assuming there is a table called Locations with the columns Latitude and Longitude, the distance in kilometers between two coordinates can be calculated as follows using STDistance.

SELECT geography::Point(loc1.[Latitude], loc1.[Longitude], 4326).STDistance(geography::Point(loc2.[Latitude], loc2.[Longitude], 4326)) / 1000
    FROM Locations loc1, Locations loc2
    WHERE loc1.Id = 1 AND loc2.Id = 2

In this example, the first two entries/rows of table Locations are queried as loc1 and loc2. The Latitude and Longitude of both rows are then converted into Point (geography data type). Last but not least, the STDistance method is executed on the first point and the second point is passed as an argument to STDistance. The division by 1000 is done to get the distance in kilometers as STDistance returns the result in meters.

4326 is the Spatial Reference Identifier (SRID). Based on Wikipedia, 4326 needs to be passed for ellipsoidal coordination system types (latitude / longitude).

Important note: STDistance() returns null if the spatial reference IDs (SRIDs) of the geography instances (points) do not match.

Let’s check the accuracy with a concrete example with the following coordinates.

loc1: 47.400203600000,8.163527300000
loc2: 47.081461300000,9.366688500000

STDistance returns 97.74398955266528 km

Google Maps distance measuring results in the following.