Zip based search in mysql.

Question

I want to implement a zip based search in mysql database, I am populating the location column in my database based on a drop-down where in I have predefined zip codes, now if a user wants to search for a commodity which is in the radius of 75 miles of his present location, I want ideas as how and which is the best way to accomplish this. I am new to mysql so a detailed answer would be much appreciated. I would be much glad if I am given some example too.

Answer 1

There are a couple of ways you could do this

a. Geocode your postcodes and calculate the distance between the 2 points (or use an existing API to do this). There are several places you can geocode small quantities of addresses for free, or you can purchase databases. Note that this method is not entirely trivial, but is probably the best for the long term.

You can get Geocoding done via Google at https://developers.google.com/maps/documentation/geocoding/

Then, to calculate the distance, use https://developers.google.com/maps/documentation/distancematrix/

b. Manually set which zip codes are bordering other zip codes in the database. You say you are supplying predefined zip codes so this may be a simpler solution (though for a large number of zip codes it would be costly) The idea is that you have a new table zip_neighbours as follows

 Zip code,     neighbour
  100,       101, 102, 103
  101,       100, 102, 104
  102,       100, 101, 104

This wont give an accurate '75 miles' boundary, but can be used as a pretty good indication. When a user wants to include nearby zip codes you lookup the neighbours in this table and include them in the search.