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.
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)
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
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
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 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
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)
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:
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.
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 https://stackoverflow.com/questions/13026675/calculating-distance-between-two-points-latitude-longitude