I have had a few people ask about it, so I decided to publish the tools I created. If you use this, please let me know and please send any enhancements.
Please also consider lobbying the government to fix the fact that this database is excessively expensive rather than it being free and shareable.
See the CivicSpace posting (Link no longer works -- text copied below) for details.
Download tools (updated periodically - Most recently September 16, 2008): digital-copyright.ca/pcfrf/pcfrf.tgz
Few pieces: see the readme.txt for some simple documentation.
Current version supports both the raw data from Statistics Canada, as well as the MakeTheChange.ca web API.
The CivicSpace labs article is no longer available, so I am copying the article from an archive.org archive.
I am involved with a site that is launching as part of the Canadian Federal election. One of the features will be a lookup of their postal code to find their electoral district, and from that they will be able to find a list of candidates.
While I could "go my own way", I want advise on how to best author this so that it can be integrated into work that other people are doing. Areas are postal code lookups, how to indicate what electoral district people are in, and how best to store "candidates" for the election.
I'm also wanting to be creating all of this data in a way that I can sent back to CSL so that other Canadian campaigns won't have to duplicate all the effort.
Postal Code Lookup
Unlike the US, Canadian Crown Copyright is in the way of distributing a lookup table. To get an accurate table in Canada you have to purchase it from Statistics Canada who charges $2,900.00 for a one-year subscription and has a draconian EULA. Abolishing crown copyright entirely, especially on information such as this which the government must maintain anyway, should be an issue in the election -- but it is so hard to get lesser known/understood policy issues onto the agenda.
The main table of information is a simple mapping between postal codes and the standard electoral district ID that everything else uses. Because of odd boundaries there are postal codes that map to more than one district.
A0A1A0 maps to 1 postal code (there are 789027 of these)
A0A1C0 maps to 2 postal codes (there are 5293 of these)
B0J2L0 maps to 3 postal codes (there are 440 of these)
H1C1H2 maps to 4 postal codes (there are 85 of these)
H1T4C6 maps to 5 postal codes (there are 11 of these)
T5S2B9 maps to 6 postal codes (there are 2 of these)
I notice that the zipcode.mysql database table seems to be focused more on finding out where things are located (including lat,long), which is information that the Canadian database I have doesn't offer.
Right now I'm just using a separate table structure, named pcfrf as that is the acronym that Statistics Canada uses:
CREATE TABLE `pcfrf` (
`edid` int(11) NOT NULL default '0',
`postal_code` varchar(7) NOT NULL default '',
KEY `postal_code` (`postal_code`)
If anyone else is purchasing the database I can send them the PERL script which I used to create the insert statements from the CD that Elections Canada offers.
Before starting my current project I wrote a simple module for Drupal which used this database, and allowed people to set an EDID variable using the simple "profile" module method. I created a "set this to my electoral district" option when displaying the taxonomy, and a "my electoral district" option which would jump to the right taxonomy
See: http://www.digital-copyright.ca/edid/35064 for the "Ottawa South" district, which is my district.
Note: the edid/35064 is a url_alias for taxonomy/term/191 . I have a taxonomy http://www.digital-copyright.ca/taxonomy/term/1 that lists the provinces and then the districts per province.
Storing Electoral District
While the EDID variable I used for the simple module works fine for the simple Drupal site, I'm wanting to know the "better way" to do things with CiviCRM. Should I create a CiviCRM custom field? Can the "selection" option support having 308 possible options (there was some filed which suggested there was a 10 limit?).
Built into the first 2 digits of the EDID is the province, so that information is already known.
Storing Election Candidates
Each electoral district will have a group of candidates for this election. What is the best way to store this information?
My first thought was to create a tag such as "cec2006", with a description of "Canadian Federal Election 2006 Candidate". Then I could create a cache of all the contacts that have this flag and store which EDID they are in to be able to quickly generate the list of candidates in a district.
While there are candidates that run as independents, most run as part of a party and it is important that this is prominently displayed. Is this another obvious candidate for a custom field, where other contacts who are not candidates could also indicate party affiliation if it is relevant?
After the election we will also have contacts that become MP's, and we'll want an easy way to flag that.