Upload a different zipcode table?

CavySpirit · September 11th, 2006, 11:11 PM

I needed the Canadian lat and longitude.

I've purchased a table that has them combined. How do I upload and insert that table in the database? I figure I'll just create a new table, test it, and then delete the old one.

But, I have no idea how to get it in there!? Tons of records.

Chuck S · September 12th, 2006, 07:17 AM

You would need to download there cvs or excel zipcode and arange the data in the same format as our zipData table and delete any rows needed. Once you have the data correct you can insert those inserts.

CavySpirit · September 12th, 2006, 11:30 AM

I guess that's my question. How do I "insert those inserts?" There are over 800,000 records!

Chuck S · September 12th, 2006, 11:38 AM

If you have there cvs dump you need to load it into excel and delete rows you do not need and alter the line up of the rows to match the fields in the order the zipdata table has the records. Once you get it in the right format you can import it through phpmyadmin using the upload cvs txt file function

CavySpirit · September 22nd, 2006, 10:55 AM

Just an update on this project. I've gone a different direction with lat and long. But I just want to say that calling the zip/postal file of 800,000 records using the standard logic with Canada is NOT a viable solution. You hit the enter key and go get a cup of coffee and that's just on a test database with a dozen product entries. And I have a dedicated server.

Chuck S · September 22nd, 2006, 06:54 PM

How would you be calling 800,000 records though?

CavySpirit · September 23rd, 2006, 12:53 PM

Well, I don't know the internal workings of mySQL, but when the zipData file has 800,000 records and when showcat.php has to access that file searching for a valid entry on every product, the performance went to hell. I don't know if making zipData indexed or not or some other technique would help, but I assumed that mySQL must have to do some kind of opening of the whole file or something when it is queried.

Soooo, my version now only checks that file when a zip/postal is created the first time in uploadproduct.php or changed in editproduct.php or changed on a search in showcat.php.

I now store the lat and long in the product file. And I also require that non-US/Canadian folks create an entry in the Googlemap application (thanks to vBulletin) before creating an ad in the classifieds. That way I can get their lat and long from the table that stores it by userid.

I've also added a flip on the search entry that allows the user to switch the location based search from entering a zip/postal code to entering a lat/long. And now my distance calculation is available to anyone and everyone in the world. And I've also changed the distance calculation in the application itself. I think I found what looks like a better one, and it allows me to pass an "M" or "K" and if it's K, I get back the distance in Kilometers instead of Miles (one simple multiplication in the function). So, on the showcat search info, I've also added an option to switch between miles and kilometers--and I store the preferences as a cookie.

I've been forced to use up valueable extra fields for 'city,' 'state/province,' and 'country.' Originally, I also used zip for non-US zip codes which I have to go back change now. In my opinion, location fields and contact info should absolutely be in the product file. Things like area code should be searchable and sortable. Same with city, state, country, lat & long for distance. A contact name. There are just basics about classified listings that should be there without having to devote so much time to hacking the system to get them in.

I've had to rely heavily on vBulletin userprofile fields to come up with the missing info and that wasn't easy.

I love this script, but I have spent a mountain of time trying to get it up to snuff on handling classified listings that are location based around the world, not just selling simple things over the internet. I'm almost there. I would be happy to send you back my files to take a look at what I ended up doing so that perhaps you can think about adding those features back into to future releases.

CavySpirit · September 23rd, 2006, 01:02 PM

Also, I'm going to end up making the same distance modifications to reviewpost when I get done with classifieds because I want people to be able to search for certain products/listings within their area--even if they aren't in the US.