Share via


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. 

 

Happy locating!