Distance between two sets of latitude & longitudes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Distance between two sets of latitude & longitudes

Author

Topic

dmj07
Starting Member

8 Posts

Posted - 2011-07-27 : 11:54:05

I have this function that is meant to calculate the distance between two lat and longs.

[fnGetDistance]
(
@lat1 decimal(8,4),
@long1 decimal(8,4),
@lat2 decimal(8,4),
@long2 decimal(8,4)
)

RETURNS decimal(28,10)
AS
BEGIN

DECLARE @d decimal(28,10)

SET @d = 3958.75 * ( Atan(Sqrt(1 - power(((Sin(@lat1/57.2958) * Sin(@lat2/57.2958)) + (Cos(@lat1/57.2958) * Cos(@lat2/57.2958) * Cos((@long2/57.2958) - (@long1/57.2958)))), 2)) / ((Sin(@lat1/57.2958) * Sin(@lat2/57.2958)) + (Cos(@lat1/57.2958) * Cos(@lat2/57.2958) * Cos((@long2/57.2958) - (@long1/57.2958))))))

RETURN @d

END

The test values I have are:

Lat1 = 53.3739
Long1 = -2.7601
Lat2 = 53.3687
Long2 = -2.7378

Problem is it always returns 27 miles even though the distance should be around 1 mile from each other. Appreciate any help that can get me further in this. Thanks for your help in advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-27 : 12:03:46

You might want to compare your calculations and formula with the one described here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360

dmj07
Starting Member

8 Posts

Posted - 2011-07-27 : 13:26:07

Yes tried that one but same result of 27 when the result should be closer to 1. Anymore ideas? Is my formula correct for a calculation in miles?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-27 : 14:16:54

Not sure why you are getting what you are getting. I installed your function and tested it. For your test coordinates, it gave me the distance as 0.9869869368.

SELECT dbo.[fnGetDistance](53.3739,-2.7601,53.3687,-2.7378);

I also tested the distance between Stamford, CT and New York, NY - which gave me the result as 29.2195621384 which is approximately the straight line distance.

SELECT dbo.[fnGetDistance](40.783,73.967,41.050,73.533);

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-07-27 : 14:52:16

quote:
Originally posted by sunitabeck

You might want to compare your calculations and formula with the one described here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360

I tested F_GREAT_CIRCLE_DISTANCE with this call, and got a result of 1.588403816284186 Kilometers, which is about 1 mile.


SELECT Dist =[dbo].[F_GREAT_CIRCLE_DISTANCE]
(
53.3739 ,
-2.7601,
53.3687,
-2.7378
)

If you want to convert F_GREAT_CIRCLE_DISTANCE to miles replace this code:

-- Sets average radius of Earth in Kilometers
set @radius = 6371.0E

with this:

-- Sets average radius of Earth in Miles
set @radius = 3956.0E

CODO ERGO SUM

dmj07
Starting Member

8 Posts

Posted - 2011-07-28 : 05:03:29

ok you are all correct the function does work fine however it does not work in my current GoDaddy hosted SQL 2008 server. Has anyone had any problems like this before? Does a hosting environment or version of sql stop the calculation being correctly computed?

dmj07
Starting Member

8 Posts

Posted - 2011-07-28 : 05:51:46

Well now I have narrowed it down to the fact that I use it in a SELECT statement that it doesn't calculate correctly.

SELECT Adverts.ID, Adverts.DatePosted, dbo.fnGetDistance(@Latitude, @Longitude, Adverts.Latitude, Adverts.Longitude) AS Distance, Adverts.Latitude, Adverts.Longitude
FROM Adverts

dmj07
Starting Member

8 Posts

Posted - 2011-07-28 : 09:17:23

Ok to fix this I had to move the whole select procedure into a table value function so that it returned everything into a table and the calculation worked correctly then from within that function. Thanks for your time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources