Calculating distance between two points (Latitude, Longitude)

Question

I am trying to calculate the distance between two positions on a map. I have stored in my data: Longitude, Latitude, X POS, Y POS.

I have been previously using the below snippet.

DECLARE @orig_lat DECIMAL
DECLARE @orig_lng DECIMAL
SET @orig_lat=53.381538 set @orig_lng=-1.463526
SELECT *,
    3956 * 2 * ASIN(
          SQRT( POWER(SIN((@orig_lat - abs(dest.Latitude)) * pi()/180 / 2), 2) 
              + COS(@orig_lng * pi()/180 ) * COS(abs(dest.Latitude) * pi()/180)  
              * POWER(SIN((@orig_lng - dest.Longitude) * pi()/180 / 2), 2) )) 
          AS distance
--INTO #includeDistances
FROM #orig dest

I don't however trust the data coming out of this, it seems to be giving slightly inaccurate results.

Some sample data in case you need it

Latitude        Longitude     Distance 
53.429108       -2.500953     85.2981833133896

Could anybody help me out with my code, I don't mind if you want to fix what I already have if you have a new way of achieving this that would be great.

Please state what unit of measurement your results are in.

You shouldn't divide the argument to sine by the additional /2. Also you could have more accuracy in the Earth radius, as well as using some _Datum_ used e.g. by GPS system (WGS-84) that approximates Earth by an ellipsoid (with different radii at equator and to poles) — Aki Suihkonen, Oct 23 '12 at 08:59
@Waller, why don't you use Geography/Geometry (Spatial) type to achieve this ? — Habib, Oct 23 '12 at 09:00
I checked your calculation with Mathematica; it thinks the distance in statute miles (5280 feet) is 42.997, which suggests that your computation is not *slightly inaccurate*, rather it is *wildly inaccurate*. — High Performance Mark, Oct 23 '12 at 09:04

AakashM · Accepted Answer · 2014-03-11T14:54:20.080

144

Since you're using SQL Server 2008, you have the geography data type available, which is designed for exactly this kind of data:

DECLARE @source geography = 'POINT(0 51.5)'
DECLARE @target geography = 'POINT(-3 56)'

SELECT @source.STDistance(@target)

Gives

----------------------
538404.100197555

(1 row(s) affected)

Telling us it is about 538 km from (near) London to (near) Edinburgh.

Naturally there will be an amount of learning to do first, but once you know it it's far far easier than implementing your own Haversine calculation; plus you get a LOT of functionality.

If you want to retain your existing data structure, you can still use STDistance, by constructing suitable geography instances using the Point method:

DECLARE @orig_lat DECIMAL(12, 9)
DECLARE @orig_lng DECIMAL(12, 9)
SET @orig_lat=53.381538 set @orig_lng=-1.463526

DECLARE @orig geography = geography::Point(@orig_lat, @orig_lng, 4326);

SELECT *,
    @orig.STDistance(geography::Point(dest.Latitude, dest.Longitude, 4326)) 
       AS distance
--INTO #includeDistances
FROM #orig dest

edited Mar 11 '14 at 14:54

answered Oct 23 '12 at 09:06

AakashM

62,551
17
151
186

6

