Finding an exact or best match

[ubh]

Building a store finder and I have 3 major columns to search from in my database.


Country.
State.
Zip/Postal Code.


I can successfully narrow down both country and state, however I don't always want to find an exact zip code but rather if the exact zip code exists great, if not find the next best one available?

How would a person go about finding an exact or next best zip code?

Thanks

[Fumigator]

Are you asking how to do a spatial search based on zip code? That's a can o' worms... Try these Google searches to research the beast:

"spatial search" +"zip code" (found this nugget) (http://209.85.173.132/search?q=cache:iVoZEAZR-QIJ:assets.en.oreilly.com/1/event/2/Geo%2520Distance%2520Search%2520with%2520MySQL%2520Presentation.ppt+%22spatial+search%22+%2B%22zip+c ode%22&hl=en&ct=clnk&cd=1&gl=us)

"locator software" (a starting point) (http://en.wikipedia.org/wiki/Locator_software)

[ubh]

Hum well I would of thought it was easier. I mean if I have a database filled with all our stores already and their zip/postal codes wouldn't it just be something along the lines of.

Actual zip/postal code:
97266

Trimed Zip/Postal Code for best match:
9726

Some way of telling MySQL to find all rows with at least these first 4 zip/postal code numbers or the exact 97266??.

[Fumigator]

What about a store on the Kansas side of Kansas City (zip codes of 661xx)? You'll never see that if you enter the zip code for the Missouri side of Kansas City (zip codes of 64xxx). And there are hundreds of situations like this. Nope... this 'aint an easy thing to pull off. To be accurate you have to use the latitude and longitude of the zip codes and use a spatial search.