Another notes style post on the remains of the LGR data.

How fresh is the data?

The last month in which data is found in the LGR dump is April 2012, which led me to question the frequency of entry;  how many registrations share a common “Registration Date” column, to give an indication of how busy the RCMP was at certain points in time

The earliest record is Thu, 26 Nov 1998 and the last record is Sat, 21 Apr 2012.

lgr-per-month

The spikes correspond to activities coming into force as a result of Bill C-68, where the regulations were passed in March of 1998, being phased in starting December 1, 1998.  At it’s peak, the Firearms Program processed 67519 registrations in a single day on January 18th 2002, marked with other points such as 53214 registrations on November 13th 2002 and 47857 on January 10th of 2002.  Of the 4863 days of registry data, there are 142 days in which the Firearms Program processed 10,000 or more registrations.  The LGR processed, on average 1648 registrations per day, or 2.44% of it’s peak processing capacity.  If we exclude those 142 busy days (days with more than 10000 registrations processed), the daily average falls to 1176 registrations.

lgr-by-year
On average throughout the life of the LGR program, 534,000 firearms were registered annually in Canada.  The tailing years of the program tell a story of a more routine, but slightly growing system.  From 2004 to the end of program, annually, about 370,000 firearms were registered, trending for year-over-year growth of about 65,000 registrations per year.

Unknown is what the protocol ever was for removing a firearm from the LGR;  while the certificate is not in the data, any statistic on the rate of de-certification is as-such not captured in these scraps.

The tape shows a system that got busy in 2000 and was really crushed from 2001 until late in 2002, finally stabilizing in 2004 in a routine.  This 3 year period accounts for 4.8M issued certificates.

Data for 2012 is incomplete representing about 30% of the year;  crudely extrapolating from this sample, Non-Restricted certificates could have reached 700,000 (seems high), 6,000-7,000 Prohibited, and 50,000 to 60,000 Restricted.

In the routine years (2004+):

On average 31,300 Restricted certificates were issued per year, totaling a program lifetime of 556,639 Restricted certificates issued.

On average 7,000 Prohibited certificates were issued per year, totaling a program lifetime of 198,879 Prohibited certificates issued.

On average, 327,100 Non-Restricted certificates were issued per year, totaling a program lifetime of 7,260,663 Non-Restricted certificates issued.

More to come…

Advertisements

Back on May 15th Global News had a piece that included a downloadable link to scraps of largely redacted LGR data. I downloaded the file on a whim and have been sitting on it, thinking about what could be done with it’s contents.  As I write, I can’t see this being a one-of article, but rather the first of a series recounting what is uncovered in it’s contents.

The file is of tab delimited format, is malformed and generally too large for most people to deal with.  It’s been sitting in my download folder for months now and only recently have I had a chance to play around with it a bit.

Sitting in a flat file is pretty useless, so as a first task, I created a MySQL schema and a table to contain the initial data-set.  The file contains 8,016,342 clean rows, and at a quick glance, near 100 that are malformed feeding next row records into fields on a trouble row, like the line breaks aren’t in the right spot.  Being statistically insignificant, I’m just going to ignore the corrupt rows for now.

As the file is so large, I just exported a few rows from the top to cast what I thought would be reasonable data-types for my first go at this table.  The rows are structured into 16 tab delimited columns

  1. Make of the firearm
  2. Model
  3. Manufacturer
  4. Type (One of:  Rifle, Shotgun, Handgun, Commercial Version, Other, Combination Gun, Submachine Gun, Not Available, Machine Gun, Grenade Launcher, Mortar, BC, AIR PISTOL, , ON, Cannon)
  5. Action (One of: Semi-Automatic, Bolt Action, Pump Action, Lever Action, Hinge Break, Single Shot, Revolver, Multi Barrel, Air, spring or gas, Other, Converted Auto, Full Automatic, Not Available, Pistol, V4, , P9)
  6. Classification (One of: Non-restricted, Restricted, Prohibited, Unknown, Individual, , Antique, NOT APPLICABLE)
  7. Barrel length in mm
  8. Calibre (Don’t even get me started on this)
  9. Capacity
  10. Date of Registration
  11. Province of Registration
  12. Postal Code Fragment (first 2 of the FSA)
  13. Registration Type (Business, Individual or Museum)
  14. Stolen Date
  15. Lost Date
  16. Recovered Date

