Calculating Distance in Miles via SQL Query
I have been working diligently on a new app which has a need to use location to find people in a specified radius. My data store already had the coordinates for these users in decimal degree radians (I. e. latitude = 25.762098 and longitude = 80.189117) BTW you can use Bing maps to get these coordinates for test purposes. What I needed to be able to do was to query my SQL database using a standard SQL query and get back a set of people who were nearby. In my research to find this there are a bunch of Math formulas out there and it seemed there are 4 very specific ones which can be used to calculate the distance. I found this blog article useful in determining which formula to use. I landed on the Modified Pythagorus’ distance formula as it was accurate enough for what I needed.
The next step was to get the SQL Query for which I could apply my newly found calculation. After speaking with a very good friend of mine who is a SQL genius he helped me put the following query together:
SELECT * FROM myTable WHERE (POWER ((POWER( (69.1 * ( Longitude - @longitude ) * cos(@latitude/57.3)) , 2 ) + POWER( (69.1 * ( Latitude - @latitude )) , 2 )), .5)) < ( @radiusInMiles )
@longitude, @latitude, @radiusInMiles are all parameters you can pass to the stored procedure to keep the query dynamic.