Display range addresses within x miles of geographic location with google maps

Question

I have a list of places in my database, and for each I have a street name and number, zip code, city and county. Some of them are located in latitude and longitude.

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

To do this, you will need geolocation for each of my places of work, I could set up a script to use google maps api to use geocoding to get the geographic location for all my places and update the database using Lat / LNG. Then I will have a database full of lat and long places to work.

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

Answer 1

It is not difficult if you have lat / long data, and if someone gives you the big circle distance formula in mySQL format.

@maggie gave a good link. How to efficiently find nearby locations near this place

Indexing strategy. Keep in mind that one minute of latitude (1/60 degree) is one nautical mile or 1.1515 miles (approximately) worldwide. Therefore, index your latitude column and search in this way. (If you are in that part of the world that uses a kilometer, you can convert, sorry for the answer “Old-British Empire Center”, but they determined 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 indexing of the database and fairly accurate distance circles.

One additional clarification: you can also index longitude. The problem is that the distance between the earth is not directly related to longitude. At the equator, this is one nautical mile per minute, but it is becoming smaller, and the poles have features. So you can add another term for your WHERE. It gives the correct results, but not as selective as latitude indexing. But it still helps index the search, especially if you have a lot of rows to sift. 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 

Answer 2

Most likely you want to use a spatial fill curve or spatial index to reduce your two-dimensional problem to a 1D problem. For example, you can combine a lat / long pair with a z curve or a Hilbert curve. I use a hilbert curve for myself to search for zip codes. You can find my solution at phpclasses.org (hilbert-curve).