NTSB investigations, PR

180,604 investigations by the National Transportation Safety Board since 1962: 174,583 accidents, the rest incidents and other occurrences. Each has NTSB’s narrative and, once the investigation is done, its probable cause; since 2008, its findings and sequence of events.

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."eventStateOrRegion" = 'PR' AND c."eventCountry" = 'USA' 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.\"eventStateOrRegion\" = 'PR' AND c.\"eventCountry\" = 'USA'
  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."eventStateOrRegion" = 'PR' AND c."eventCountry" = 'USA'
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 UNK64X1926Accident · Serious injuriesClosedPiper PA22 · Arecibo, PRMay 23, 1964 · N4532Z · No damage NTSB UNK64X1615Accident · No injuriesClosedPiper PA30 · San Juan, PRMay 4, 1964 · N7322Y · Destroyed NTSB UNK64X1474Accident · No injuriesClosedPiper PA28 · Lobos Is, PRApr 26, 1964 · N5657W · Substantial damage NTSB UNK64X1385Accident · No injuriesClosedAero Comm 500B · San Juan, PRApr 19, 1964 · N619TW · Substantial damage NTSB UNK64X1205Accident · No injuriesClosedCessna 150 · Vega Baja, PRApr 6, 1964 · N7903E · Substantial damage NTSB MIA64I0081Incident · No injuriesClosedDouglas DC-8B · San Juan, PRFeb 17, 1964 · N8607 · Minor damage NTSB UNK64X0554Incident · No injuriesClosedDouglas DC-8 · San Juan, PRFeb 13, 1964 · N8604 · No damage NTSB UNK64X0259Accident · Minor injuriesClosedBeechcraft J35 · Utuado, PRJan 16, 1964 · N8301D · Destroyed NTSB MIA62A0039Accident · No injuriesClosedCurtiss-Wrt C-46R · San Juan, PRJun 5, 1962 · N1243N · Substantial damage