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

4
votes

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?

2
phpjavascriptmysqlgoogle-maps
you talking about IP addresses? – Etienne Marais
stackoverflow.com/questions/3922404/… – teemitzitrone
@etbal I was talking about street addresses. – benjovanic
@maggie Thanks that is exactly what I needed – benjovanic

2 Answers

2
votes

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. 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
0
votes

Most likely you want to use a space-filling-curve or a spatial index to reduce your 2D problem to a 1D problem. For example you can combine the lat/long pair with a z-curve or a hilbert curve. I use for myself a hilbert curve to search for postcodes. You can find my solution at phpclasses.org ( hilbert-curve ).