Postal codes by federal ridings (2003 representation order) file (PCFRF) tools

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.

Examples:

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.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Other Queries..

I have had a number of people ask about the postal codes that match more than 1 riding. There is a growing belief that either the database used by PARL.gc.ca or the database provided by Statistics Canada (which I have) is incorrect. I am providing a few queries to help with investigations of this potential problem.

Postal Codes with 6 matches:

T5S2B9
T6T1V2

Postal codes with 5 matches:

H1T4C6
H2B2B1
H2S3R5
K0A2W0
K1A0K2
N0M1C0
T5Y3B1
T5Y3B3
T6T1T4
T6T1T9
T6T1V6

Sampling of postal codes with 4 matches:

T6T1P9
T6T1T3
T6T1T7
T6T1V3
T6T1V5
T6V1K2
T6X1C9
T6X1E2
V3X3L6
V3X3R1

Sampling of postal codes with 3 matches:

B0J2L0
B0N2H0
B3T1Y3
B3T2B7
B3T2B9
B3T2C1
B3Z1L4
B3Z1L5
B3Z1L7
B3Z1L8

Sampling of postal codes with 2 matches:

M9B3L9
M9B3M2
M9B3M4
M9C4Y4
M9C5K5
M9C5M1
M9N2V3
M9V4R9
M9V5C5
M9V5C6

For the techies, here are the SQL statements I've used:

SELECT postal_code, count(*) as numedid FROM `pcfrf` group by postal_code having numedid = 2;

SELECT *, count(pccount) from (SELECT *,count(edid) as pccount FROM `pcfrf` group by postal_code) as T1 group by pccount;

10001 	A0A1A0 	1 	789027
10001 	A0A1C0 	2 	5293
12002 	B0J2L0 	3 	440
24002 	H1C1H2 	4 	85
24021 	H1T4C6 	5 	11
48012 	T5S2B9 	6 	2


Free/Libre and Open Source Software (FLOSS) consultant.

Some more sample postal codes

One obvious way to get a current list of the riding names from a service (such as MakeTheChange.ca) which supplies English and French riding names from a lookup is to have an example postal code in each riding.

The file postal-code-for-districts.csv that is supplied with recent versions of this tool is a list of 308 postal codes which should give you each of the current 308 electoral districts.


Free/Libre and Open Source Software (FLOSS) consultant.

COST RECOVERY AND STATISTICS CANADA

A report by Ronald C. McMahon, Saskatchewan Bureau of Statistics, reviewed the cost recovery policy back in 1995 and concluded then that this policy was a failure. I wonder how long it will take to finally move forward on this issue.


Free/Libre and Open Source Software (FLOSS) consultant.