Coder Perfect

Distance between two points is calculated (Latitude, Longitude)


I’m attempting to determine the distance between two points on a map. Longitude, Latitude, X POS, and Y POS are all stored in my data.

I have been previously using the below snippet.

SET @orig_lat=53.381538 set @orig_lng=-1.463526
    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 believe the data that comes out of this, as it appears to be slightly wrong.

In case you require it, here is some sample data.

Latitude        Longitude     Distance 
53.429108       -2.500953     85.2981833133896

Could somebody assist me with my code? I don’t mind if you want to fix what I already have, but if you have a better solution, that would be fantastic.

Please specify the unit of measurement for your results.

Asked by Waller

Solution #1

Because you’re using SQL Server 2008, you have access to the geography data type, which is built specifically for this type of data:

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

SELECT @source.STDistance(@target)



(1 row(s) affected)

The distance between (near) London and (near) Edinburgh is approximately 538 kilometers.

Naturally, there will be some learning curve, but once you get the hang of it, it’s a lot easier than doing your own Haversine calculation, and you gain a lot more functionality.

You can still utilize STDistance if you wish to keep your present data structure by creating appropriate geography objects with 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);

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

Answered by AakashM

Solution #2

The function below calculates the distance in miles between two geocoordinates.

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
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
set @d = 3958.75 * Atan(Sqrt(1 - power(@d, 2)) / @d);
return @d

The function below calculates the distance in kilometers between two geocoordinates.

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

    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

The following function uses the Geography data type, which was introduced in SQL Server 2008, to calculate the distance between two geocoordinates in kilometers.

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);


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

Reference: Ref1,Ref2

Answered by Durai Amuthan.H

Solution #3

Microsoft appears to have infiltrated the minds of all other respondents and forced them to write the most convoluted solutions imaginable. The simplest method, which does not require any additional functions or declare statements, is as follows:

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

Simply replace LATITUDE 1, LONGITUDE 1, LATITUDE 2, LONGITUDE 2 with your data, for example:

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

Answered by Stalinko

Solution #4

Create Function [dbo].[DistanceKM] 
      @Lat1 Float(18),  
      @Lat2 Float(18), 
      @Long1 Float(18), 
      @Long2 Float(18)
Returns Float(18)
      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; 



pick dbo.DistanceKM from the drop-down menu (37.848832506474, 37.848732506474, 27.83935546875, 27.83905546875)



With commented floats, you can edit the @R parameter.

Answered by Fatih K.

Solution #5

I’d recommend looking at the GEOGRAPHY data type if you’re using SQL 2008 or later. Geospatial queries are already supported by SQL.

For example, you might have a GEOGRAPHY column in your table that is supplied with a geographical representation of the coordinates (check out the MSDN reference linked above for examples). The methods exposed by this datatype allow you to run a variety of geographical searches (e.g. finding the distance between 2 points)

Answered by AdaTheDev

Post is based on