Does anyone have any recommendations for working with zip code distance

ranges? I need to calculate the distances between US zip codes.

Thanks!

Somewhere online (I don’t recall where, but I’ve used it so I know it’s

available) you can download a file with all the US zip codes and a bunch

of data on each, including the lat/long on their approx. “center”. Then

you’ll need to use a “Great Circle” formula to compute the distance

between two points. Sorry if my answer is a little vague. I’ve done

this in Perl a while back, but not recently in Ruby. Good luck!

On Tue, 11 Jul 2006, Nathan P. Verni wrote:

Does anyone have any recommendations for working with zip code distance

ranges? I need to calculate the distances between US zip codes.

If you get the right data set, you can get lat/long with the zip codes,

and it’s a simple matter of spherical geometry from there.

Nate:

This sourceforge project has the data you need.

Its in MS-Access, but you should be able to export the data to

something ruby-friendly. You should download the “ASP zipdistance”

archive. The ASP page here also has the equation you use to

calculate distance from Lat. and Long. It should be simple to port

over.

Thanks,

Tristan

You will need to purchase a list of zipcodes with their

latitude/longitude coordinates (zipinfo.com has good prices), and then

use a function to get the distance between the zipcodes in decimal

degrees and convert that into meters, miles, or whatever end

measurement you want. And because zipcodes have all sorts of

strange geographic dimension, the distance between the zip centriod

might not be what you expect.

Here is a postgresql function I use for something similar that might

help you figure out the math. Also, this is not going to be real

accurate, since the number of decimal degrees per any other

measurement (miles in this case) varies depending on where you are.

It’s probably good enough for a lot of applications though.

– function to convert miles into decimal degrees

create or replace function convertMilesToDecimalDegrees(numeric)

returns numeric as $$

begin

– $1 = radius in miles

– x = ($1 * 63360) = convert to meters (i.e. 63360 meters/mile)

– x = (x * 0.0254) = convert to inches (i.e. 0.0254 inches/meter)

– x = (x / 1852) = convert to nautical miles (i.e. 1852

meters/nautical mile)

– x = (x / 60) = convert to decimal degrees of latitude =

– (i.e. 1 nautical mile = 1 decimal minute; therefore

nautical miles/60 = decimal degrees

return (((($1 * 63360) * 0.0254) / 1852) / 60);

end;

$$ language plpgsql;

–function to buffer a geometry by a radius whose value is specified in

miles

create or replace function getBufferedGeometry(geometry,numeric)

returns geometry as $$

declare g geometry;

begin

select Buffer($1,convertMilesToDecimalDegrees($2)) into g;

return g;

end;

$$ language plpgsql;

I know I’ve downloaded them for free in the past. Now that I’ve thought

about it more, I believe it was from the US Census Bureau website

somewhere.

Ya I forgot the part about how to calculate the distance. Actually we

just use postgresql with postgis. Setup your geometry as a POINT with

an SRID of 4326, then use the Distance function to calculate the

distance between two points.

For example how to add a geometry column:

SELECT AddGeometryColumn( ‘addresses_geom’, ‘geom’, 4326, ‘POINT’, 2);

Update a database row to add lat/long:

UPDATE addresses_geom set geom = GeomFromText(‘POINT(47.651443

-117.411025)’, 4326) where id = 9

And the distance query. Calculates the distance from the geom to the

specified coordinates where id = 9.

SELECT Distance(geom,GeometryFromText(‘POINT(44.422 -123.876)’,4326))

AS distance from addresses_geom where id = 9

To get the distance between two points stored in the database:

SELECT Distance(geom,(SELECT geom from addresses_geom where id = 13))

AS distance from addresses_geom where id = 9;

http://civicspacelabs.org/home/zipcodedb

Thats what I use. Its a little dated and missing some zip codes.

Maybe the holes can be filled in using the census data. It has a lot

of cool stuff. Specifically it has timezone info which is very

useful.

On 7/11/06, Jonathan M. [email protected] wrote:

Rails mailing list

[email protected]

http://lists.rubyonrails.org/mailman/listinfo/rails

–

Elliott C.

[email protected]

[email protected]

On Tue, 11 Jul 2006, Elliott C. wrote:

http://civicspacelabs.org/home/zipcodedb

Thats what I use. Its a little dated and missing some zip codes.

Maybe the holes can be filled in using the census data. It has a lot

of cool stuff. Specifically it has timezone info which is very

useful.

Thanks Elliott – that’s the one I use and find most useful.

Nathan P. Verni wrote:

Does anyone have any recommendations for working with zip code distance

ranges? I need to calculate the distances between US zip codes.

Another option is Zipdy, which is free. I do not know how current the

data is, but it seems to work pretty well. Can’t compre it to zipcodedb,

but I’ve had good luck with it, and it gets its data from the same

source.

I have a DB with ZipCodes and their Lat/long.

What I hear is that basically, we need to compute distance between all

points (~70K) calculations before returning a result. Isn’t there a

better way to reduce the set of calculations to determine say, zipcodes

< 50 miles from x.

Rajat

Thanks for posting that. I didn’t even know it existed.

On Feb 2, 10:11 pm, Gregory S. [email protected]

wrote:

On Sun, Feb 03, 2008 at 02:57:42AM +0100, Rajat G. wrote:

I have a DB with ZipCodes and their Lat/long.

What I hear is that basically, we need to compute distance between all

points (~70K) calculations before returning a result. Isn’t there a

better way to reduce the set of calculations to determine say, zipcodes

< 50 miles from x.Precompute it, rather than trying to compute it on the fly. Disk is cheap,

indexes are good.

For 25 million pairs of zip codes? Disk is cheap, but that seems a

bit extreme.

As an alternative, you could index the zip codes by degrees of

latitude and longitude. Then to find zip codes within 50 miles of a

given zip code, you could simply retrieve all the zip codes within one

degree of latitude or longitude.

A better idea: index all the zip codes by latitude and longitude to

two decimal places. Then if your database has trigonometric functions

you can get a result with a single database query, and no ruby code.

There is a great tutorial with CODE to calculate both distance between

zip codes and zip codes that fall within a specified range.

On Sun, Feb 03, 2008 at 02:57:42AM +0100, Rajat G. wrote:

I have a DB with ZipCodes and their Lat/long.

What I hear is that basically, we need to compute distance between all

points (~70K) calculations before returning a result. Isn’t there a

better way to reduce the set of calculations to determine say, zipcodes

< 50 miles from x.

Precompute it, rather than trying to compute it on the fly. Disk is

cheap,

indexes are good.

Rajat

–Greg