A table was created, based on the data observed in those first few rows from the top of the file, and I proceeded to write a quick script to read the file in chunks and write them into the database;  easy enough I thought.

Quickly I came to realize I was up against a data product born of bureaucracy, lack of forethought and oversight.

The import stops;  some of the datatypes selected were not suitable for the data contained further into the file and MySQL is moaning about it.

Take “Capacity” for example, one might expect this value to be a positive integer, affording plenty of space for the number of bullets a firearm could contain.  Wrong.  While generally, the number in the “Capacity” column seems sane, there are 166 distinct values for this ranging from NULL to 861 including a textual reference named “Multi shots”.  Astonished, I revised the “Capacity” to support character strings.  A little trial and error and most of the column sizing issues were sorted out and the import proceeded

It’s a big data-set and my PC is groaning from obsolescence and competing memory requirements; but I am able to freely query the data and run some aggregate functions.  What follows are the first few impressions.

The “Calibre” is simply bewildering; in this file there are 94,798 different calibres listed. A two-worksheet Excel file to make your eyes bleed.

LGR Calibres

For the sake of illustration, lets focus in on plentiful .22LR calibre.  On the second worksheet named “.22 Confusion”, there are 23013 rows that contain “22”;  while some are clearly different calibres (223 REM, .223, .223 Remington etc. etc.) and rightly included as part of the query, the confusion at the Firearms Centre is now clear.  The data captured was largely free-form, with probably nothing more than type and size limits applied.

Little or no effort to normalize the inbound data has been performed;  either at the hand of data entry, the people working the phones, or the venerable “verifier network”.  The data was entered verbatim and, as imported.

Calibre Highlights:

CHAMBERED 308 WINCHESTER
3 BARREL SET
24 ESTIMATED
7MM HIGH POWERED RIFFLE
12 CALIBRE CHOKE
77MM REM MAG

It would be amusing only if it didn’t get much worse.

The column of “Make” is comprised of 163,616 records… the “Black & Decker” must have been removed.  Duplicates abound, in some cases several dozen different spellings for the same manufacturer, some entered in french such as “Smith et Wesson”, and even a broad category of “OLD 22, Unknown”, to “OLD 303, DOES NOT SAY ON IT”.

Take one of the nearly 700 Springfield name variants.

This is only the data they didn’t redact!

These scraps are a mess, but should prove to yield some interesting data.  For example, overlaying the counts of firearms by type by postal code fragment yields an amusing map of where guns are found throughout the country.

Green = Non-Restricted

Blue = Restricted

Red = Prohibited

http://www.gpsvisualizer.com/display/data/20151012210838-23012-map.html

Now forgive the fact the pins are stacked on top of each other in many cases, I’ve just done a quick and dirty map to bring the data to life somewhat and will be refined in time.

I can think of a bunch of questions to pose of the data, but I’m getting the feeling that this whole billion-dollar-boondoggle was just that, the embodiment of garbage in and garbage out.  Other than curiosity, I don’t feel any results, even with dated data would be worth much at all.

“Restricted” Gun Purchases Plunge to Three-Year Low.

 

TheGunBlog.ca went down the same rabbit hole looking at RCMP date, coincidentally at the same time our latest post went up.

Great insight into that last restricted numbers report.

 

 

RCMP Facts and Figures…

Posted: March 12, 2015 in Firearms News

I’ve been grabbing the RCMP Facts and Figures data in order to compile various aggregate outputs that aren’t otherwise available.

A first output:

Using the published “Firearms Registration” data by province, we can peer into the data at a national level to see what it looks like in aggregate. Trendlines are linear.

CDN_registration_trends

Some observations:

  • The RCMP did not publish a December 2009 report.  Doubted my data and sure enough, they re-used September 2009.
  • Prohibited class firearms are slowly vanishing.
  • The number of Restricted firearm registrations has grown from 440,430 in September 2008 to a peak of 721,764 in September 2014; 281,334 additions over 6 years (~47K per year).
  • December 2013 marked an odd data point showing an increase in the number of Prohibited class registrations.

