Zip Code - Stumped (revised)

[fishbone]

Ok, so the other one was too compressed and nobody could open it. basically, I have a file that needs all the work done to it. Why? 1.) because i can't figure it out. 2.) Because it would be a good tool for people to have.

I have a zip file with an excel sheet that has the formula for finding a "straight-line, shortest distance" between two zip codes based on latitude and longitude coordinates. It's in the range of yahoo maps and google maps, except they have sophisticated (dont ask me how) driving routes involved in their equations.

Essentially, i think this serves as a good tool not for distance between two zipcodes, but zipcodes within a certain radius of 5,10,25,50,100, etc.. The expansion of havinig X number of products, restaruants, within a radius of a zipcode could easily be added on.

I am lost. in the link below is the equation for zipcodes within a radius, but i didnt even get that far, and in the other link is to a partial FM File that is too large to put on this site. Please take a look and see what you can do.

If you are too busy, it's ok. I sell advertising for a living a just do this for fun anyhow.

Jeff

Radius link:

http://whatis.techtarget.com/tip/1,289483,sid13_gci523000,00.html

FM File and excel file with distance betweeen zips codes:

http://www.dropload.com/redeem.php?t=1840e42564116b53637f2957973c6d97

Jeff

Edited October 6, 2006 by Guest

[ino]

Zip File doesn't correct :

Edited October 5, 2006 by Guest

[fishbone]

:? How is it supposed to correct, if you put it an invalid zip code it comes up "no match"

[ino]

Look Att

[fishbone]

Ok, Sorry. I had to use the maximum compression allowed in order to get it to fit the forum file size prerequisites. When i get home i'll put it on a file hosting site for every one to view. But first i have to make changes because i found some good information on finding the distance between zips based on latitudes and longitudes. stay tuned until tonight!

[BobWeaver]

Jeff,

Dropload only allows the file to be downloaded once. Can you attach a stripped down version of your file (ie, delete all but a few sample records of the data) to this forum so that we can all have a look at it? Thanks.

[fishbone]

Ok, There are 40,000 + zip codes in the US, virgin islands, puerto rico, guam, etc. I deleted all but 200 and re attached this zip file. Please note that the attached excel document has the formula for finding distance between two zip codes which needs to be converted into FM. I dont know how to play around with the trig and math functions too much in FM, only Excel which does me no good. Also, in the Fm file, look at the zip_data layout for zip code info.

zipsradiuscalc.zip

[BobWeaver]

I didn't look at the formula in the excel file, because I remembered a previous discussion about this same subject here:

http://fmforums.com/forum/showtopic.php?tid/66958

There is a sample file posted there with the formula to calculate the distance between two locations with specified latitude/longitude. I added this to your sample file, and added the appropriate relationships to look up the lat/long based on zip codes. It is attached here.

zips_beta2.fp7.zip

[comment]

Calculating the distance between two locations is quite easy. Finding locations within a given distance from a given location is not. To determine if location x is within the given distance or not, you need to perform the actual calculation, using data from BOTH locations - IOW, for each given location/distance you need to loop between all records and check them one by one. With 40k records, that's going to take some time.

It is possible to shorten the process, if one is willing to sacrifice accuracy and settle for finding locations within a SQUARE (well, almost square), instead of a CIRCLE. This was discussed here.

[ino]

Maybe can help

http://www.fmforums.com/forum/showtopic.php?tid/180914/

Ino :