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

Here's a version of the file with all 750k+ postal codes:

http://www.nerdliness.com/files/download/ca_postal_codes.zip

For our purposes, we needed to have all of the postal codes listed to make the searches as close to 100% accurate pulling results as possible. Without them all, we ran into the problem kasiawaka mentioned in #9 above.

The file's big, that's for sure, but it should work. I didn't include the time zone information in it, though. Didn't really matter for our purposes.

Sorry, noticed some folks trying to get that file, but we borked that URL after making some changes. Try this URL: http://www.nerdliness.com/sites/default/files/download/ca_postal_codes.zip

The file posted in #13 returns an error on import into the zipcodes table because it has duplicate entries in postal codes. Zipcodes table has a key on the postal code field and it doesn't allow duplicates.

I took the original file from http://www.populardata.com, removed duplicate postal codes and got 764,852 rows with Canadian postal codes. The file is available here: http://www.freeformsolutions.ca/en/sites/default/files/downloads/zipcodes.ca.mysql.zip.
This file should be almost like the file in #13 but won't return an error on import.
The timezone and dst are not in it, because populardata file didn't have those values available.
Be aware - the file is big (2.2MB zip, unzipped 105MB) and takes time to import into the database.

Hey all. There's an error in the file in #14.

Right at the end of the file it says:

<b>Fatal error</b>:  Maximum execution time of 300 seconds exceeded in <b>C:\wamp\apps\phpMyAdmin-3.1.1\libraries\export\sql.php</b> on line <b>928</b><br />

This is in the file itself. It's not an error message I get when installing. I didn't try to install it yet. I found this on visual inspection.

It seems like the last row is V4C 3N8, then it times out.

If your import is slow you can add the following to the start of the script if using MySQL. And don't forget to erase the error line at the end of the script in #14...

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

SET AUTOCOMMIT=0;

And the following at the end:

COMMIT;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

I ran into an issue with the CA postal code list in 6.x.

When doing a proximity search, and your exact postal code isn't listed in the zipcodes table, NOTHING will be returned. This isn't ideal behavior, IMO, but that's just how it works currently.

If you don't have the exhaustive list of zip codes, or you just want a bit more flexibility in your canadian searches, then here might be a good option.

Replace your location_latlon_rough_ca() function in /sites/all/modules/location/supported/location.ca.inc with the following:

function location_latlon_rough_ca($location = array()) {
  if (!isset($location['postal_code'])) {
    return NULL;
  }

  $postal_code = $location['postal_code'];
  while(strlen($postal_code) >= 3) {
      $result = db_query("SELECT latitude, longitude FROM {zipcodes} WHERE country = '%s' AND zip LIKE '%s%'", $location['country'], $postal_code);

      if ($row = db_fetch_object($result)) {
        return array('lat' => $row->latitude, 'lon' => $row->longitude);
      }
      $postal_code = trim(substr($postal_code, 0, -1));
  }
  return NULL;
}

What it does:
Searches first for the entire zip code - if nothing is returned, it will continue to lop off the last character and search for any zip code beginning with that sequence. If it gets down to the first three and STILL doesn't find a matching zip code, it will return with the standard failure mode.

This is great for those of us who do only need to do approximate proximity searches or those who don't know their exact postal code (which is a valid concern we have gotten from customers)

That's an interesting hack - you should consider contributing it to the module as a patch, as I think most people would prefer that response from the module!

My concern in #15 was not about the install process. It was pointing out the fact the the file is incomplete because whoever generated it got a timout error which was logged in the file. All records after that point are missing. As I mentioned, the last entry before the error in the file was V4C 3N8.

The comments in #15 and #19 are absolutely correct - I didn't notice the script timed out and missed a few hundreds postal codes from the end of the table.
Thank you for posting that information.

I've corrected the file (http://www.freeformsolutions.ca/en/sites/default/files/downloads/zipcodes.ca.mysql.zip) and now there should be no issues with the error at the end of the file dues to a missing rows. The total rows of Canadian postal codes in the zipcodes table, after importing that file, should be 764,852.

Please let me know if you experience any problems with this file

I've updated the status of this support request to "needs review".

Thanks for this!

