Thursday, December 20, 2012

NJ Motor Vehicle Crash data on-line

At last night's New Jersey Foundation for Open Government (NJFOG) meeting, I learned that New Jersey keeps a comprehensive database of motor vehicle accident data on the Internet here.   I'm posting this for those might find it useful to know, for instance, how many school bus accidents occurred in a given county or town within a given year.

I played with the data for a while to see if it might help me isolate accidents in which government vehicles were involved.  Just for fun, I decided to see how many car accidents took place in Neptune Township (Monmouth County) during 2011 in which at least one of the vehicles was a police car.  I found ten such accidents and have listed them in the PDF file I've placed on-line here.  If I wanted to, I could now OPRA each of the accident reports to learn more about what exactly occurred in these accidents.

I didn't need any special software to do this, I used a text editor, a text sorting program, both of which I got for free off the Internet, and Excel (which I already have) to format the PDF file.  Without getting into too much detail, here are the basic steps I took:

1.  Went to the above listed site and downloaded the "raw data" from Monmouth County in 2011.  I downloaded two files, "Accident" and "Vehicle."

2. Using the "Vehicle Table" and "Accident Table" on the same site, I learned the various column positions within each file at which certain data fields began.

3. Also at the site, I looked up "County and Municipal Codes" and learned that Neptune Township's code is 1334.

4. I sorted the "Vehicle" file on columns 4-7.  Then I opened up the "Vehicle" file in my text editor and deleted all the accidents that happened somewhere other than 1334 (Neptune Twp).  This gave me a more manageable file to work with that contained only vehicles involved in accidents in Neptune Township.

5. I sorted the resulting file on columns 130-131, which is the two digit code for "Special Function Vehicles."  This allowed me to filter for police cars ("02"), fire/rescue ("04"), ambulances ("05"), school buses ("09"), etc.

6. I then boiled the file down to just 10 entries in which police cars were involved.  I then searched the "Accident" file for each of the unique "Police report number" from those 10 entries to get more information for the attached table.  If I wanted, I could have searched the "Driver" file in the same manner and gotten other information, such as the drivers' dates of birth, whether summonses were issued, etc.

There are probably better ways to do this, but this is what I can do with my limited computer skills.

No comments:

Post a Comment