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!

``````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``````

## All 5 Replies

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.

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

Latitudes and longitude MUST be Double or Float
jjc

Of course
Pi as well
jjc

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.