NASA AVIATION SAFETY REPORTING SYSTEM README Data updated March 2010 The National Aeronautic and Space Administration's Aviation Safety Reporting System database consists of anonymous reports about aviation safety. Anyone is eligible to file an ASRS report, including air traffic controllers, pilots, flight attendants and passengers. Pilots appear to be among the most frequent filers. All reports are submitted to NASA and entered into the database, which began in 1988. The data was updated by NASA in November 2009 and contains records current through September 2009. There are 153,257 voluntarily reported events. Most reporters use this data to identify particular incidents to use as examples in their stories and gather quantifiable data from other aviation data. The reports are anonymous. They do not include the name of the filer. Reports may include month, year and day of week, but not the exact date. They do include location. They do not include flight numbers. Some journalists have been able to get more exact information about ASRS entries by looking at various FAA databases, talking with sources, and checking news reports. NASA keeps the database in a report format rather than traditional rows and columns. Note that for each record there will be rows representing different fields, but not every record will have a value for every field. ************************************ CONTENTS OF THE DVD OR DOWNLOAD ************************************ Tables: MAIN.DBF: 7,519,616 records. NASA documentation refers to this as FixField_Dump REMARKS.DBF AND REMARKS.DBT: 308,122 records. The narrative and other supplementary data of all years available, including the latest. NASA refers to this table as Text_dump. Static.dbf: This is the static_attribute_value lookup table provided by NASA. The 2009 update was the first year this table was provided. Please read under "About the data" below for more information from NASA on this table. NICAR documentation: README: The readme written by NICAR LAYOUTS.txt: Record layouts for the MAIN tables and the REMARKS table, written by NICAR. LEGAL.txt: Legal notice, from NICAR. Documentation from NASA: ASRSDB_Dec09.doc: Detailed information written by NASA about ASRS and the tables. Entities are discussed. Encode_Decode.pdf: ASRS abbreviations - A list of ASRS abbreviations used in report narratives. Changes_to_ASRS_Data_Structure.doc - Recent changes to ARSR. Taxonomy_Mapping_of_ASRS_Data_Internal_v8.xls - Taxonomy of ASRS data Contact NICAR if you are missing anything or have any trouble accessing the data: datalib@ire.org or (573) 884-7711. Questions about the content of the data should be directed to NASA. If you are working in Microsoft Access, here's how to open the DBF files: 1. Copy the tables 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" Then select "Import" or "Link Tables..." 4. An import or link wizard will ask you to locate the file(s). You will need to change the "file of type" to "Dbase IV". If you are linking, hit "Cancel" when it ask for Index Files. 5. Each table will need to be imported or linked separately. ************************************ ABOUT THE DATA ************************************ In 2009, NASA made some changes to the data by adding static_attribute_value. According to Joseph Lau from NASA, "The static_attribute_value is a look up table of what we consider valid values. For example, if you look at where entity = �GenericAircraft�, what is displayed are aircraft make models that we recognize. Anything outside of this table our system would flag as something that needs to be added to the database or its not a valid make model. "The fixfield_dump and text_dump tables are actual reports submitted and entered into our system. So you are correct that there are things in static_attribute_value that won�t necessarily be in the fix field tables. Our various applications that are used by the public or in house uses static_attribute_value to derive a variety of information. It�s a universal look up table for us. It is not dependent on any other tables. We provide this table to people because some people care less about the reports and more about airport codes, make model codes etc. They use it like a reference tool." NASA last made significant changes to the data in 2000, eliminating the codes used in the past and using phrases and some abbreviations instead. In addition, they split the information into two tables - one consisting of the basic data and the other of the narrative information. There are 10 types of information referred to in the database as ENTITY: aircraft, component, environment, events, factors, person, place, situations, time and supplementary; assessments was one type of Entity information in 2000, but not anymore, and the supplementary is newly added in 2001. The detailed information can be found in the file "ASRS DBjul06.doc" (published by NASA). Then those are further broken down into particular attributes. The attribute field identifies the "type" of information that is located in the value field. For example, you might have a row where entity is "events" and attribute is "anomaly.cabin event" and value is "passenger contraband." This indicates that the event being reported took place in the cabin and that it involved passenger contraband. For aircraft, component and person entries, the "enumer" field indicates whether the data refers to person #1, person #2, aircraft #1, aircraft #2, etc. Zeros in the enumer field mean that type of information doesn't have multiple entries. The Remarks table contains the full narrative from the reporting individual, as well as SYN (synopsis). The synopsis is written by the NASA analyst who handled that particular report (essentially it's a condensed version of what happened). Both tables contain 153,257 separate incidents. Because this database is in a report format, you'll have to make at least two passes through to get the report that interests you. For instance, to find all reports relating to Miami International Airport, you'll have to first run a query asking for all the distinct Accnsion numbers where attribute= "LOCALE REFERENCE.AIRPORT" and value like "MIA*" You'll want to use a wildcard search on the value, particularly on the airport codes, because there could be additional information in the field. This query will give you a list of the accnsion numbers (a.k.a. report numbers) that took place at Miami International Airport. Make this into a new table or save it as a query (if working in Access). Then take that new table and join it with the database for the full reports. SELECT MAIN.* FROM MAIN, NEWTABLE WHERE MAIN.ACCNSION=NEWTABLE.ACCNSION One note of caution: journalists who have used this database advise keeping queries as broad as possible. For location, this means being aware of the aviation facilities in your area, and the way they may be coded. The Lookup table and coding sheets are useful in determining what types of "attributes" and "values" you might be looking for when trying to find particular records. The lookup table consists of the entity values and all their possible attribute codes and value data. The coding sheets are the ones used by NASA analysts when entering the data. As a result, they include a lot of aviation jargon and acronyms. Where possible, NICAR has included additional documentation to help decipher these. However, you may still want to contact aviation authorities in your area or NASA for further information as you dive into this data. The Remarks table has a field in Memo format. When you import this into either Access or FoxPro the computer may ask you to convert to the proper format. Just go ahead and say yes. This does, however, make it more difficult to transfer the file back and forth between different programs. ************************************ DEFINITIONS ************************************ Here are some definitions, from NASA, regarding the primary types of information included in this database: Accension (Report) Number: The unique accession number of the occurrence. Individual reports are not necessarily filed for each involved aircraft; therefore, ASRS reports can refer to more than one aircraft. State: The state, territory or foreign location of the facility nearest to where the event occurred. Event Anomaly Description: Unsafe or illegal action/condition during the event. Note: there may be more than one anomaly for a given event. See also the paper documents for explanations of the various anomalies. Primary Problem Area: Indicates whether the primary problem is with the aircraft, flight crew, ATC, airport, navigational aids, publications or weather. Type of Operation: Operator type. Aviation operations are categorized by the federal regulation that governs their conduct. Examples: AIR CARRIER, MILITARY, CORPORATE, PRIVATE, etc. Where the operation cannot appropriately be reported as any of the listed values, an additional category of "OTHER" is included. Helpful Web sites: Air Traffic Management Glossary of Terms: http://www.fly.faa.gov/FAQ/Acronyms/acronyms.jsp A Pilot/Controller glossary is available at http://www.faa.gov/airports_airtraffic/air_traffic/publications/ The Federal Aviation Regulations can be found at http://www.faa.gov/regulations_policies/ More information about ASRS is available at http://asrs.arc.nasa.gov/ ************************************* RESOURCES ************************************* From the IRE Resource Center (573-882-3364) Story Number: 14516 The WTAE-Pittsburgh series is a result of a two-month review of federal air safety documents in the Aviation Safety Reporting System or ASRS. The documents are part of a system that is little known outside of aviation circles. It allows anyone involved in flying to anonymously and confidentially report incidents, accidents or problems they witness or are involved in. We reviewed 2000 such reports on Pittsburgh International Airport, dating back to 1988. The review revealed many incidents and even accidents that the public never heard about. The investigation also uncovered a pattern of problems with runway and taxiway signs at the airport that have led to near miss incidents on the ground. The investigation also revealed an alarming number of accidents between planes and ground vehicles in the terminal parking area. Story Number: 16016 WABC-TV Channel 7 Eye Witness News investigated why two foreign 757 jumbo jets nearly collided on the JFK Airport in New York in June of 1998. The investigation revealed that this near-miss and an Avianca jet crash that killed 73 people 10 years ago "resulted from foreign pilots inability to clearly understand English, the international language of aviation." Story Number: 17563 NBC News Dateline reports on "a simple, yet deadly problem: miscommunication between commercial pilots and air traffic controllers." The investigation reveals that although "English is the de facto language of aviation, ... a lack of oversight has led to a breakdown in simple communication." It documents how poor language skills have hindered communication between foreign pilots and U.S. controllers, as well as between American pilots and controllers abroad. The report shows that the problem is widespread, because the Federal Aviation Administration has failed to enforce a standard. The investigation uncovers a tape "that documented how poor language skill contributed to the crash of an American Airlines plane into the side of a mountain in Cali, Columbia." It also details numerous differences between the standard aviation phraseology in the U.S.A. and the rest of the world. Tipsheet Number: 1211 This IRE tipsheet gives a list of data useful for covering aviation safety and explains how each can be helpful for journalists. The tipsheet also includes a check-list of what to do when a plane crashes to help the reporter find the information he or she is looking for more easily.