NTSB investigations

4,605 cases where the first occurrence was “In flight collision with object”.

Defining event

Finding

Phase of flight

Aircraft model

Cases, newest first

Query with SQL

SELECT c."ntsbNumber", c."eventDate", c."eventType", c."eventCity", c."eventStateOrRegion", a.n_number, a."aircraftMake", a."aircraftModel", c."highestInjuryLevel", c."totalFatal", c."completionStatus", c.report_url FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = c."mKey" AND a.position = 1 WHERE c."mKey" IN (SELECT o.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_occurrences.parquet' o WHERE o."occurrenceNumber" = 1 AND o."occurrenceCode" = 220) ORDER BY c."eventDate" DESC, c."ntsbNumber" DESC

duckdb -csv -c "SELECT c.\"ntsbNumber\", c.\"eventDate\", c.\"eventType\", c.\"eventCity\", c.\"eventStateOrRegion\", a.n_number, a.\"aircraftMake\", a.\"aircraftModel\", c.\"highestInjuryLevel\", c.\"totalFatal\", c.\"completionStatus\", c.report_url
  FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = c.\"mKey\" AND a.position = 1
  WHERE c.\"mKey\" IN (SELECT o.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_occurrences.parquet' o WHERE o.\"occurrenceNumber\" = 1 AND o.\"occurrenceCode\" = 220)
  ORDER BY c.\"eventDate\" DESC, c.\"ntsbNumber\" DESC"

Prints the whole list as CSV. It reads the release’s Parquet files over HTTPS, so there’s nothing to download first. Install DuckDB to run it.

SELECT c."ntsbNumber", c."eventDate", c."eventType", c."eventCity", c."eventStateOrRegion", a.n_number, a."aircraftMake", a."aircraftModel", c."highestInjuryLevel", c."totalFatal", c."completionStatus", c.report_url
FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = c."mKey" AND a.position = 1
WHERE c."mKey" IN (SELECT o.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_occurrences.parquet' o WHERE o."occurrenceNumber" = 1 AND o."occurrenceCode" = 220)
ORDER BY c."eventDate" DESC, c."ntsbNumber" DESC

Run it at the DuckDB prompt, or in any tool that reads Parquet over HTTPS. More ways to query the release are on Use the data.

NTSB LAX82FUG05Accident · No injuriesFinal reportQuickie 1 · Upland, CAThe pilot took off on an upward sloping runway towards rising terrain. The pilot stated that he was unable to maintain enough speed to clear obstacles with the 22 horsepower engine. He elected to land on a residential street. While maneuvering around a street…Jan 10, 1982 · N234Q · Substantial damage NTSB MIA82FLD14Accident · 1 fatalFinal reportCessna 172M · Odessa, FLWhile maneuvering at low altitude, the aircraft struck a power line and crashed into a field about 1000 ft south of the airport. The accident occurred at night in heavy fog. An examination of the cable marks on the wreckage indicated the aircraft was in a…Jan 8, 1982 · N80264 · Destroyed NTSB LAX82DA039Accident · No injuriesFinal reportAerospatiale SA-316B · Mammoth Lakes, CAThe pilot started to air taxi the helicopter out of a hangar. There was snow in front of the hangar that had been only partially cleared away. As the pilot was exiting the hangar, he raised the aircraft to go over the snow, the tail rotor struck the hangar…Jan 6, 1982 · N608RA · Substantial damage NTSB DCA82AA008Accident · 8 fatalFinal reportCessna 414A · Ashland, VAThe plt received 2 wx briefings, one on the evening bfr the flt, another at 1009 EST, within 3 hrs of the tkof time. IFR conditions were forcasted at the uncontrolled, destination arpt. The plt filed an IFR flt plan, but did not list an alternate arpt. The…Jan 3, 1982 · N2620L · Destroyed NTSB FTW82FPJ10Accident · 1 fatalFinal reportNavion A · Chickasha, OKThe aircraft departed from a private airport 2 miles south of Chickasha at 0330. At 0336 power was interrupted on electric power lines southwest of Chickasha. The wreckage was found 6 miles south and 4 and 1/2 miles west of Chickasha a few feet west of a 35…Jan 2, 1982 · N4876K · Destroyed