The March 2014 reclassification of 12,000 CZ 858 rifles isn’t reflected;  these are in limbo, reclassified from Non-Restricted to Prohibited, but as the amnesty exists individual firearms may not have been registered as Prohibited with the RCMP.  These firearms may have changed hands post-LGR and will be difficult to reconcile.

In the wake of the October 22nd shooting in Ottawa, the internets have been a-buzz with chatter surrounding the Long-gun Registry and whether the data it contained would have been of any assistance in preventing the senseless actions of the day.

Yes.  The LGR (Long-gun Registry) was abolished in the parts of Canada that respect Federal Law back in November 2011. The Restricted and Prohibited firearms registry still exists and is busier than ever as evidenced by excessive wait times and turn-around on documents. The bill C-19 re-wrote parts of the FA (Firearms Act) to reflect this and other required changes to keep the act cohesive. The data contained within the LGR contained a link between a Firearms Licence,one or many Registration Certificates issued on a 1:1 basis to each registered firearm.

  • Each long-gun has a serial number (most do, some don’t)
  • Each long-gun is issued a certificate
  • Each long-gun is assigned to a licence
  • A licence is held by an authorized/trained person

Without rehashing the entirety of FA (Firearms Act), there are 3 classifications of firearms in Canada.

  1. Non-Restricted.  Most rifles and shotguns
  2. Restricted: Most handguns and other rifles and shotguns deemed restricted (Registered)
  3. Prohibited: Automatic firearms, some compact pistols and pistols of certain calibers. (Registered)

The LGR was focused on the registration of Non-Restricted firearms, which weren’t tracked and distributed widely among the citizenry from coast-to-coast under the previous FAC system.

In a perfect world of citizen compliance to government command, everyone would have obtained a licence, registered or surrendered their guns and complied with transfer protocols when buying, selling or trading firearms.   Lost and stolen firearms are to be reported and these missing guns would be flagged to CPIC (a national crime information network) .  Infracting Licence holders are severely punished with a variety of charges and their firearms confiscated until their innocence is proven.

Everything is great. We know where all the guns are and that only authorized persons can access and use them.  Licence holders are terrified and keeping their heads down. Furthermore, in this fictional orgasm of government control, Quebec has even more draconian measures in place requiring ATTs (authorization to transport) on all firearms, allowing only rifles and shotguns to be carried in specified areas, as indicated on the ATT.  Magazine limits are capped at 3 rounds on everything.  Semi-automatics are banned, or adapted with a bolt-hold device that requires a separate action to chamber the next round.

The events of October 22nd never occurred as the individual had taken the training, passed, and applied for a firearms licence, but upon checking his references, it was discovered that he had a criminal history and was disqualified from owning firearms as part of a previous sentence.  He was not issued a PAL.

He was unable to acquire the lever-action rifle for use that day.

There is no utopia.  October 22nd would have happened with or without the registry, here’s why, and it’ll be a shocker to some readers:

Criminals do not comply with the law.

They don’t register their guns.  They don’t care about magazine capacity limits.  They don’t care about carrying laws.  They don’t care if the guns are stolen.  They don’t care if they sell or give a gun to someone who doesn’t have a valid licence.  I can keep going, but suffice to say, murder is illegal, and some people still do it.

How did the shooter get the gun?   He probably didn’t have a PAL, the RCMP will certainly confirm this, if it’s fact. He probably didn’t buy the rifle at LeBaron. If the firearm was ever in the now-defunct registry, it would show the certificate and to which licence it was attached. With or without the registry, indeed, the firearms current location is now known.  They’d be able to go back and determine where it came from, but unfortunately, this knowledge does not recall the expended bullets  and their ensuing consequences.

The means by which he came into possession of the firearm is a whole other mystery fraught with speculation.  Was it stolen?  Was it illegally sold to him?  Was it imported illegally?  The fact he opted to attack Parliament with a 100 year-old (model) lever-action rifle suggests that he didn’t have much choice and went with the best available option at the time.

The internet buzzes with: “We register our cars?  That works, why can’t it work for guns?”

