Latitude / Logitude SQL Query?

Question

Queries are not my strong point and I hate to ask but I would like to see if anyone here could write the basic code of the syntax for me. I have a single table that has a list of US geographic locations by LAT and LONG and I need a query to return all locations within 100 miles.

txtLong,txtLat,txtCity,txtState
-92.35781,36.329026,Mountain Home,AR
-98.69707,36.804875,Alva,OK
-98.28297,36.949448,Amorita,OK
-124.35756,42.813437,Sixes,OR
-123.01769,44.061057,Springfield,OR
-75.32755,39.890462,Folsom,PA
-75.20374,40.13477,Fort Washington,PA
-96.54327,31.752538,Tehuacana,TX
-98.10754,29.699844,Canyon Lake,TX
-98.17653,29.749204,Canyon Lake,TX
-97.60044,29.269305,Smiley,TX
-81.50071,37.677563,Sabine,WV
-81.21634,37.964622,Scarbro,WV
-80.86194,38.653616,Clem,WV
-80.79366,38.704457,Gassaway,WV
-90.70102,46.273618,Mellen,WI
-90.05754,46.183572,Mercer,WI
-91.81795,46.233591,Gordon,WI

I’m writing a logistics app for our internal users and this is the final component I need

Its messy but after a number of reseach, I found this derived and make this inline forumila:

((ACOS(((SIN(Latitude1*3.14159265358979 / 180)*SIN(Latitude2*3.14159265358979 / 180))+(COS(Latitude1*3.14159265358979 / 180)* COS(Latitude2*3.14159265358979 / 180))) *COS((Logitude1-Logitude2)*3.14159265358979 / 180)) * 180 / 3.14159265358979) * 60 * 1.1515)

Answer 1

This seems to be what you want

Answer 2

Well, from a given lattitude / longitude coordinate pair, you get a circle as your shape for all points within 100 miles. I'm afraid SQL Server is not capable of doing this, not that I'm aware of. SQL likes absolute queries, not fuzzy ones, or ones based on geometric definitions.

You could do it brute force and check if a given point is contained within the circle. To do this, calculate the distance from the input point to the desired point using pythagoras' theorem:

if (sqrt(InputPoint.Lattitude * MountainHome.Lattitude + InputPoint.Longitude * MountainHome.Longitude) < distance_to_check)
{
// point is within circle
}

For a large number of points, this is going to be terribly slow, so I recommend not taking the square root and instead squaring your distance to check (so instead of taking sqrt, you just square 100, this balances the equation and avoids the expensive square root for each point and substitutes it with a one time multiplication).

Answer 3

Check the google maps api, there may be a function there already.

http://www.google.com/apis/maps/