Trucks Crash Readme Updated 02/04/14 The Department of Transportation's truck crash file includes all accidents involving commercial carriers, as recorded by law enforcement in each state. Each state submits a report to the federal DOT that consists of all accident reports gathered by local and state law enforcement agencies. The data includes accidents from 2000 to Nov. 7, 2013. Starting in 2000, the data were compiled with a new record layout and divided into three tables: crash14, carr14 and event14. You can use the field CRASH_ID to join the tables. Jeremy Cotton, the data contact, said the unique identifier is CRASH_ID in crash table, CARRI_ID in carrier table and CRASHEVENT in the event table. In the Tables folder: -crash14.csv: 1,911,636 records This is the main table and includes the crash date, place and fatality information. For most of the records, you can find detailed information, such as driver's status (i.e. normal, sleepy or drinking), road conditions (i.e. wet, iced or dry) and weather conditions (i.e. rain, snow or fog). The one thing you cannot do with this data is count with accuracy the total number of accidents because of poor documentation of multiple-vehicle accidents. -carr14.csv: 1,911,636 records This table has information about the names and locations of truck companies that own the trucks involved in the accidents. This table can be used to identify a carrier company in an accident. -event14.csv: 2,554,243 records This table includes a description of the crash, such as whether the vehicle overturned or collided with a bicycle. A crash could involve multiple events that were listed in time order with sequence numbers. (i.e. first the truck hit the guardrail, then went across the median, then into an oncoming truck). -CrashDoc.doc: Codes, definitions and layouts from the Office of Motor Carriers. Note that the document includes blank pages. -Layout.xls: Record layouts in Excel format -Fieldname.xls: It lists all the new and old fieldnames for better understanding of the old and new layouts before and after 2000. The Database Library received data for 1988 through 1999 in a different layout. This list can help journalists who look at both layout records. -_fipscnty.csv: A table listing geographic codes for states and counties -_fipsplce.csv: A table listing geographic codes for states and places. This database is helpful for giving you a starting point to look up further information on a specific carrier or a crash. You can get more information on the crash by talking with the department of transportation in the state where the crash occurred. That would also be the most accurate crash information available, according to the DOT. By linking the truck accidents table to the truck census file (also available from NICAR) you can discover more detailed and useful information about the carrier. To link these tables, use the census number for each vehicle, also known as the U.S. DOT number, (in the Crash05.dbf table it's the field called "DOT_NUM"). To link the two data, you must use the DOT_NUM (crash table) and CENSUS_NUM (census table) fields. NICAR also added the Federal Motor Carrier Safety Administration Truck Inspections database to its collection. Records in the Inspection file are sent to the FMCSA by state and federal enforcement agencies. You can link the truck accident table to tables in truck inspections (such as violations, inspections, carriers, etc) by using the "DOT_NUM" field in each file. This data is not necessarily restricted to the United States. It is possible to find many carriers from Mexican states as well as Canadian territories in this database. This does not mean that the data includes accidents occurring outside of the United States, rather it includes all incidents whether involving national or international carriers within U.S. borders. To find accidents involving international carriers, link the Truck Crash table with the Truck Census table (see above). The Census table includes the PHY_NATN field, which is where the nationality or general geographic location of the carrier's principle office is found. This field defines whether a carrier is from the United States, American territories, Canadian territories, Mexican states or Central American Nations. Possible states and territories are listed in the state abbreviation appendix, (found in the codes file under the codes folder of this CD). Notes about the truck crash data records: RPTNUM, DOT_NUM, DOCKETNUM, and SNETID are not unique numbers. For example, there are 6 records with the same crash report number (RPTNUM) "TN0007540305," which means the 6 records represent 6 vehicles involved in one accident. Another example, in the field of DOT_NUM, you will find the value of "54283" in 5,161 records, which means these vehicles belonged to the same carrier--Swift Transportation Co Inc. (A vehicle's census number will change under different company, but VIN is unique and remains the same.) Records that share the same DOT_NUM are registered under the same company. The crash file may contain multiple records for a crash. Separate reports are entered for each commercial motor vehicle involved in a crash. These multiple reports can be distinguished by the format of the report number: the report number will be the same except for the last character, which may be "A" for the first report, "B" for the second report, "C" for the third report, etc. So, if there was a three-car crash involving a semi, a bus and another semi, you should find three records in the database where the report number (RPTNUM field) is exactly the same except for the last character, which will be an A for the first report, a B for the second report, a C for the third report, and so on. For example, "WY99176K062A", "WY99176K062B", "WY99176K062C" will be shown in the field of crash report number. This is supposed to ensure that all records are unique by their report number. However, the data is incomplete. If you spot a record with a C at the end of the report number, for example, and you want to find the other two vehicles involved in that crash, they don't come up sometimes. This varies mostly from state to state. For example, the Texas data comes up wrong more often than other states such as Alabama. Also, you will notice numerous instances when a record will appear with an A, but there is no B or C record. So far, the DOT has no answer for why this occurs. Although this number should be unique, there are several instances in which the RPTNUM is exactly the same, but each record refers to a separate report. Some records are not in consistent format in some fields. For example, "YELLOW FREIGHT SYSTEMS, INC" was in several formats -- "YELLOW FREIGHT SYSTME INC/IN/INC." in the field of carrier name. As a new carrier is added to the national census database of trucks, the next sequential number is assigned to that carrier for a Census Number. However, not every record in the crash table links up with its corresponding record in the census table. Linda Giles, of the Department of Transportation, said this is because states compile all crash data on their own and may assign a different census number than what the U.S. DOT assigns, and therefore, they won't match up. Also, many census numbers are just plain wrong. While very useful, the truck crash table also has some flaws. Much of the data has typos and other mistakes that make it very difficult to get accurate counts for cities, states, and more. For example, there are at least 25 different spellings for Philadelphia found in Pennsylvania in the Carri05 table. Some are just abbreviations (Phila. or Phil.) while others are even more convoluted (Philaddelphia, etc.). Also, it�s wise to check the details. While some data elements like dates seem to be accurate overall, some other pieces of information -- such as the number of vehicles involved -- can be underreported. The SNET_NUM field lists the version of SafetyNet software that was used for each record. SafetyNet is the program used to input this information into Computing Technologies' system. Valid entries for this are 10, 20, 51, 60, 70, 80 and 90. Another word of caution: Please note that a lot of fields contain blank records. There are 628 records of value "0" in the SEQ_NUM field, and all of them were reported from North Dakota. Jeremy said it should be a reporting error from the state. The value of "P" in the STATUS_CD stands for "potential resolution." The agency redacted the fields containing information on hazardous materials for the years 2000, 2001, and 2002, citing security reasons. For the 2005 update, this information is back in (for 2003, 2004, and 2005 through 10/31) in the haz_num, haz_mat, and haz_id fields. The fields are gone again for 2006 and 2007. (An additional, related hazardous material database is also available from NICAR.) If you have any concerns regarding the fields below or any fields in the truck accident table, please call Frank Guzman at Computing Technologies, (703) 280-4001 or the Federal Highway Administration, (202) 366-2971. For more information about codes, go to www.itl.nist.gov/fipspubs/index.htm These files are provided in CSV format: comma-delimited and double-quoted. The tables are too large to open in Excel, a database manager such as MySQL or Access is required. If you are working in Microsoft Access, here's how to import the files: 1. Copy the files to your hard drive. 2. Open a blank database in Access, name it and save it. 3. Inside the new database, in the File menu select "Get external data" 4. Choose "Text File" in the "Import & Link" section. We recommend that you "link" these files, as Access has a 2GB import limit, and these files collectively equal about that. If you import, Access might be sluggish. 5. A wizard will ask you to locate the file(s), and walk you through the steps to import: You must tell Access that the files are delimited with a comma (and only a comma), that the first row contains field names, and that the text qualifier is a double quote. For data types, see the Layout.xls. *********************************************************************************************** The following is from the DOT's record layout. It may answer further questions you might have: *********************************************************************************************** The Federal Motor Carrier Safety Administration (FMCSA) is the agency of the U.S. Department of Transportation (USDOT) responsible for monitoring and developing safety standards for commercial motor vehicles operating in interstate commerce. The FMCSA Crash file contains data from State police crash reports involving drivers and vehicles of motor carriers operating in the U.S. Each report contains about 80 data elements pertaining to the motor carrier, driver, vehicles, and circumstances of a crash. BACKGROUND Under the Motor Carrier Safety Assistance Program (MCSAP), a grant program administered by FMCSA in cooperation with the States, FMCSA has implemented a crash reporting system based on State police crash reports which are electronically transmitted from the States to the FMCSA. The latest phase of this implementation is based on uniform crash data elements developed through the National Governors Association (NGA). The data collected are entered into a microcomputer-based system called SAFETYNET that allows States to do analysis on all motor carriers in the State (interstate & intrastate) and to transmit these data to the FMCSA Headquarters Motor Carrier Management Information System (MCMIS) via telephone lines. Due to privacy restrictions, driver data are not included in any crash files released to the public. The crash file may contain multiple records for a crash. Separate reports are entered for each commercial motor vehicle involved in a crash. These multiple reports can be distinguished by the Report_Seq_Number field. The crash files are made available through the Department of Transportation (DOT), Federal Motor Carrier Safety Administration (FMCSA), 400 7th Street S.W., Washington, D.C. 20590. Police-reported crash data are available from 1993 to present. ************************************** Stories from the IRE Resource Center: ************************************** If you are interested in one of the following stories done on this subject, you can call the IRE Resource Center at (573) 882-3364, and tell them the story number you are looking at. Tipsheet #2420 This tipsheet is a comprehensive guide to reporting on the trucking industry. It begins with a list of questions to ask at the beginning of an investigation, like, "Did the truck driver have a valid Commercial Drivers License?" Next, the tipsheet lists some pieces of information that reporters should be able to find before deadline, that could make their stories better. Then, the tipsheet lists possible follow - up investigations; these are more long-term projects and might make for good enterprise stories. There is a description of how to go about each investigation. Finally, the tipsheet ends with a list of contacts and government agencies that could be helpful for a reporter writing a story about the trucking industry. Tipsheet #2814 Nancy Amons describes how to use CAR as a tool to beef up general assignment stories. Instead of just reporting on one isolated incident -- like a trucking accident or a botched 911 call -- you can do some database analysis to see how often those sorts of incidents occur. This will add more depth and context to your story. Uplink May/June 2002 Kansas City Star reporters Greg Reeves and Judy Thomas did a three-day series on the effect driver fatigue played in truck accidents. In order to do the story, they had to acquire and analyze multiple federal databases. While analyzing the data, they came across numerous pitfalls that had to be crossed. Story #23174 The Dallas Morning News found drivers with "poor safety records and histories of drug and alcohol abuse," poorly inspected and maintained trucks and lax enforcement of safety laws are the main problems affecting the truck driving industry in the state of Texas. The Dallas Morning News investigates, spurred by the case of Miroslaw Jozwiak, a trucker who falsified his log reports before causing a fatal accident, which he survived. The stories of those who perished in the crash are told, as are those of the people who survived the crash. Story #15367 WLS-TV of Chicago began it's investigation by looking at a truck vs. van accident in which 6 children were killed and the truck driver obtained his Illinois commercial driver's license under "unusual" circumstances. WLS-TV learned that truck driver's licenses in Illinois were routinely and illegally being sold by Secretary of State employees to unqualified, untrained drivers. Story #14966 This Winston-Salem Journal series examines the safety records of the trucking industry in North Carolina. Extensive problems, gaps in regulations and relaxed enforcement caused serious issues. Most of the state's trucking companies have "unsatisfactory" safety ratings from federal inspectors. Also found the number of roadside inspections by Division of Motor Vehicles has dropped; rare check-ups; minimum regulation. Story #16152 "A six-month Dateline investigation revealed an extraordinary number of eighteen-wheelers driving the nation's highways with serious defects. We searched accident and inspection records of trucking companies throughout the country and spent several days at truck inspection stations and found forty percent of big rigs were so dangerous they were ordered off the road. Incredibly, the main defect we found was brake problems." Story #12140 The Morning Call conducted a computer-assisted investigation finding that deaths from heavy truck crashes rose in 1993 and that faulty equipment, tired drivers and unrealistic schedules contribute to lax safety. Story #8795 Los Angeles Times conducts a data-base study of 68,000 hazardous materials incidents from around the United States, and finds the number has risen 37 percent from 1982 to 1991. They also find that injuries to people as a result of truck spills rose 374 percent, and almost all of the deaths--106 out of 108--involved tanker trucks. Further digging showed gasoline, ammonia and sulfuric acid are the most dangerous liquids transported. Other stories give accounts of a railway accident that dumped weed killer into the Sacramento River, killing virtually every organism along the river for miles and the death of a whole family as a result of a gasoline truck accident, Sept. 20, 1992.