Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks man, I will go with the send method, seems neat and easy. –  Heretic Monk Apr 11 '13 at 8:22
    
Can you please explain more on the first method and point me to an example or something which can give me a head start? –  Heretic Monk Apr 11 '13 at 8:23
    
No problems - I updated the answer to include some links –  acutesoftware Apr 11 '13 at 13:23
    
I am planning a Address to Latitude, longitude thing now. Wherin a user inputs his address and in the backend I convert the same into Lat and Lon, Using Google Geocoder, but the only problem now is that how do I restrict this search according the borders of a country. Thanks a lot for your help mate :) –  Heretic Monk Apr 12 '13 at 9:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.