Display in range addresses within x miles of a geo location with google maps

Question

In my database I have a list of places and for each I have a street name and number, postcode, city and county. Some of them have a latitude and longitude location.

And I have the geo location of the city centre for example. I would like to display only the places that are within X miles of the city centre on a google map.

Incase this would need a geo location for each of my places to work, I could perhaps set up a script to use google maps api to use geocoding to get a geo location for all my places and update the database with the lat/lng. Then I would have a database full of lat and long locations to work from.

Once all the places have a lat/lng then maybe mysql can return the within range addresses?

Answer 1

This is not hard once you have lat / long data, and if somebody gives you the great circle distance formula in mySQL format.

@maggie gave a good reference. http://stackoverflow.com/questions/3922404/how-to-efficiently-find-the-closest-locations-nearby-a-given-location

Indexing strategy: Keep in mind that one minute of latitude (1/60 degree) is one nautical mile, or 1.1515 statute miles (approximately) all over the world. So index your latitude column and do your search like this. (If you're in the part of the world that uses km, you can convert; sorry for the Old-British-Empire-Centric answer, but they did define the nautical mile.)

WHERE mylat BETWEEN column.lat-(myradius*1.1515) AND column.lat+(myradius*1.1515)
  AND (the big distance formula) <= myradius

This will give you both decent data base indexing AND reasonably accurate distance circles.

One extra refinement: You can index longitude too. The trouble is that ground distance isn't directly related to longitude. At the equator it is one nautical mile per minute, but it gets smaller, and at the poles there are singularities. So, you can add another term to your WHERE. It gives correct results but isn't as selective as latitude indexing. But it still helps the indexing lookup, especially if you have lots of rows to sift through. So you get:

WHERE mylat BETWEEN column.lat-(myradius*1.1515) AND column.lat+(myradius*1.1515)
  AND mylon BETWEEN column.lon-(myradius*1.1515) AND column.lon+(myradius*1.1515)
  AND (the big distance formula) < myradius