Right, because criminals don’t change VIN numbers, steal licence plates or run chop-shops either.  Vehicle registration only goes so far; vehicles enter and leave jurisdictions, get de-registered for legitimate reasons, and is just as daunting to keep correct as vehicles out-number firearms and wear out much sooner with varying fates.  Vehicle registration is mostly for revenue generation anyways, mitigating fraud and theft are secondary features.

All laws assume compliance and infractions, when charged and prosecuted are sentenced on a sliding scale of severity, which aspires to deter infractions in the first place.

In a recorded event put on by OFAH, Ontario’s CFO, Chris Wyatt relays a situation where a Restricted licence holder was buying multiples of the same model of handgun for delivery to a gang.  He was caught, but if these transfers don’t trigger flags within the registration system, guns can be getting into the wrong hands.  These are cases of legal guns, becoming illegal.  A tightly managed registry still leaks and is fraught with bureaucracy to those familiar with restricted transfers and transport permits.

Illegal guns pour in from the US;  news reports the occasional bust or worthy haul, and like drugs, they’ll just find a way to get them in country because people are demanding them and are willing to pay top dollar. We continue to spend millions to nanny licence holders, manage registries, issue transport permits all while illegal guns keep finding their way in;  the priorities seem all wrong.

We’ve let it happen.  The law abiding citizen can’t compete, the field is slanted. We don’t even give bullets to the soliders guarding our National monuments!  We don’t trust our soldiers with bullets for their C7s on Elgin street?  How does no-one recognize a rifle shot and step-up their levels of alertness?  They don’t even know what it sounds like.

Everyone obeys the law right?

The firearm has been demonized, made an instrument of terror and right hand of the “terrorist” (Islam or otherwise, this guy was off his rocker and un-well IMO). Watching video of my fellow countryman scatter on the front-lawn of our Nations Capital, unable to defend themselves at the sight of a rifle sickens me to my core.

You cannot register the intent of a man’s soul, it’s up to the rest of us to be just, measured, responsible while looking out for each other, and valuing life despite our differences.

The AR-15 is a semi-automatic rifle chambered in 5.56mm designed by ArmaLite in the late 1950’s.  The design was acquired by Colt and entered into military service as a select-fire (fully automatic capable) rifle known as the M-16.

Colt then offered the civilian version, the originally designed AR-15 for sale in 1963.  The name “AR-15” is a Colt trademark, but many variant patterns of the rifle are made and sold by other manufacturers.

The M-16 and Ar-15 look nearly identical to the untrained eye.  The Hollywood use of the M-16 is seen in many movies, and usually accompanied with depictions of endless automatic fire. This, in plays a part in the confusion and misinformation.

So, for the most part, AR-15’s are semi-automatic rifles chambered in 5.56mm or .223 Remington

Briefly, there are SOME AR-15’s that had been converted to automatic firearms, but these are limited and reclassified by the ATF as of May 1986;  new AR-15’s cannot be legally converted to automatics.   These are AR-15’s that are essentially M-16’s now and their ownership is tightly regulated.  These, and M-16s are “Prohibited” in Canada, and simply unobtainable.  AR-15’s are Restricted firearms in Canada.  Obtainable, but essentially range queens.

The “Assault Rifle” label is incorrectly applied to both of these rifles interchangeably, but the reality is, only the M-16 is a true assault rifle.  The AR-15 is a semi-automatic rifle that is unfortunately named “AR-15”.  Maybe the civilian version of the “AR-15” should be called the “SR-15”, meaning “Sporting Rifle – 15”.

File:M16A1 brimob.jpgFile:1973 Colt AR15 SP1.jpg

Whatever happened to asking questions and civil dialog?    Are peoples emotions so in control of them that they queue jump the ladder of escalation and resort to shooting to get their point across?    Is it because we live in a world where immediate gratification is become so common place that we don’t think of consequences any more?

In today’s world, information, communication can be obtained and exchanged in real-time, we don’t have to wait.  Not that long ago, if you didn’t want to use the phone, you could write and mail a letter.  Depending where it was going, this could take some time. A reply would be at least as long as the trip there.  Waiting was a fact.

