Search for geocoded information by distance

Question

I have an address database, all geocoded.

What is the best way to find all addresses in our database within a specific radius of a given lat, lng value?

In other words, the user enters a (lat, lng) location, and we return all records from our database that are within 10, 20, 50 ... etc. miles from a given location.

It does not have to be very accurate.

I am using MySQL DB as the back end.

Answer 1

There are spatial extensions available for MySQL 5 - the documentation entry page is here:

http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html

, , , , .

- , . :

www.movable-type.co.uk/scripts/latlong.html

, .

Answer 2

, SQL Server 2008 ,

zipcodes 20 zipcode 10028

SELECT h.*
FROM zipcodes g
JOIN zipcodes h ON g.zipcode <> h.zipcode
AND g.zipcode = '10028'
AND h.zipcode <> '10028'
WHERE g.GeogCol1.STDistance(h.GeogCol1)<=(20 * 1609.344)

. SQL Server 2008

SQL Server 2000 : S QL- Zipcode

Answer 3

.

1 > db , sql2008, oracle, ESRI postgis - db-, . 2 > , , - Algo, . , , .

Answer 4

, , , . PostgreSQL, Oracle MS SQL , .

, , , . , lat-long → distance ( ), .

Answer 5

, - , . Google , - . , , , , , .

Answer 6

If this is not very important, and I assume that you have an x ​​and y column in your table, just select all the rows in the large bounding box and use pythagorus (or Haversine) to crop leads to corners.

eg. select * from the places where (x between xpos-10 miles and xpos + 10miles) and (y between xpos -10miles and ypos + 10miles).

Remember that pythagorus is sqrt (x_dist ^ 2 + y_dist ^ 2).

Its quick and simple, easy to understand and does not need funny connections.