destination (zip code,city, etc) database

[Jocka]

I've asked this before but now I can't find the answer (I asked close to a year or more ago).

The site I'm working on needs to be able to find things within miles of eachother. I don't know how I could go about doing that on my database aside from entering the (god knows how many) zip codes, cities, and states into the database and hooking them to eachother.

I'd prefer not to do that lol. So where can I find something that's built for this? I just need it to pull information according to how many miles away from eachother it is (i'm guessing from a list of given zip codes from this database).

Any ideas? Also, it might be cool if this database or whatever had the MAP feature too. But it's not "needed".

[.josh]

well if you have or can (easier) get gps coords for your cities (i guess you're talking about cities), you can use Pythagorean theorem to find the distance (a^2 + b^2 = c^2). dunno if that somehow helps you any, but i thought i'd throw that out there just in case...

[Philip]

Here is a list of all (but 6 zipcodes) and their longitude and latitude:
http://www.cfdynamics.com/cfdynamics/zipbase/index.cfm

You could make a PHP script to insert it into the DB pretty easily.


Or, if you are a little more lazy (like myself) there is a pre-made script here:
http://www.micahcarrick.com/v2/content/view/4/3/ (click download zipcode-1.2.0.zip)

[ober]

That's awesome KingPhilip.  Thanks :)

[Jocka]

I used that premade code there KingPhillip. It works NICE but it's a bit slow.. I understand this is really more of a database issue than PHP though. All I added so far is one state (texas) and it takes 5 seconds (about 3,000 entires). I can only imagine how long it'll take to run through all 40,000+ that file has.

Anyone have an idea of how to cut the time down? I'm thinking it might be best to create a different table for different states. It might make it a LITTLE easier. The only thing with that is, I'd need to know which state the zip went to and asking the users that would be ridiculous.

If anyone can help me cut the time down, that would be kool. If not then I'll just go the other way with it and create a loading page using ajax or something.

[redbullmarky]

setting up a proper index will help. i set up a database of 3,000,000 records (city, country, longitude, latitide) and it took AGES to search until i set up an index across city+country fields (not just normal primary key). now it takes only marginally shorter than a normal SELECT query.

[ober]

I would suggest just downloading the zip file from the other link and then loading that into a database and creating your own calculations.  It would be faster and custom to your needs.  Plus it has the state in it.

[Jocka]

ober - You mean the 2nd link? thats the one I'm using. I started it just to get an idea of how all this works and I decided I'd keep it's database and just rewrite the code the way I like it.


for a 50 mile search it took 594 zip codes from a list of 3,000+. It took 3.3118558 seconds.

zip_code is indexed. Is this still bad or is it just me? I'll be pushing 40,000 or so soon and if it takes 3 seconds for 3,000 then it'll take (estimated) 14-15 seconds. Thats HORRIBLE.

There HAS to be something else that can be done here. I'll only need to load this info once and then I'll set it in a session for the user. But that 14-15 seconds can be a lifetime to somebody that wants it now.

Should I just go with a "loading page"? Or is there some other mysql trick I'm missing

[ober]

Maybe it's the way you're calculating it.  I mean really... you're just doing a simple calculation with 2 coordinates, right?  You're not calculating the distance between every single zip code in between them.... are you?

[Jocka]

Sort of. what I'm doing is puling out everything within so many miles of the given zip code. It spits out however many zip codes in that radius and then I pull info from a different table with any of those zip codes.

I forgot to INDEX the zipcode area of the other table. It cut down the time a little bit but not much. Even after it gets all the info from the database, it takes anywhere from 15-60 seconds to load the page. It's on a test server on my home computer which IS sometimes slow but never this slow.

[redbullmarky]

i'm not an authority to advise of the hows and whys as i don't understand the technical aspects of mysql fully, but like i say, i'm using a db with more than 3m records and the time is negligable, even when doing a JOIN with another table with about 10000 records (regions/counties). maybe someone can explain indexes a little better (and how to use them properly). but that sort of lag isn't what you'll need to settle with.

[ober]

If anyone downloaded the text file version of this database and you can email it to me, I'd appreciate it.  They have removed it from that site and I could really use it.  If no one can get it to me in the next hour or so, forget it.

[Jocka]

the download worked for me? I have the text file database information from the 2nd one (which also appears to work). But I'd have to drive home real quick to get that to you. I'm at work right now.

[ober]

Ok, nevermind.  It wasn't working when I tried it. 

[ober]

FYI, in case anyone was wondering how fast it would be, I've imported all records with all columns to a database on my server and setup my contact form to fetch the data.  The table is indexed on the zip code itself and I don't think it could be any snappier.  Granted, I'm just grabbing the city and state, without any sort of calculations.

Check it out: http://www.whproductions.com/index.php?req=contact