I'm the webmaster for a nationwide BMW motorcycle club (BMWRA). Every year we host a rally and awards are given out for, among other things, those who rode the farthest to attend. Over the years different clubs have used different methods to calculate this, but we wanted to process the registration and awards information online as much as possible, rather than with pencil and paper, so I used the ZIP code in the attendee record to calculate the distance for everyone.
It worked. Of course it's pretty simple, so that wasn't a surprise. I wrote a query which stepped through the attendees with ZIP codes and for each one I queried the ZIP code database for the lat/long and then ran the function provided using their lat long and the one where the rally was held. A minor tweak would be to complete some of the math in the function using the rally lat/long, but I didn't. I think the bulk of the query time was used in querying the ZIP code database for the lat/long of the attendee.
When I listed the people with the distance they traveled, I printed the town/state they entered as well as the town/state that the ZIP code database had. That was just to weed out any errors; they all matched exactly in this case although a mismatch wouldn't be unexpected where you have small suburbs clustered together.
I had about twenty records with ZIP codes that weren't in the DB. I entered them in the DB using the lat/long from Google Maps (described in a different thread). Probably not the same method that the census bureau used but accurate enough for us.
For next year I think we'll use the same database to populate the entry form with city and state.
The only real shortcoming for our purpose is that it doesn't include Canada, and we have quite a few Canadians attending out rally. Unfortunately there are over 750,000 Canadian postal codes, which is basically an unmanageable amount of data. I created DB records for a few of them but I'd like a better way to deal with them short of creating a 750,000 record table.
Although there are actually about 850,000 postal codes, for most purposes the first three characters (the FSA, or Forward Sortation Area) will be enough, and there are only about 1500 of those. I looked and looked but I cannot find a free DB of FSAs. Canada Post does have a PDF which maps them all out, but it's an actual map - no Cartesian coordinates.
One sloppy option might be to average the postal code lat/longs from every FSA and use the average. www.populardata.com has a list of about 750,000 of the 850,000 postal codes.
Log in to post a comment.