Forum OpenACS Q&A: zip code distance matrix

Posted by james shannon on 01/23/02 09:56 PM

Hi...
I'm interested if anybody has ever used any sort of technology or
service that determines the closest location to a user defined zip
code.
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?

Thanks

--js

2: Response to zip code distance matrix (response to 1)

Posted by Bart Teeuwisse on 01/23/02 10:08 PM

Take a look at Zipdy (http://www.cryptnet.net/fsp/zipdy/). Zipdy is a program for calculating the distance between two zip codes and finding all the records in a RDBMS with a zip code with x miles of another zip code. Currently, RDBMS support exists for postgreSQL.

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.

3: Response to zip code distance matrix (response to 1)

Posted by Jon Griffin on 01/23/02 10:23 PM

acs-reference has the zipcode data in it with lat/longitude.<p>
Getting completely accurate zipcode info is not possible without paying the USPS $$$. ref-zipcodes should be complete as of the 2000 census also.

4: Response to zip code distance matrix (response to 1)

Posted by Gilbert Wong on 01/24/02 01:07 AM

A while back, I wrote some code to calculate the distance between two zip codes (latitude and longitude coordinates from the list that Jon mentions). I can dig it up and post it. I just used a distance calculation formula which I got off of a website. I think the longitude and latitude coordinates are at the center of the zip code area so it will only be an approximate calculation.

5: Response to zip code distance matrix (response to 1)

Posted by james shannon on 01/24/02 04:04 AM

thanks everybody...

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,
james shannon

6: Response to zip code distance matrix (response to 1)

Posted by Daryl Biberdorf on 01/24/02 05:59 AM

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.

7: Response to zip code distance matrix (response to 1)

Posted by Jon Griffin on 01/24/02 06:42 AM

us_zipcodes definitly includes lat/long. The new package should really be written on top of that, instead of yet another zipcode database from the same source.

8: Response to zip code distance matrix (response to 1)

Posted by Michael A. Cleverly on 02/01/02 10:11 PM

The thread on web/db (http://www.arsdigita.com/bboard/q-and-a-fetch-msg?msg_id=0006pD) from March 2000 addresses computing distances between zipcodes (using SQL), sources of zipcode lat/longitude info, etc.

9: Response to zip code distance matrix (response to 1)

Posted by Don Baccus on 02/01/02 10:19 PM

The zipcodes data and other reference data aren't installed by default, since they're only needed by some packages.

But the stuff's there.

10: Response to zip code distance matrix (response to 1)

Posted by David Ornelas on 02/11/02 02:35 PM

PostgreSQL has a function in /contrib called earthdistance. Using this along with a contributed procedure in the PostgreSQL cookbook http://techdocs.postgresql.org, you can pretty much do an sql query looking for the closest n providers/vendors/whatever to the given zipcode. HTH

11: Response to zip code distance matrix (response to 1)

Posted by Brad Duell on 10/09/02 12:37 AM

From what James had, I've also implemented this in Oracle. I uploaded this into the new-file-storage at:

https://openacs.org/new-file-storage/one-file.tcl?file_id=450

Thanks James.

12: Response to zip code distance matrix (response to 1)

Posted by Jon Griffin on 10/09/02 12:44 AM

Why are you adding yet another set of zipcodes? As has been posted us-zipcodes has all that information or am I missing something? Lat and Lon are already there.

btw, zipcodes should not be an integer as it has leading zeros.

13: Response to zip code distance matrix (response to 1)

Posted by james shannon on 10/09/02 03:21 AM

without seeing us-zipcodes, i am under the impression that it only includes the lat/lon and does not provide any sort of calculations, which zipdyacs does. if this is the case, then zipdyacs should be modified to use the data in that table instead of keeping it's own lat/lon info.

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.

james

14: Response to zip code distance matrix (response to 1)

Posted by Jon Griffin on 10/09/02 04:44 AM

us-zipcodes does include lat/lon and all that is needed is to write some (db agnostic) functions or even tcl procs to do the calcs.

There is no need for a new set of data.

15: Response to zip code distance matrix (response to 1)

Posted by Brad Duell on 10/09/02 05:20 PM

I didn't see the lats/lons for us_zipcodes when putting this together (well, the actual server I needed this for isn't openacs anyway), but you're right, no reason to duplicate the data. I made the change to the file.

16: Response to zip code distance matrix (response to 1)

Posted by Jon Griffin on 10/09/02 05:58 PM

One other advantage, I am going to update the zipcodes when I figure out if I spend the money (buying from USPS), can I then distribute. I am still waiting on the USPS for an answer.

If yes I will have monthly updated zips.