I'm having problems with my query below. Basically I need to calculate the feet between two gps coordinates.

Basically once I can get this formula completed instead of manually entering the data I'll use a temp table and then select the top 2 rows and then delete these and keep looping through until everything has been calculated but this gps thing is driving me nutz!

Please help.

declare @pi     int 
    declare @D2R    int 
    declare @dLon   int
    declare @x      int
    declare @y      int
    declare @lat1   int
    declare @lat2   int
	declare @lon1	int	
	declare @lon2	int

set @lat2 = 28.089375
set @lat1 = 28.0892416666667
set @lon1 = -81.9935083333333
set @lon2 = -81.9935933333333

set @pi = 3.14159265358979
set @D2R = @pi / 180

   set @lat1 = @D2R * @lat1
   set @lat2 = @D2R * @lat2
   set @dLon = @D2R * (@lon2 - @lon1) 

 set   @x = Sin(@lat1) * Sin(@lat2) + Cos(@lat1) * Cos(@lat2) * Cos(@dLon)
 set   @y = Sqrt((Cos(@lat2) * Sin(@dLon)) ^ 2 + (Cos(@lat1) * Sin(@lat2) - Sin(@lat1) * Cos(@lat2) * Cos(@dLon)) ^ 2)
--set	@go = atan(@x * @y)
print @x
print @y
print @go
6 Years
Discussion Span
Last Post by jjc_Mtl

Are you using SQL2008? You should check out the "geography" datatype. It's a CLR Assembly datatype with all kinds of .NET methods you can use (specifically the .STLength method to calculate the distance between two geo points). There's a good write-up in SQL2008 BOL. Helps you get away from all the trig.

From BOL:

This type represents data in a round-earth coordinate system. The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.

Edited by BitBlt: n/a


No, Im using 2005 so I dont have any of those built in functions unfortunately


Could it possibly be that you are assigning FLOAT values to INTEGER variables? Just asking...

Also, it appears you are trying to use the haversine formula, correct? Did you code this from the actual formula, or did you use someone else's code? The reason I ask is that I'm not sure you got the formula right...

Here's a link to a page that explains several different methods, with the actual math formula and some code (non-SQL) examples:
Hope this helps!

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.