Canadian Civil Aircraft Register data / download

[WastedFlyer]

Hello all… Has anyone here used (or wanted to use) the Canadian Civil Aircraft Register (http://wwwapps.tc.gc.ca/saf-sec-sur/2/c ... hSimp.aspx)? For those who never heard of it, it’s a public database (2 tables) of every civilian aircraft registered in the country. The website allows for searches (by mark, model, city, etc.) and there’s also the option to download the current database (which has more than 36,000 marks and over 40,000 registered aircraft owners, either companies or individuals). The other day I downloaded it as I thought it would be interesting to analyze the civilian aircraft landscape in Canada (for example, most popular aircraft in each province, average age of single-engine piston aircraft, number of aircraft based at each airport, flying school fleets, etc… there are endless things to analyze…). Interestingly, when I downloaded the data, there was a “comment” section (which I’m now unable to find, maybe it was removed) and the vast majority of comments were along the lines of “this is useless”. What I don’t know is if those saying the downloaded tables were “useless” did so because they didn’t know how to handle/use databases (the format is comma-delimited text files, which looks like an ugly mess for those not familiar with databases, but it’s a standard format for importing data into specific database environments), or if they actually came across any other issue with the data… To be sure, the data is not perfect and some things need to be cleaned/tweaked. For example, there are 19 different spellings for the Pitt Meadows airport (near Vancouver): “CYPK”, “CPPK”, “Pitt Meadows”, “Pitt Meadows Airport”, “Pitt Medows”, “Pitt Meadow”, etc. Seems harmless but from a database perspective it really messes things up when it comes to searches, groupings (counts or aircraft per airport, for example). Things can be sorted out and cleaned/tweaked though, so I wouldn’t say the data/tables are “useless”. So back to my original question, has anyone tried (and failed/succeeded) in using this Canadian Civil Aircraft Register data (particularly the data download)…? If so, what were you trying to find out…? Let me know if you have any specific query, question and I’ll do my best to “crunch the numbers” and let you know. Free of charge of course! I’m evaluating how much interest is in this CCAR data and also how many problems there are (such as the above-mentioned problem with the airport city spellings), to see if it’s worth building a simple computer app for it and who knows maybe in the future I’ll become a “CCAR database expert” (and if ever make any money out of it, I plan to use it to help me pay for my CPL and assorted ratings…!) Just FYI, I’ve attached a few tables (print-outs from Excel, in PDF format) with data crunched from the CCAR tables (it’s possible to drill down to specific aircraft / marks, but thought I’s start with some general overviews). PM me or post here what you think of the CCAR data or of the attached numbers… Many thanks in advance for any feedback on this!

[Mick G]

Yawn...

[WastedFlyer]

Yawn...

I definitely wasn't aiming to entertain with fun and excitment. Some people may find plain data and information dull and boring. For example, some people may yawn at the thought of a POH (Pilot's Operating Handbook, for the uninitiated), with all those numbers and tables and technical words. Others, though, (myself included) thrive at the opportunity of knowing and understanding things better. Yet, I get this is not for everyone. Most people would rather entertain themselves playing video games, than bothering to understand the real world (particularly if understanding the real world involves having to deal with pesky numbers). To each their own, we can't all be the same.
Cheers!

[CFR]

Well I can say that I use the registry on a regular basis (pre-purchase inspections, serial number verification, aircraft history etc).
As to making statistical use of the data, as with all data mining expeditions you need to understand the confounders you may encounter. There are a number of aircraft registered that have been written off in accidents, the owner did not cancel the C of R. How many? Who knows. As well many home builts get issued a registration before they are completed. The final completion rate for home builts is ...? The big question is how many of those registered aircraft are actually flying or even airworthy?

[WastedFlyer]

Well I can say that I use the registry on a regular basis (pre-purchase inspections, serial number verification, aircraft history etc).
As to making statistical use of the data, as with all data mining expeditions you need to understand the confounders you may encounter. There are a number of aircraft registered that have been written off in accidents, the owner did not cancel the C of R. How many? Who knows. As well many home builts get issued a registration before they are completed. The final completion rate for home builts is ...? The big question is how many of those registered aircraft are actually flying or even airworthy?

Good points CFR...

Home builts are a bit of a different universe, same with ultralights, but those can be filtered out by certificate type (the Register has over 7,000 ultralights and 4,000 home builts)...

