GPS Feet Difference

[moone009]

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

[BitBlt]

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.

[moone009]

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

[BitBlt]

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:
http://www.movable-type.co.uk/scripts/latlong.html
Hope this helps!

[jjc_Mtl]

Latitudes and longitude MUST be Double or Float
jjc

[jjc_Mtl]

Of course
Pi as well
jjc