Getting geocoder.ca Canadian Postal Codes into Postgres

--download and unzip http://geocoder.ca/onetimedownload/Canada.csv.gz (9MB) to /srv/geocoder.ca/zipcodeset.txt


create table canadian_postal_codes (
  postal_code text primary key,
  latitude numeric(8,6) not null,
  longitude numeric(9,6) not null,
  place_name text,
  province_code char(2) not null
);

copy canadian_postal_codes from '/srv/geocoder.ca/zipcodeset.txt' with ( format csv, null '', encoding 'latin1' );

--there are two postal codes that are too long:
delete from canadian_postal_codes where length(postal_code) > 6;

--there are some that are lower case:
update canadian_postal_codes set postal_code = upper(postal_code) where postal_code ~ '[a-z]';

--some are "impossible", i.e. start with wrong letter for province, will update here later.