Forum OpenACS Q&A: zip code distance matrix
I'm interested if anybody has ever used any sort of technology or
service that determines the closest location to a user defined zip
Example: User enters a zip code and the web software determines
which of several locations(based on the location's zip codes) are
closest to the user's zip code.
Do you have any experience with this at all, whether involved or
not with openacs? I'm curious how it works. Is it a black box in
which you pass two zip codes and it returns the distance? My hack
solution would be to start a table with two zip codes and a distance.
I look in the table for the two zip codes and, if they aren't in
there already, I forge a request to maps.yahoo.com and parse the
distance from there, entering that into the table. Any better ideas?
Zipdy is free software released under the terms of the GNU General Public License. Zipdy was written in C but is also available in PHP and perl. It runs with data from the US Census Gazetteer program. The original Gazetteer data is available here. The data is from the 2000 census.
The distance calculations are made by doing a great circle calculation on longitude and latitude points provided by the census data. The result is returned in standard miles.
Getting completely accurate zipcode info is not possible without paying the USPS $$$. ref-zipcodes should be complete as of the 2000 census also.
i looked at the code from zipdy and it seemed pretty easy to tweak so I made zipdyacs(which isn't acs specific so much as it's postgresql specific):
* everything is self contained in a single .sql file
* the longitude and latitude within the zip code table are now in radians, which saves an extra few steps in the mathematical formula
* everything is contained in a single stored procedure now (za_distance(zip1, zip2))
* it implements caching which will dramatically speed up some implementations in which you are constantly looking for the distance between a specific set of zip codes (IE, a few locations to various zip codes in a single geographical area)
* it no longer deals with looking for zip codes in a specific radius.
once i tweak this out a bit more, I'm going to make it available publicly(this might be never). if anybody wants it, i'll email it out on demand.
thanks for the info,
Just so it's in the historical record here, Oracle's Spatial module should be helpful, assuming you've spent the big bucks on your database. You can see details of this module for both 8.1.7 and 9i (both require a free Technet login and password).
9i appears to have improved upon 8i by no longer treating all coordinate systems as being imposed on a flat Cartesian-style plane. That is, 9i actually takes the Earth's curvature into account when doing such calculations.
The downside of Oracle Spatial is that it's part of the Enterprise Edition, not Standard. That's $40k/CPU list, instead of $15k/CPU. But...if you're already using Oracle Enterprise Edition, maybe this is your ticket. It certainly has a lot of nice searching features, judging from the docs.
Incidentally, the us_zipcodes table appears to include latitude and longitude for the zip codes. I'm not at my OACS installation right now to verify that the table got created when I installed OACS.
But the stuff's there.
btw, zipcodes should not be an integer as it has leading zeros.
also, zip codes can be stored as integers since there is no difference between the zip code 00872 and 872. It's not The Best way to store them from a textbook perspective, but from a practical perspective in regards to space and indexes it does work. Moot point if the us-zipcodes data is used.
There is no need for a new set of data.
If yes I will have monthly updated zips.