@nezam no - the longitude will be negative for places West of the [Prime Meridian](http://en.wikipedia.org/wiki/Prime_meridian), and positive for places East of it – AakashM May 26 '14 at 10:26
1

Using the built in function seems to be VERY slow. Eg in a 100,000 item loop, it takes 23 seconds instead of 1.4 seconds for my user defined function (see Durai's answer). – NickG Feb 02 '16 at 14:31
@NickG Spatial indexes may help. Also note that the haversine formula is for geodesic distances on *spheres*, of which the Earth is not one. [STDistance knows better](http://gis.stackexchange.com/questions/58762/what-formula-does-sql-server-2008-use-in-stdistance) (and links onward) – AakashM Feb 02 '16 at 15:02
@AakashM - Yes I realise it uses spherical model. In my case I just really need to find the nearest, or get an estimate in KM so a few metres of error isn't a problem. I've found the UDF to be always less than 0.6% different (just a few metres) to STDistance for my lat/long in the UK. – NickG Feb 03 '16 at 10:39
1

Just wanted to chime in and confirm @AakashM 's recommendation for spatial indexes + ... for an ETL application, the difference was several orders of magnitudes better after implementing the spatial indexes – Bill Anton Oct 04 '16 at 13:58
3

FYI: POINT(LONGITUDE LATITUDE) whereas geography::Point(LATITUDE, LONGITUDE, 4326) – Lzh May 18 '17 at 09:13
Is there a way to ORDER BY Distance in this example? Works fine without the order by, but when I add that, i get this error: Msg 6522, Level 16, State 1, Line 36 A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": System.FormatException: 24201: Latitude values must be between -90 and 90 degrees. System.FormatException: at Microsoft.SqlServer.Types.SqlGeography.Point(Double latitude, Double longitude, Int32 srid) – Dennis Sep 07 '17 at 14:46
Hi I tried using this method,And I do get the distance. but what I am seeing as a problem is the distance received from it and distance from google maps is way off – Jerin Cherian May 06 '20 at 15:49
@JerinCherian probably best if you [ask a new question](https://stackoverflow.com/questions/ask) with the specific details – AakashM May 07 '20 at 10:52

score 50 · Answer 2 · edited May 23 '17 at 10:31

50

The below function gives distance between two geocoordinates in miles

create function [dbo].[fnCalcDistanceMiles] (@Lat1 decimal(8,4), @Long1 decimal(8,4), @Lat2 decimal(8,4), @Long2 decimal(8,4))
returns decimal (8,4) as
begin
declare @d decimal(28,10)
-- Convert to radians
set @Lat1 = @Lat1 / 57.2958
set @Long1 = @Long1 / 57.2958
set @Lat2 = @Lat2 / 57.2958
set @Long2 = @Long2 / 57.2958
-- Calc distance
set @d = (Sin(@Lat1) * Sin(@Lat2)) + (Cos(@Lat1) * Cos(@Lat2) * Cos(@Long2 - @Long1))
-- Convert to miles
if @d <> 0
begin
set @d = 3958.75 * Atan(Sqrt(1 - power(@d, 2)) / @d);
end
return @d
end

The below function gives distance between two geocoordinates in kilometres

CREATE FUNCTION dbo.fnCalcDistanceKM(@lat1 FLOAT, @lat2 FLOAT, @lon1 FLOAT, @lon2 FLOAT)
RETURNS FLOAT 
AS
BEGIN

    RETURN ACOS(SIN(PI()*@lat1/180.0)*SIN(PI()*@lat2/180.0)+COS(PI()*@lat1/180.0)*COS(PI()*@lat2/180.0)*COS(PI()*@lon2/180.0-PI()*@lon1/180.0))*6371
END

The below function gives distance between two geocoordinates in kilometres using Geography data type which was introduced in sql server 2008

DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SET @h = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
SELECT @g.STDistance(@h);

Usage:

select [dbo].[fnCalcDistanceKM](13.077085,80.262675,13.065701,80.258916)

Reference: Ref1,Ref2

edited May 23 '17 at 10:31

Community

1
1

answered Mar 18 '14 at 10:29

Durai Amuthan.H

31,670
10
160
241

2

I needed to do distance calculation for 35K zip codes against various events' zip codes ordered by distance to a zip code. It was too large of a list of coordinates to do calculations using the geography data type. When I switched to use the single-line trig functions based solution above, it ran much faster. So using geography types just to calculate a distance seems to be expensive. Buyer beware. – Tombala Dec 08 '16 at 21:17
Very useful for calculating distance in the WHERE clause of a query. I did have to wrap an ABS() around the "set @d=" expression, because I found some cases where the function returned a negative distance. – Joe Irby Sep 26 '17 at 18:04
2

Function for "distance between two geocoordinates in kilometres" fails if we compare 2 equal points, it gives you error "An invalid floating point operation occurred" – RRM Oct 13 '17 at 15:35
2

This was great but doesn't work for short distances because "decimal(8,4)" doesn't provide enough precision. – influent Oct 19 '17 at 23:26
1

Any reason you didn't just use the fnCalcDistanceKM (not using Geography) and divide the distance by 1.609344 to go from Km to Miles? Seems the Miles calculator is unnecessarily complex with modifying and reassigning values and doing checks. – Trevor F May 13 '19 at 14:02
1

@influent is correct, this is not useful for short distances (5 miles in my case) – Roger May 28 '19 at 18:26

score 31 · Answer 3 · edited Dec 03 '19 at 20:33

31

It looks like Microsoft invaded brains of all other respondents and made them write as complicated solutions as possible. Here is the simplest way without any additional functions/declare statements:

SELECT geography::Point(LATITUDE_1, LONGITUDE_1, 4326).STDistance(geography::Point(LATITUDE_2, LONGITUDE_2, 4326))

Simply substitute your data instead of LATITUDE_1, LONGITUDE_1, LATITUDE_2, LONGITUDE_2 e.g.:

SELECT geography::Point(53.429108, -2.500953, 4326).STDistance(geography::Point(c.Latitude, c.Longitude, 4326))
from coordinates c

edited Dec 03 '19 at 20:33

MDEWITT

2,338
2
12
23

answered Apr 10 '18 at 13:25

Stalinko

3,319
28
31

4

for reference: STDistance() returns distances in the linear unit of measure of the spatial reference system in which your geography data is defined. You're using SRID 4326, which means that STDistance() returns distances in metres. – Bryan Stump Feb 08 '19 at 19:19
This gives me the error 'Type geography is not a defined system type. '. I'm on SQL Server 2014. Any idea what I can do to solve this? – Jem Jul 27 '22 at 13:56
this gives an error when you use input data from a table that may contain NULL : 'geography::Point' failed because parameter 1 is not allowed to be null – Allie Jul 27 '23 at 14:53
@Allie obviously you need to add an additional check that both arguments are not null – Stalinko Jul 27 '23 at 18:02

score 8 · Answer 4 · answered Nov 09 '17 at 14:34

Create Function [dbo].[DistanceKM] 
( 
      @Lat1 Float(18),  
      @Lat2 Float(18), 
      @Long1 Float(18), 
      @Long2 Float(18)
)
Returns Float(18)
AS
Begin
      Declare @R Float(8); 
      Declare @dLat Float(18); 
      Declare @dLon Float(18); 
      Declare @a Float(18); 
      Declare @c Float(18); 
      Declare @d Float(18);
      Set @R =  6367.45
            --Miles 3956.55  
            --Kilometers 6367.45 
            --Feet 20890584 
            --Meters 6367450 


      Set @dLat = Radians(@lat2 - @lat1);
      Set @dLon = Radians(@long2 - @long1);
      Set @a = Sin(@dLat / 2)  
                 * Sin(@dLat / 2)  
                 + Cos(Radians(@lat1)) 
                 * Cos(Radians(@lat2))  
                 * Sin(@dLon / 2)  
                 * Sin(@dLon / 2); 
      Set @c = 2 * Asin(Min(Sqrt(@a))); 

      Set @d = @R * @c; 
      Return @d; 

End
GO

Usage:

select dbo.DistanceKM(37.848832506474, 37.848732506474, 27.83935546875, 27.83905546875)

Outputs:

0,02849639

You can change @R parameter with commented floats.

score 4 · Answer 5 · answered Oct 23 '12 at 09:00

4

As you're using SQL 2008 or later, I'd recommend checking out the GEOGRAPHY data type. SQL has built in support for geospatial queries.

e.g. you'd have a column in your table of type GEOGRAPHY which would be populated with a geospatial representation of the coordinates (check out the MSDN reference linked above for examples). This datatype then exposes methods allowing you to perform a whole host of geospatial queries (e.g. finding the distance between 2 points)

answered Oct 23 '12 at 09:00

AdaTheDev

142,592
28
206
200

Just to add, I tried the geography field type, but found using Durai's function (directly using longitude and latitude values) to be *much* faster. See my example here: http://stackoverflow.com/a/37326089/391605 – Mike Gledhill May 20 '16 at 09:01

score 2 · Answer 6 · answered Feb 06 '18 at 17:41

In addition to the previous answers, here is a way to calculate the distance inside a SELECT:

CREATE FUNCTION Get_Distance
(   
    @La1 float , @Lo1 float , @La2 float, @Lo2 float
)
RETURNS TABLE 
AS
RETURN 
    -- Distance in Meters
    SELECT GEOGRAPHY::Point(@La1, @Lo1, 4326).STDistance(GEOGRAPHY::Point(@La2, @Lo2, 4326))
    AS Distance
GO

Usage:

select Distance
from Place P1,
     Place P2,
outer apply dbo.Get_Distance(P1.latitude, P1.longitude, P2.latitude, P2.longitude)

Scalar functions also work but they are very inefficient when computing large amount of data.

I hope this might help someone.

Calculating distance between two points (Latitude, Longitude)

6 Answers6

Linked