Canadian postal codes for MySQL zipcodes table

I don't think you can get anything for free in Canada, unfortunately. You can purchase a database for a few hundred bucks from a number of companies online. I can't vouch for any of them.

There is a free Canadian Postal Database available at http://www.populardata.com/downloads.html
(As far as I could find the GeoNames database doesn't contain actual postal code information - just city name to lat/long)
Cheers

Here is the Canadian postal codes file ready for import into the zipcodes table created by the Location module.

Please let me know about any issues with that file.

I'm using it on my test site and I am able to do proximity searches on Canadian postal codes to locate resources in the area.

IMPORTANT: you only need the first 3 characters of the postal code to do a search, because that sql file has only first 3 letters of the postal codes included.

Explanation

I compared files with Canadian postal codes from:
1. GeoNames.org website, downloaded file CA.zip from http://download.geonames.org/export/dump/
2. GeoNames.org website, downloaded file CA.zip from http://download.geonames.org/export/zip/
3. http://www.populardata.com/downloads.html (thanks, newmediaist, for sharing the link)

2 files from GeoNames have different type of content.

1. The first one (export/dump) has 307,566 records with fields like geonameid, name, asciiname, alternatenames, latitude, longitude, feature class, feature code, country code (info from readme.txt file that comes with that download). I checked out that file and there are a lot of addresses for which latitude and longitude are the same. I believe that it would bring down the number of records with unique latitude and longitude quite a bit. Also, that file doesn't have the postal code information, therefore I went ahead with the second file from the same website.

2. The second file from GeoNames (export/zip) has postal code included and the values for latitude and longitude are unique (they don't get repeated through the table, which tells me that this file eliminated the redundancy from the first file). The second file has about 1,600 records.

3. The file from populardata has about 56,000 records but most of the postal codes have the same latitude and longitude (postal codes with the same first 3 characters).

Since the searches on 1,600 are faster then on the 56,000 and since it seems that we are not missing any information, I used the CA.zip file to create my sql import.

The import is ready - use phpMyAdmin or other tool to run that query directly in the database. I divided that query into several small, about 500 rows each. You can run the whole file as one query or run as several queries, if needed.

I welcome any feedback. If that file works, I can change it's format so I can be part of the database zipcodes dump for Location module.

kasiawaka -- great work on the CA_postalcodes_rev1.zip! You really saved my life with this!

The only problem seems to be that the list only includes 1600 records. Are there other files I could download?

Thanks!

Hi, thanks for the work in putting this together. I imported the zip codes list into my database, but I've noticed that on my user location table, if I do a proximity search for a canadian postal code (using 3 digits), it returns my entire list of users (all the US users).

The proximity search works great for my US database... is there anything I'm missing here? Should I set up a separate view just for canadian searches?

To provide more information, I can see that longitude and latitude is being generated for any of my users in the US - they enter their address, save the node, and the long/lat gets created.

For the canadian users, I don't see any longitude and latitude, even after resaving their nodes. I've configured geocoding for canada to use google maps, same as US, so not sure why it doesn't work for them :(

Edit: Turns out they weren't putting their province in as part of their address, which resulted in the long/lat not being determined when the node was saved. Fixed :)

Hi nest0r, thanks for noticing it. I corrected my first post, I entered incorrect number of rows. I am using this file with 1600 records and so far everything seems to be working great.
If I get a chance, I will analyze other files to see if they would bring more information into the postal code table.

OK, here it is another version of the Canadian postal codes - this time I cleaned the third file mentioned in my first post (from the populardata.com: http://www.populardata.com/downloads.html).

This time I got about 6,500 records after the cleanup. The original file had about 700,000+ records but for our purpose there was a lot of duplications (the same postal code, latitude and longitude but different city name), so I removed the duplicates, leaving only the unique values.

The good news is that the search can be done on the full postal code (no more need to enter only first 3 characters as it was with the fist file).

So again, in order to add the data from this file to the zipcodes table, use phpMyAdmin or some other database administration tool to import that file into your zipcodes table and you can start using Canadian postal codes in you proximity searches.

Good luck
Kasia

Ups, there was 1 postal code repeated twice which was returning the insert error. Here is the corrected file.

Also, please notice important difference between the first file I posted (in my first post) and this one.

This file has more rows (6,500+), but it only has about 1,200 unique postal codes, if considering only the first 3 characters of the postal code.
That means that searches for certain postal codes return more exact latitude/longitude, but some postal codes are not included.

The first file contains only postal codes with the first 3 characters, therefore is less exact but has 1,600 postal codes (considering only the first 3 characters), so it's more likely to return a search on the postal code proximity.

Example: I live in a small suburb of Ottawa. When I do proximity search using the first file, I actually get results of the search. They are about +/- 1km, and I have to use only the first 3 characters of my postal code for the search conditions, but the search works. Using the second file, I don't get any results (or, to be exact, the whole set of nodes), because my full postal code is not included in the data set.

kasiawaka:

I've just had a chance to download the new file and it has the right number of rows. I haven't had a chance to try it in the app, but it looks good. Either way, thanks for going to all the trouble! Really, you saved my life!

Thanks again!

related: #175193: Zip code database out of date