# Distance between two points is calculated (Latitude, Longitude)

## Problem

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.

``````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 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.

## 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)
``````

Gives

``````----------------------
538404.100197555

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

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

## 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
begin
declare @d decimal(28,10)
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 function below calculates the distance in kilometers between two geocoordinates.

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

Usage:

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

Reference: Ref1,Ref2

## 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
``````

## Solution #4

``````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)
* Sin(@dLon / 2)
* Sin(@dLon / 2);
Set @c = 2 * Asin(Min(Sqrt(@a)));

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

End
GO
``````

Usage:

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

Outputs:

0,02849639

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