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