Jocka Posted November 23, 2006 Share Posted November 23, 2006 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". Quote Link to comment https://forums.phpfreaks.com/topic/28248-destination-zip-codecity-etc-database/ Share on other sites More sharing options...
.josh Posted November 23, 2006 Share Posted November 23, 2006 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... Quote Link to comment https://forums.phpfreaks.com/topic/28248-destination-zip-codecity-etc-database/#findComment-129170 Share on other sites More sharing options...
Philip Posted November 23, 2006 Share Posted November 23, 2006 Here is a list of all (but 6 zipcodes) and their longitude and latitude:http://www.cfdynamics.com/cfdynamics/zipbase/index.cfmYou 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) Quote Link to comment https://forums.phpfreaks.com/topic/28248-destination-zip-codecity-etc-database/#findComment-129181 Share on other sites More sharing options...
ober Posted November 23, 2006 Share Posted November 23, 2006 That's awesome KingPhilip. Thanks :) Quote Link to comment https://forums.phpfreaks.com/topic/28248-destination-zip-codecity-etc-database/#findComment-129186 Share on other sites More sharing options...
Jocka Posted November 24, 2006 Author Share Posted November 24, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/28248-destination-zip-codecity-etc-database/#findComment-129653 Share on other sites More sharing options...
redbullmarky Posted November 24, 2006 Share Posted November 24, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/28248-destination-zip-codecity-etc-database/#findComment-129680 Share on other sites More sharing options...
ober Posted November 24, 2006 Share Posted November 24, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/28248-destination-zip-codecity-etc-database/#findComment-129687 Share on other sites More sharing options...
Jocka Posted November 24, 2006 Author Share Posted November 24, 2006 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 Quote Link to comment https://forums.phpfreaks.com/topic/28248-destination-zip-codecity-etc-database/#findComment-129696 Share on other sites More sharing options...
ober Posted November 24, 2006 Share Posted November 24, 2006 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? Quote Link to comment https://forums.phpfreaks.com/topic/28248-destination-zip-codecity-etc-database/#findComment-129740 Share on other sites More sharing options...
Jocka Posted November 24, 2006 Author Share Posted November 24, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/28248-destination-zip-codecity-etc-database/#findComment-129745 Share on other sites More sharing options...
redbullmarky Posted November 24, 2006 Share Posted November 24, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/28248-destination-zip-codecity-etc-database/#findComment-129781 Share on other sites More sharing options...
ober Posted December 1, 2006 Share Posted December 1, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/28248-destination-zip-codecity-etc-database/#findComment-133608 Share on other sites More sharing options...
Jocka Posted December 1, 2006 Author Share Posted December 1, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/28248-destination-zip-codecity-etc-database/#findComment-133648 Share on other sites More sharing options...
ober Posted December 1, 2006 Share Posted December 1, 2006 Ok, nevermind. It wasn't working when I tried it. Quote Link to comment https://forums.phpfreaks.com/topic/28248-destination-zip-codecity-etc-database/#findComment-133650 Share on other sites More sharing options...
ober Posted December 5, 2006 Share Posted December 5, 2006 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 Quote Link to comment https://forums.phpfreaks.com/topic/28248-destination-zip-codecity-etc-database/#findComment-135633 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.