Link to home
Start Free TrialLog in
Avatar of Phil Catterall
Phil Catterall

asked on

Search for addresses within a given distance from my location

Here's an interesting one. I have address records which have a latitude and longitude - lets say a simple table with:
AddressId INT
Latitude decimal(10,6)
Longitude decimal(10,6)

I know my current position expressed also in degrees latitude and longitude (lets call them variables @Lat and @Long.

So can anyone assist me with some SQL to bring back those Id's which fall within a certain distance. Ideally records within a radius (r) of a circle where I am at the center, or a square at which I am the center if that is easier.
I did find a solution which uses a .Net CLR but I am restricted and not able to use a CLR.
ASKER CERTIFIED SOLUTION
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The Haversine formula/method can give you the distance, and it can be done in SQL Server, but it consists of fairly intensive mathematical calcs.

You might be better off precomputing all (relevant) distances in a separate table, on which you could do a quick lookup.
Avatar of Phil Catterall
Phil Catterall

ASKER

The google article link was missing, but I re-googled it (Is that recursive googling I wonder ?) and found it.
https://developers.google.com/maps/articles/phpsqlsearch_v3#findnearsql
A bit of re-factoring to suit MS SQL and it works.
So thanks.
Sorry, thought I included it.  Thanks for the points!  'recursive googling'  I like that and I'm pretty sure I did that trying to find that link again myself.  The one I forgot to post...  I did bookmark it this time.