Classified...distance from seller

[Trader]

how about accepting Canadian Postal codes also? There are a LOT of us Canucks here!

[M61A1MECH]

Problem would be the mileage would not be any more accurate than the ones for the US postal codes, I have a few tell me it only 500 miles from my place in Florida to someplace in Michigan or New York, I pretty sure that is not right. I am not sure what data base the system uses, but it needs updating I think.

[Freebird]

Yea, the zip code database seems to have some errors. I have tried updating it a couple of times but it is still not perfect.

 

The problem with adding the Canadian postal codes is that there does not seem to be a list that can easily be converted. The US zip codes are available for free so the programmer of the classifieds software included the data base with the software. There seems to be no free version of the Canadian codes. I could purchase one but then it would most likely not be in the correct format and would have to be totally modified before it could be imported.

 

Here is what is required. Maybe we have somebody here who would be willing to tackle it.

 

Here are the fields in our zipcode database, As stated as long as you can find a zipcode cvs file with this information then it could be manipulated into the format of the zipData database.

 

CREATE TABLE zipData (
      zipcode varchar(6) NOT NULL default '',
      ZipCodeType char(3) NOT NULL default '',
      City varchar(128) NOT NULL default '',
      CityType char(3) NOT NULL default '',
      State varchar(64) NOT NULL default '',
      StateCode varchar(5) NOT NULL default '',
      AreaCode int(5) NOT NULL default '0',
      lat varchar(8) NOT NULL default '',
      lon varchar(8) NOT NULL default '') ENGINE=MyISAM DEFAULT CHARSET=utf8;

so if you can find a canadian zip code cvs and format the data in the proper row sequence you can import that right into the zipdata database. The area code field is not needed so there could just be a blank row used there.

 

There is one available here but I don't know how out of date it is:

http://www.populardata.com/downloads.html

[SilvrT]

Yea, the zip code database seems to have some errors. I have tried updating it a couple of times but it is still not perfect.

 

The problem with adding the Canadian postal codes is that there does not seem to be a list that can easily be converted. The US zip codes are available for free so the programmer of the classifieds software included the data base with the software. There seems to be no free version of the Canadian codes. I could purchase one but then it would most likely not be in the correct format and would have to be totally modified before it could be imported.

 

Here is what is required. Maybe we have somebody here who would be willing to tackle it.

 

Here are the fields in our zipcode database, As stated as long as you can find a zipcode cvs file with this information then it could be manipulated into the format of the zipData database.

 

CREATE TABLE zipData (
      zipcode varchar(6) NOT NULL default '',
      ZipCodeType char(3) NOT NULL default '',
      City varchar(128) NOT NULL default '',
      CityType char(3) NOT NULL default '',
      State varchar(64) NOT NULL default '',
      StateCode varchar(5) NOT NULL default '',
      AreaCode int(5) NOT NULL default '0',
      lat varchar(8) NOT NULL default '',
      lon varchar(8) NOT NULL default '') ENGINE=MyISAM DEFAULT CHARSET=utf8;

so if you can find a canadian zip code cvs and format the data in the proper row sequence you can import that right into the zipdata database. The area code field is not needed so there could just be a blank row used there.

 

There is one available here but I don't know how out of date it is:

http://www.populardata.com/downloads.html

 

 

I am looking into that link as I type this

[Freebird]

According to the classifieds developer, the program is written to accept the alpha numeric codes.

[SilvrT]

I have Imported the Postal Code data from that link and can convert it into another .csv file in the format that you can use.

 

However, some data may be missing as here is how the fields transfer over...

 

zipcode varchar(6) .......... PostalCode

ZipCodeType char(3) ....... ????? (no corresponding field)

City varchar(128) ........... City

CityType char(3) ............ CityType

State varchar(64) ........... Province

StateCode varchar(5) ...... ProvinceCode

AreaCode int(5) .............. ???????? (no corresponding field)

lat varchar(8) ................ Latitude

lon varchar(8) ................ Longitude

[Freebird]

I'm going to email you one of the sql files that are currently used.

[Trader]

so all this tech talk means you can do it right???

[SilvrT]

so all this tech talk means you can do it right???

 

yes... us tekkies can do anything ... eventually!

[friesman]

yes... us tekkies can do anything ... eventually!

 

 

Yes tekkies can do anything,,,,,,,,,given enuff beer.....lol!

 

Brian

[Freebird]

Yes, it means MAYBE? SilvrT has sent me back the csv file. I'll see if I can get it to work. Will try to get on it tonight.

[Trader]

Its a small thing but it helps....THANKS EH! (I think that's a different thread!)

 

I know my Canadian Geography...but I have to admit I have trouble knowing where some of the states are located...other than a general area.

 

Not that it matters too much for now....I still have to get my passport so I can come visit "y'all"

 

Certainly want to get to next years' MD....and the Dragons Tail sounds interesting.

 

Lots of twisties up here though...so I'm not depraved or anything I mean DEPRIVED.

[Freebird]

Sorry about the delay folks. SilvrT was kind enough to create a file that we THINK will work to add the Canadian postal codes but I just haven't got around to trying it yet. I'm out of town and last night had to setup for a trade show that starts this morning. I promise I will get to it ASAP. I just want to make time I have sure to repair anything that goes wrong when I do it. All I have to do is try and import the new file into the existing database and I will create a copy of the existing database before I do so. Therefore, there should be absolutely no chance of any major problems but......well......you just never know.

[SilvrT]

All I have to do is ...*****...you just never know.

 

heh ... so many times I've spoken those words.... hope it works for ya without any glitches!

[Freebird]

OK folks...this appears to be working now. Thanks to SilvrT for his help with the data.

 

I posted a test ad using a Candian postal code and it did work. Now I know there are some mistakes in the US zipcode data and that is what sometimes causes the distances to be incorrect. The program uses longitude and latitude coordinates that are tied to each zipcode to calculate the distance. So if any of those coordinates are entered incorrectly, it causes mistakes.

 

If anybody cares to verify the data, you are welcome to do so. There are only about 1,860,000 entries that have to be verified.

 

I'll just live with the mistakes thank you.

[SilvrT]

And we can now pass along an old "handle" I had to Don .... we can now refer to him as "The Grinder" .... and don't go lettin yer dirty little minds go there!!!!

 

Don kept at it until he made it work!! Great Job!

 

[Trader]

Quote:"OK folks...this appears to be working now"

 

I looked into an ad posted July 3 (1st gen for sale) and tried to put in my Canadian postal code but the input field would only accept 5 characters....we need at least 6...or 7 if you allow for a space between first 3 and last 3 characters.

 

I know you are watching....and I know you are on a well deserved holiday....so when you get back or feel so inclined it is something that perhaps you can look into?

[Freebird]

Try again. It should take 6. Do not use a space.