Selecting store locations based on ZIP Code

I have a customer that owns 90 stores.

He will be providing me with a mailing list on a monthly basis.

For each record in the mailing list, I need to determine the CLOSEST store to that address.

I'm sure there are a billion zip codes across the U.S., so writing a rule that matches the zip code is not practical.

I'm thinking I need some type of solution that will calculate the distance between the mailing address zip code and the store zip codes, and select the one which is closest.

Any ideas would be greatly appreciated!

Eric,

To perform this type of lookup, I believe you need to do the following:

1) Associate latitude and longitude to all ZIPs you are working with

2) Calculate the distance between the lat/long for the given record against the store ZIP lat/long

To get the lat/long for ZIPs, I'm thinking there's probably a free database of this information out there but I wasn't able to find one. I did find a fairly cheap database of this info, however, here: http://www.zipcodedownload.com/Products/Product/Z5Premium/Standard/Overview/

As far as calculating the distance between the different lat/long values, this requires some math that can calculate the distance between 2 points on a sphere (the earth in this case). I ran across this script in JS which does this: http://www.zipcodeworld.com/samples/distance.js.html

If you would like this logic in alternate languages, you can find that here:

http://www.zipcodeworld.com/developers.htm

This calculation process can be a bit intensive, so you might want to perform this on the data before you work with it in FusionPro. The end result of that calculation could be something like a new field in the customer data file called "closest store" that lists the unique ID of the record in the store data file which is the closest.

hth.

Looks promising (and very cool!)

I will meet with my programmer over the weekend and see if he can work on this for me.

Thank you Mark!