I would assume that Transport Canada would flag no-longer-airworthy or written-off aircraft (indeed there are over 3,000 records with Registration Status "cancelled"or "expired")... But you mention that some owners may not cancel the CR...? Not sure at the motives for that (I've never owned an aircraft, so this area is new to me)... If an aircraft gets destroyed in an accident, wouldn't its CR get cancelled "automatically"? Wouldn't it be a problem / nuisance for an owner with a written-off aircraft having an active/valid CR...? (I'm thinking on the lines of fees that he would to keep paying, etc.) Just curious...

Thanks for the feedback!

[ZaSz]

One thing missing from the custom reports page is a filter on base of operations (home airport).
Would be nice to have some kind of Excel file with quick filters (pivot tables/graphs?)

[WastedFlyer]

One thing missing from the custom reports page is a filter on base of operations (home airport).
Would be nice to have some kind of Excel file with quick filters (pivot tables/graphs?)

ZaSz, I agree it would be very useful... The problem is that the name of the home airports is not standardized in the database. I believe the "city airports" are transcribed directly from forms filled in by the owners, and the result is that different people will write different things for the same city. For example, as I wrote above, for the Pitt Meadows airport the database has:

CPPK - Pitt Meadows [CPPK being a typo]
CYPK
CYPK - Pitt Meadows
CYPK Pitt Meadows
CYPK Pitts Meadows
Piit Meadows
Piott Meadows
Pitt Meadow
Pitt Meadows
Pitt Meadows - CYPK
Pitt Meadows (CYPK)
Pitt Meadows Airport
Pitt Meadows CYPK
Pitt Medows
Pitts Meadows
Ptt Meadows
YPK

Each of these spellings, from a search perspective, would be considered as a "different city" by the computer, so if you typed "Pitt Meadows" in the search, the records with the different spellings would not show up. It's definitely an issue that they (TC) should sort out, and it's not that hard (I did a good clean up of the cities in BC in this database in a couple of hours, so it shouldn't take more than a couple of days, at most, for TC to standardize all the airport cities in Canada... Also, entering the ICAO code for the airports, instead of the cities' long names, would greatly minimize spelling errors)

I have the data in MS Access which is a bit more powerful than Excel for database queries / analysis, so if you there's something you're looking for in this regard give me a shout via PM and I'll let you know what I find...

[lhalliday]

My plane is an uncommon type (Beech 23 Musketeer), and of the 15 examples known to Transport Canada six show cancelled registration and/or mail returned as undeliverable. One more, while still registered, was badly damaged last year and is unlikely to fly again. This doesn't show in TC's records either.

...laura

[AirFrame]

I remember flipping through the hardcopy of the register that used to be published and distributed. I have downloaded the online database a number of times over the years out of curiosity, but in general find the online register easier to use. You can search current and historical databases online.

I found, as you did, that there are a lot of database "nuisances" like inconsistent airport names, aircraft types, etc. Once in a fit of mis-spent spare time, I normalized the entire database, removing all the inconsistencies. I remember thinking it didn't take as long as I thought it would. Wasted effort though, as there was no way to feed the data back into the master database, or any way to prevent further inconsistencies from creeping in in future. I don't even have that copy of the database anymore, unfortunately, it was lost to a hard drive crash many moons ago.

[WastedFlyer]

My plane is an uncommon type (Beech 23 Musketeer), and of the 15 examples known to Transport Canada six show cancelled registration and/or mail returned as undeliverable. One more, while still registered, was badly damaged last year and is unlikely to fly again. This doesn't show in TC's records either.

...laura

I see the 15 Beech 23 in the CCAR database (one being a 23X), yet if we include all variants of the Beech 23 there are actually 108 Beech Musketeers in the database (9 Beech A23, 5 Beech A23A, 18 Beech B23, 60 Beech C23 plus 1 Beech A23X) not counting the Model 19 Musketeer or the 23-24 Musketeer Super III

What's the mark of the one "badly damaged last year and unlikely to fly again"...? (I'd like to double check it in the database...)

If you'd like the list of those 108 Musketeers with their marks, owners etc, from the CCAR database give a PM and I should be able to sent it to you in a Excel file...

[WastedFlyer]

Wasted effort though, as there was no way to feed the data back into the master database, or any way to prevent further inconsistencies from creeping in in future. I don't even have that copy of the database anymore, unfortunately, it was lost to a hard drive crash many moons ago.

