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.
Microsoft SQL Server

Avatar of undefined
Last Comment
Dave Baldwin

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Dave Baldwin

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scott Pletcher

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.
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.
Phil Catterall

ASKER
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Dave Baldwin

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.