I remember living overseas, ordering parts for an R/C car.  It would take months to get a catalog from Tower Hobbies.  You had time to save money, to really think and fantasize about what you wanted, how you’d build it, dream of running it, until one day, the catalog arrives!

You fill out your order, run to the Post Office with your Deutsche Marks to get a US dollar Money Order, that you’d then send back, snail mail to Tower.  More waiting, longer waiting… driving yourself and everyone around you stark mad.  Your family wants the order to arrive so they can get some relief.

No shit, 4 to 6 months later, you’d get a package, and no matter how mangled the box was, you were happy that it had finally arrived.

This was a fact of life.  You had to wait for stuff.

Now that real-time (or near real-time) we’re not used to waiting.   We don’t go to the bus stop and wait anymore. The transit system has the buses monitored by transponder, and one way this data is used is to inform an “App” so you can monitor the bus, and set out for it without additional waiting. Handy, a good use of existing data, and informs it’s customers.

We don’t give it a second thought.

A thought crosses our minds, “I wonder what so-and-so is doing?”.  What used to be a passing whim; probably because you were mowing the lawn would get deferred until you finished your job, cleaned up and called so-and-so on the phone inside the house.   Today, we reach into our pockets, grab a rectangular device that we tap on, and if so-and-so receives notification of our message, and replies, we can receive an answer to our query in near real-time.

We have “Apps” that capture peoples “Tweets”;  a deluge of disjointed thoughts, some purposeful, some for amusement, some are disgusting; like people, they come in all shades.  Now the collective idling is spent airing, what seems for most, to be their inside voices.  It’s both re-assuring and disturbing to realize now similar we all are.

It doesn’t stop with technology;  you can drive up to a restaurant, order via intercom system, and have your meal ready to go to your waistline in under 2 minutes.   Hunger can no longer be delayed, and people eat till they’re stuffed rather than satisfied.

Everything is becoming much more relevant, more here-and-now, more trusted and the consequences of this usage/application are never dire enough for anyone to really care.

This lack of care and a desire for immediate gratification have paved the way for how things are with guns today, especially pointing the finger to our friends south of us, the United States.   The cycle for immediate gratification when emotions and firearms are added to the context is disastrous.   As humans we are both gifted and cursed with emotions, and through observation, there are seems to be two types of emotional people;  those that are driven by their emotions, and those that are guided by there emotions.  The difference is found between the words “driven” and “guided”.    One of the challenges we have as humans is to gain a mastery over our emotions, and in the authors opinion be “guided” by our emotions, you as the human being must be in control, no matter the influence.

The immediate gratification has slipped into the ranks of the police, where at one time policing skills employed communication, trust and a caring for the people they have sworn to protect has seemingly eroded into a melee of suppress and detain by any means necessary.  Countless instances of police dash cam footage show officers escalating use-of-force in situations that didn’t call for it; often resulting in injury to and sometimes death of the subject.    Use of the Taser has replaced policing skills and is the first offender on the list of Shoot First, ask Questions Later.   The application of Tasers within policing has in-fact done more harm to the public’s view of policing in general, which has lead to escalating civil disobedience as police are seen as no-longer upholding their original mandate. This feeds a self-fulfilling prophecy.  It feeds an emotionally driven police officer who needs to have a situation resolved immediately, side-step the basics of dealings with a human being, treats that person like an animal, and wonders why that person wants to rip his face off.

The media reporting of shootings in the US seems to be on rise since the Newton attack.  Is it just the reporting is wider now given the gun control agenda or is it par for the course?  80 deaths a day by firearm in the US is the widely reported figure.

Now we have our ingredients for this casserole:

1 Part desire for immediate gratification

1 Part lack of care for consequences

1 Part Policing role-model

1 Part emotionally driven reaction to life events

1 Part media coverage

Make sure you started cooking it on December 15, 1791 ensuring that firearms are available and plentiful.

The US can add all the firearms laws it wants, it won’t change a thing.   People need to change.  Society needs to change. Firearms are not tools used for everyday problem solving.    We need to stop this spiral of devolution, soon we’ll be back to grunting and crushing each other’s skulls in with rocks.