Zip Code search

Anon

I got a free zip code file from the US gov. It is from the 1990 cencus. I want to offer a search on my site quite abit wider than most zipcodes, say 30 miles from the zip. All the searches would be in the US, and the file has the longitude and latitude for each zipcode. This looks like it should work for me. I am thinking I can query the database with a zip get its long. and lat. and then do some arithmatic with the zip using php3 or javascript, then make another query for all the zips within those coordinates. There are over 38,000 rows in this table.
Ok am I on the right track with this? Should I shop for a better product?

Thanks in advance,
Jim

Anon

Oh yeah forgot to mention I am using MySQL. Also was wondering if MySQL might be able to do the calculations with the longitude and latitude and then supply all those zips that fit.
Again thanks,
Jim

Anon

Where did you get this file? Does it name the cities, too?

I know that there are equations for finding zip codes within a radius of each other, using the long and lat, but I don't know where to find these equations. Sorry.

---JH

Anon

Thanks John
Yeah I found the link in this forum...
Kirk Parker wrote on
2000-09-25 14:19:33
Subject: RE: Zip Code Code
See:

places.html

This has the city names as well, is really nice but like Kirk points out it is from the 1990 cencus, but for my needs that should be fine as long as I can come up with a way to get surrounding cites.

Jim

Anon

Please don't neglect to mention I pointed out the other limitations
of this file, or at least called everyone's attentions to the faq's that
the census bureau itself points out. Briefly, they are using the zipcode
in a way (to determine geographical proximity) that is completely
unrelated to the post office's purpose for developing the zip codes in
the first place, and while the census bureau's proximity information
is certainly useful, it is also approximate and there are bound to be
failures.

Anon

Sorry Kirk to have you point that out a second time. You are right there are some limitations there, and a very good thing to know up front. My mistake.
Jim

Anon

Does mysql have geometric functions, like postgresql does? If so, then it should be able to do the math for you. If not, you might wanna look at switching to a database with the ability to do geometry/trig/x/y coord systems and such.

I've been thinking of re-programming my towns crappy bus schedules under postgres's functions and building a few best route functions to go with it.

Anon

I've been thinking of re-programming my towns crappy bus schedules
under postgres's functions and building a few best route functions to
go with it.

You're gonna have to have more GIS information than just the
zip code, aren't you?

Anyway this sounds like a wonderful project; any chance you
will be open-source releasing it if you do it?

Anon

Yes, MySQL supports trig functions. Or you could just do it in PHP after pulling the data out...

---JH

Anon

True, but if you're gonna do a select and want a list of the closest zip codes or something, it's MUCH more efficient to have your database do the math for you. If mysql can tell you the distance and angle of two points in the database, it makes life MUCH easier than writing all that stuff in PHP land.

Anon

That's something I've been curious about lately. Is it faster to have the database do the work, or PHP, and why? I'm talking things like math, date, and string functions, where both the database and PHP have similar functions...

I'm going to write a test script when I get a chance, I'll share the results if anyone is interested...

---JH

Anon

I implemented something like this a while back using PHP/Oracle 8i and have been thinking of porting it to PHP/MySQL and creating an open source project for some time.

The difference in my project was that I did not use zip codes because I had to do distance calculations for every city in the entire world (about 2.5 million if you're curious). I don't know the exact source of the data for the non-US cities because my employer had it on a cd, but I obtained the US city information from the U.S. Geological Survey (www.usgs.gov). You can find the files at:
http://mapping.usgs.gov/www/gnis/gnisftp.html

The file that will probably be of most interest is "Pop_places_deci.gz" as this only has cities whereas the other data has every single geographic feature they have mapped (and not too many people are trying to find out how far they are from something like "rock 652"). The only drawback is that this data doesn't have zip codes and I haven't come across a convenient way to merge zip code data with the USGS data. If anyone has suggestions on that I'd like to hear them.

In addition, if you'd like to save yourself the trouble of downloading and parsing through the USGS data sets to build your database I have a MySQL data dump of a city/state/country table schema with all the data from the "Pop_places_deci.gz". I believe I also converted the data into XML format, too. I can make both of these available if there is interest. Also, I already have a number of functions that interact with this data for distance calculations, soundex matches, etc.

If there is enough interest in this topic we could open up a project on SourceForge or something like that. Let me know if anyone is interested in grabbing the data dumps or XML. As I said, I will need to do a bit of work to port some of the distance functions from Oracle but this is something I have been meaning to do for a while anyway.

Jamie
jalessio@stoic.net

Anon

i am very thankful 2 u

Anon

Standard database practice trys to limit the load on a database. As Tim Perdue has said You can always put in another webserver, a database server is another story.

pnoeric

Here's a question-- this stuff is all based on a zip code or city name mapping to a latitude and longitude, then you run it through a function like this one that I found at http://archives.hwg.org/hwg-techniques/3BE6E373.1124.615E5B@localhost

LLDist($FirstLatitude,$SecondLatitude, $FirstLongitude,$SecondLongitude ) {
$milesDiff = 3958.75 acos((sin($FirstLatitude/57.2958)
sin($SecondLatitude/57.2958) + cos($FirstLatitude/57.2958)
cos($SecondLatitude/57.2958) cos($SecondLongitude/57.2958 -
$FirstLongitude/57.2958));
return $milesDiff; }

The problem is that, when you have thousands of zip codes, you can't easily do the search-- to find the distance from one zip to another, you have to run the math on every other zip in the database.

Can you limit the data set by just bounding the latitude and longitude by 'x' miles (however that happens), then run the math to get the real results?

Or-- that web page referenced above talks about KD trees. Anyone know how that works/can explain it to me roughly in PHP-speak? :-)

thanks,
Eric

Anon

I would like to see more about zip codes
I am studying how to construct the tables to provide an efficient database.
I would like to expand the Countries to Uk, Fr,Au,Ca,etc.
I think your offer of a data zip file would be helpful and could it be expanded.
How do you have the tables structured...pk,fk, id etc.

Russell Griechen

vivango2

I'm also interested in an international zip/postcode relation with latitude and longitude relation database.

There's some info here:
http://spatialnews.geocomm.com/newsletter/2000/jan/zipcodes.html