Searching by Zip Code proximity - MySql

Question

I'm having some trouble getting a search by zip code proximity query working. I've search and searched google but everything I find is either way too slow or I can't get working. Here's the issue:

I have a database with a table with all the US Zip Codes (~70,500 of them), and I have a table of several thousand stores (~10,000+), which includes their zip code. I need to be able to provide a zip code and return a list of the closest stores to that zip code, sorted by distance.

Can anyone point me to a good resource for this that they have used and can handle this much load, or share a query they've used that works and is fairly quick about it? It would be MUCH appreciated. Thanks!

Answer 1

You should build a table that has each zip code with an associated latitude and longitude. When someone enters a zip and a distance, you calculate the range of latitudes and longitudes that fall within it, and then select all the zip codes that fall within that bounding box. Then you select any stores that have zip codes within that set, and calculate their distance from the provided zip and sort by it. (Use the haversine formula for calculating the distance between points on a globe)

If speed is your main concern, you might want to precompute all the distances. Have a table that contains a store zip code column, the other zip code, and a distance column. You can restrict the other zip codes to zip codes within a certain distance (say 100 miles, or what have you) if you need to cut down on rows. If you don't restrict the links based on distance, you'll have a table with > 700 million rows, but you could certainly do fast lookups.