Import works fine.
No timeouts on my Mac laptop dev machine.
Correct number of lines imported.

Registered a user and a lat/lon is found for an address/postcode.

Pass from me.

I'll let somebody else change the status, 1 test isn't enough in my opinion.

For those that are interested, I believe that a definitive guide is available, encumbered with licensing issues (as mentioned in #1), from Canada Post over at http://www.canadapost.ca/cpo/mc/business/productsservices/atoz/postalcod....

I'll test the above zip file soon. Hopefully it'll work well enough for us to get it packaged with the module!

I can verify that the file noted in comment #20 works. Great job putting this together, kasiawaka.

That's two passes & zero fails.

#17 needs to be committed. I've updated to 3.1 but have to manually reapply the patch.

Patch from #17

Last patch was created from wrong directory. This one is from the root of the location module.

Lets try this again.

Hey guys, I appreciate the link to the Canadian post codes. I had a list of USA, but needed canada, so thumbs up. I've included my db table for anyone's use. It deviates from what was provided here before me, but only a little and should be a quick change for anyone who's interested.

It also includes timezones, state/province names and area codes. Enjoy.

---

Well, nevermind. I just tried uploading it (5.X mb zip) and it was rejected. If anyone wants it, feel free to contact me. I'll always have it on hand.

I was looking for the same data previously, but found most of the free data are outdated. Even some commercial data provider is selling out dated data. After several trial run on commercial available demo, i decided to purchase the one from igeocode (http://www.igeocode.com/products/capostalcodes/gold), their data seems to be the most complete and with enough details without the price tag that will freak people away. And they seems to constantly update their data too, which is good for critical business need.

You may want to try out their online demo at http://www.igeocode.com/products/capostalcodes/demo.

cheers!
technie

I'm a newbie and having a bit of difficulty following all this. I know I need to be able to verify postal codes when the users enters them in their profile and then later use the postal code to provide a list of projects in a 100 km proximity to that postal code.

I think the first thing I need is to have a list of verified postal codes and I believe this is what these postings are referring to. Do I first add the Location module and then import the file from post # 31 and then the list will magically appear somewhere and verify the postal codes?

I don't think it's that simple but if someone could describe the process step by step that would be wonderful. I'm using Drupal 7. Thank you for the help. It is much appreciated.

I have just done my project with some basic search by using Postal Code and Cities and I am using zipcodeworld.com data. They have been around for a few years now and it has all the needed information.
http://zipcodeworld.com/postalcodegold.htm

patch works as advertised against 3.1. i typed in a postal code that doesn't exist and it in deed found nodes related to the first 5 digits. ideally, it would ignore spaces in the postal code in the db or entered by the user but perhaps that's another issue.

Here is that same function with the added bonus of stripping out spaces so it finds matches regardless of if the user inputs a space or not. Should I reroll the patch?

function location_latlon_rough_ca($location = array()) {
  if (!isset($location['postal_code'])) {
    return NULL;
  }
  $postal_code = str_replace(" ","",$location['postal_code']);
  while(strlen($postal_code) >= 3) {
    $result = db_query("SELECT latitude, longitude FROM {zipcodes} WHERE country = '%s' AND REPLACE(zip,' ','') LIKE '%s%'", $location['country'], $postal_code);
    if ($row = db_fetch_object($result)) {
      return array('lat' => $row->latitude, 'lon' => $row->longitude);
    }
    $postal_code = trim(substr($postal_code, 0, -1));
  }
  return NULL;
}

Adding to peggysmouse's modification... this sets the string to uppercase in case the user types in lowercase characters. I'd be curious to see how to make it completely case insenstive if the database includes lower-case postal codes.

function location_latlon_rough_ca($location = array()) {  
	if (!isset($location['postal_code'])) {    
		return NULL;  
	}  
	$postal_code = str_replace(" ","",$location['postal_code']);  
	while(strlen($postal_code) >= 3) {    
	$result = db_query("SELECT latitude, longitude FROM {zipcodes} WHERE country = '%s' AND REPLACE(zip,' ','') LIKE '%s%'", $location['country'], $postal_code);    
	if ($row = db_fetch_object($result)) {      
		return array('lat' => $row->latitude, 'lon' => $row->longitude);    
	}    
	$postal_code = trim(substr($postal_code, 0, -1));  
	$postal_code = strtoupper($postal_code);

	}  
	return NULL;
}

Kasia you and Julian always seem to be saving my tail. Thanks for putting this together.

Hi HJulien,
This thread includes discussion and a MySQL file for Drupal database (Location module) to extend standard US postal codes database with Canadian postal codes.

To use this file, you need to have the Location module installed first. During that installation, a new table in the database, will be created. That table is called zipcodes and will contain the standard zipcodes that come with the Location module.

To extend that table and add there Canadian postal codes, you need to import the file from # 20 directly to the database - that means you need to have access to the command prompt or use some DB GUI like phpMyAdmin and use the import option to import the file.

I haven't tested that import on Drupal 7 but I just checked Location 7.x-3.x-dev module and it still has the zipcodes table so the file should work the same way for that module.

Hope that helps,
Kasia

In regards to file #20;

This file size is ridiculous. Takes about 30min just to get up to the server. I mean the US file size 2.8MB and the Canada file size is 115MB! Find it hard to believe Canada having that much more zipcodes than the US. Anywho, takes 30min to upload to server and furthermore even bigdump has a limit on this file.

Any advice on importing the database in to a 1&1 shared hosting account?

the file http://www.nerdliness.com/sites/default/files/download/ca_postal_codes.zip reports the wrong logitude/latitude for my postal code, at least a 2 kilometer difference to the NE

I did not as yet test other postal codes, but I'm guessing mine was about is random enough. Did anyone else notice that the actual data may be incorrect.

Removing location_latlon_rough_ca() makes Location use location_latlon_rough_default(), which does a one-time geocoding of the postal code and stores it in the zipcodes table. Also removed location_latlon_exact_ca(), pretty sure it's not being used...

Just so you know the sql script in #20 takes more than 30 minutes to run. I have drupal 7 installed with wamp on windows 7, and I have been running the script for more than 12 hours and still running, I can see with phpmyadmin that the rows are being created, but it takes a lot of time! and surely more than 30 minutes because de file size is 114MB!!! with more than 700.000 rows to insert!

However you can still work on drupal during the insertion!

See #1082858: Drupal 7 fixes for the Canada zipcodes DB.

In other news: Canada Post sues to keep ownership of postal code list. The mentioned company, Geolytica, seems to provide another source of this data.

In case anyone is interested: I wrote a module for an AJAX admin page for importing database records.
I also cut down the CA zipcodes file in half by removing the extraneous INSERT statements to work with the import script.
It loads the entire SQL file into buffer on the server for each pass, so while on a typical server setup, it flies through the other more normal sized imports, with the CA database I got about 1.5 seconds round-trip for an import of 50 lines. I probably should have set it at a couple hundred at a pass, but it is fine for a utility. You can start it and forget it and 6 hours later your CA import will be done. India, which is the 2nd largest took 7 minutes.
Anyone wants it let me know. I will have to tweak the menu and break it into a separate module (it is part of a larger import module for this site). Not free but maybe $25. The reduced CA zips file itself is 48.5 MB, so if you haven't enough memory limit to cache that and whatever it takes to bootstrap Drupal, it might not work for you, or you may need to break the file into a couple.

The data in #20 looks good. I wonder though if we should remove the spaces. Doing a search for "G9X 4T9" for example means we have to do a bunch of work before the query if they enter "G9X4T9" or "G9X-4T9" for example. In the second case we have to first remove the "-" then add a space after the third position. My inclination is to strip the space from the entire database table and then validate everything to simply convert the string to uppercase and remove any non alphanumeric characters before the query:

$zip = strtoupper($zip);
$zip = preg_replace("/[^A-Z0-9]/", "", $zip);

The argument to remove the spaces is particularly compelling to me when we consider that we're probably adding this into a table that contains other (U.S.?) zipcodes which don't have spaces.

Anyone have a compelling argument why we shouldn't do this?

all feature requests should be rolled against latest 7.x-3.x-dev and after commit can be backpirted to 6.x branch

bot