a MySQL Table of Zip Code Latitude/Longitude Coordinates

I was working on adding location awareness to GigBayes today and ended up making a table of zip codes and their coordinates. I figured it would be nice to share it with the world:

So here is a pre-made MySQL table containing US zip codes and their latitude and longitude (.zip 820K)

I got the data from here, and then deleted any zip codes which where missing coordinates.

You can also create this MySQL function to use for finding the distance between locations:

CREATE FUNCTION `earth_distance_miles`(p1 point, p2 point) RETURNS int(11)
RETURN
((ACOS(SIN(x(p1) * PI() / 180) * SIN(x(p2) * PI() / 180) + COS(x(p1) *
PI() / 180) * COS(x(p2) * PI() / 180) * COS((y(p1) - y(p2)) * PI() /
180)) * 180 / PI()) * 60 * 1.1515)

The formula comes from here.

You’d use the function like this:

SELECT earth_distance_miles(
(
SELECT location
FROM zip_code
WHERE zip = '06902'
), (
SELECT location
FROM zip_code
WHERE zip = '20905'
)
);

Which gives us 227 miles. A very reasonable answer.

Posted by Greg Pinero (Primary Searcher) on Nov 1st, 2008 and is filed under Web Services.

Both comments and pings are currently closed.

Add this post to Del.icio.us - Digg

Comments are closed.