I worked for many years with data from BC's Liquor Board and, to my astonishment, the cities didn't have a code and there were all sort of different spellings, just like the cities in the CCAR (so it looks like it's a common issue with Canadian government databases...) Yet what I did was having a cross-reference table having all the multiple spellings with the corresponding "correct" spelling, so the data update process that I ran every month would look for the cities in the downloaded data and match it with the existing misspellings to get the correct, standardized city spelling... New spellings would be added to the cross reference table and were updated manually.... After a few months, virtually every possible misspelling of every city was taken care of, so it stopped being an issue... Of course I had no way to update the BCLDB master database, but for all practical purposes I had it working just fine in my local database, which is all I cared... My point is that there are ways to work around the imperfections of the CCAR database, but of course it's only worth to code all the workarounds if the database is being used regularly...

[C-FABH]

For example, as I wrote above, for the Pitt Meadows airport the database has:

CPPK - Pitt Meadows [CPPK being a typo]
CYPK
CYPK - Pitt Meadows
CYPK Pitt Meadows
CYPK Pitts Meadows
Piit Meadows
Piott Meadows
Pitt Meadow
Pitt Meadows
Pitt Meadows - CYPK
Pitt Meadows (CYPK)
Pitt Meadows Airport
Pitt Meadows CYPK
Pitt Medows
Pitts Meadows
Ptt Meadows
YPK

..... It's definitely an issue that they (TC) should sort out, and it's not that hard .....

This is classic Transport Canada, you'll find the same thing (poor, if any, continuity and fidelity of data) in most of their databases. They usually have summer students or interns processing this.

[lhalliday]

I see the 15 Beech 23 in the CCAR database (one being a 23X), yet if we include all variants of the Beech 23 there are actually 108 Beech Musketeers in the database (9 Beech A23, 5 Beech A23A, 18 Beech B23, 60 Beech C23 plus 1 Beech A23X) not counting the Model 19 Musketeer or the 23-24 Musketeer Super III

What's the mark of the one "badly damaged last year and unlikely to fly again"...? (I'd like to double check it in the database...)

If you'd like the list of those 108 Musketeers with their marks, owners etc, from the CCAR database give a PM and I should be able to sent it to you in a Excel file...

The Beech 23 (with no letters) is the first of the line and has some charming features that the later 23s don't have, like the curved glareshield that looks like it came out of a Super Cub. The later Musketeers (plus the C23 Sundowner and Beech 24 Sierra) added extra goodies.

The "unlikely to fly again" plane (http://globalnews.ca/news/2790615/watch ... n-glacier/) is FQIS. One of my plane's neighbours on the ramp at Langley is GSNY (a B23), which hasn't flown in a while and won't be flying any time soon. There are planes at Langley that haven't moved the entire time I've been flying (i.e. since 2010).

...laura

[WastedFlyer]

The "unlikely to fly again" plane (http://globalnews.ca/news/2790615/watch ... n-glacier/) is FQIS. One of my plane's neighbours on the ramp at Langley is GSNY (a B23), which hasn't flown in a while and won't be flying any time soon.

Thanks for the link Laura... I see FQIS in the CCAR database that I have (which I downloaded in March this year), a Beech 23 indeed, and it also shows the owner's name as stated in the Globalnews report (Zbigniew Jedynakiewicz), so the data for FQIS seems all correct... You and I may think FQIS will never fly again, but in the report it's pretty obvious the the owner thinks otherwise...

There are planes at Langley that haven't moved the entire time I've been flying (i.e. since 2010).

I'm mystified by these (apparently) "abandoned" airplanes in airports... At Delta Heritage there's one Cessna 172 which seems to have been grounded for many years, too (it literally had moss growing on it's sides...) I can only imagine they're kept due to a) grounded pilot/owner due to health reasons, b) financial difficulties that prevent the owners to keep the airplane airworthy or c) legal disputes regarding debts, inheritances, divorce etc.... In most cases I'm sure the owners hope "one day" to get back to flying again in their airplanes, so from the perspective of TC I guess there's no reason to cancel their registration...

[lhalliday]

Thanks for the link Laura... I see FQIS in the CCAR database that I have (which I downloaded in March this year), a Beech 23 indeed, and it also shows the owner's name as stated in the Globalnews report (Zbigniew Jedynakiewicz), so the data for FQIS seems all correct... You and I may think FQIS will never fly again, but in the report it's pretty obvious the the owner thinks otherwise...

He is entitled to his opinion. As is usually the case, a lot of information didn't make it in to the media reports.

...laura

[AirFrame]

I can only imagine they're kept due to a) grounded pilot/owner due to health reasons, b) financial difficulties that prevent the owners to keep the airplane airworthy or c) legal disputes regarding debts, inheritances, divorce etc....

d) The owner has tried to sell it, but is asking well over what the market will bear for it, because they have seen the price of a new 172.

[Old Pilot]

I have used the data more many years. I use crystal decisions to manipulate the data.