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.

Answer 1

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 

Answer 2

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