NTSB Aircraft Incidents and Accidents in Google Earth

Unlike the FAA, the NTSB permits anyone on the Internet to download their entire database of aircraft incidents and accidents. The NTSB's file is simple: one table contains all fields, and their data dictionary explains the fields. The URL to the narrative at the NTSB's site is included for quick report access. I downloaded that file and converted it into a KML file for Google Earth. The event icons are color-coded: blue for incidents, orange for non-fatal accidents, and red for fatal accidents. Unknown events get the default GE pushpin.


CLICK ON THE ICONS IN GOOGLE EARTH FOR MORE INFORMATION. (Seriously, a lot of people miss this and don't get most of the information.)

I did not have to do any geographic math on latitude and longitude. It's in decimal format in the NTSB downloadable, unlike the FAA which has at least two different lat/lon formats in every file. Not all location information is correct, but most of it is. (An example is N6ZV.)

Unfortunately, not all events have been geocoded. Twenty-seven percent, or 19,706 of the total 72,571 events in the NTSB database do have latitude and longitude, and those are contained in the KML file along with other details of the event. Please note that in Google Earth, you need to click on the icon to get the details. (Some people just look at all the items but do not click and miss most of the useful information.)

Looking at the file in Google Earth, the obvious becomes apparent: Most incidents and accidents take place at airports. Out west, a planes don't always make it over mountains. Looking at red sites is also a little creepy.

The idea to map aircraft accidents into a KML file grew out of my imperfect recollection of accidents at nearby airports. I remembered one accident at a nearby airport (ANP), but not the details. If you're going to use all available information to brief yourself, then NTSB accident information for an unfamiliar destination airport might prove useful. You don't need this file and Google Earth to do it -- you can use the NTSB's site. Using the GE, however, might help you identify problem areas.

If I update the KML file (NTSB data is updated monthly), it will go into the KML archive.

New Improved Military Training Routes KML File -- Includes Puppies

After noticing that the FAA had reduced the line length of their Military Training Route text file from 553 characters to 520 characters in their April release, I knew that I had some cleanup work. They also created a new type of record, separating out the agency data from the base MTR record into its own type. Database folks would call these tables. Who says they government can't change? This makes creating the KML a little easier, because I'd rather parse more record types with fewer fields than fewer record types with more fields.



Fun facts:

• Pretty much everything the FAA writes is in ALL CAPS.


• There are about 400 "noise-sensitive" areas that our military pilots need to know about, including at least nine chicken farms.


• There are about 39 "CONGRESSIONAL" noise-sensitive areas, including several "CONGRESSIONAL (EXTREMELY) NOISE SENSITIVE AREA".


• There is at least one "CONGRESSIONAL NOISE SENSITIVE AREA (CATTLE FARM)" at N36-28.8 W80-27.5. Who knew that sonic booms make milk go sour?


• Puppies! "ROUTE SEGMENTS A TO B AND B TO C CLOSED 1 MARCH THROUGH 30 MAY ANNUALLY DUE TO NEW BORN SILVER FOX PUPS." We'll forgive the grammar for puppies.

I am working on creating a KML sub-set to include just Congressional noise-sensitive areas, so we can all know which areas to avoid.

File Format Improvements:

• SOP and other data are not commingled between IR and VR routes with the same number. Route Type and the number are a composite key.


• Now includes terrain-following operations notes, if applicable


• When you click on a line, you get a table of data rather than all freeform text.


• When you click on a waypoint, you can see the Navaid distance and radial.


• Waypoints are now star shapes instead of the stock pushpins.

The new Military Training Route file is in the KML Archive. The filename starts with "MTR".

NOT FOR NAVIGATION. CONTAINS UNVERIFIED DATA.

Airspace, Special Use, and Airport KML files updated (not all US Airports are in the Western Hemisphere)

I updated the Airspace, Special Use, and Airport KML files for the FAA data covering the 56 Day Subscription from August 27 2009 to October 22 2009. I have also created batch files to call all the scripts, and I'm working on a T-SQL script to clear the tables and do a bulk import from the files.

Also, I corrected one incorrect assumption. Not all US Airports are in the western hemisphere. I assumed all longitudes were W and put a minus sign in the conversion to Google/KML decimal format from the FAA's all-seconds format. Wrong. In the updated file, MAJ (MARSHALL ISLANDS INTL) is in the right place. I'm still not sure Pago-Pago is correct, however.

The index of files is here.

View all US Airports on Google Earth via KML

I finally took the time to write the script to take the raw National Flight Data Center APT.TXT file and write it straight to a Google Earth and Maps-compliant KML file. It's pretty basic XML, with a little math to convert the FAA's all-seconds format into decimal coordinates.

A word of warning: DO NOT USE FOR NAVIGATION. This is unverified data from the FAA, and my math is often accurate to about a factor of ten. Also, there are 13,569 airports, which means there are 13,569 points in this file. It's enough to max out the processor running Google Earth on your computer. However, when you zoom in to a single state, it will speed up because most points are off-screen. I filtered out non-airport landing facilities: BALLOONPORT, SEAPLANE BASE, GLIDERPORT, HELIPORT, STOLPORT, ULTRALIGHT, so you will find none of those in this file. (The FAA likes things in ALL CAPS.)

The pop-up description of each data point includes the airport code, airport name, UNICOM frequencies, CTAF, and pulic or private. Download the US Airports KML file here.

Here's a screenshot of Google Earth with the KML loaded: