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.

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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.

https://developers.google.com/maps/articles/phpsqlsearch_v3#findnearsql

A bit of re-factoring to suit MS SQL and it works.

So thanks.

ASKER

Link was missing.

Here it is

https://developers.google.com/maps/articles/phpsqlsearch_v3#findnearsql

Here it is

https://developers.google.com/maps/articles/phpsqlsearch_v3#findnearsql

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.

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