Skip to content

NTSB investigations, CT

180,610 investigations by the National Transportation Safety Board since 1962: 174,589 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" = 'CT' 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\" = 'CT' 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" = 'CT' 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 UNK65X0877Accident · No injuriesClosedPiper PA12 · Madison, CTNov 28, 1964 · N3355M · Substantial damage NTSB UNK65X0874Accident · Minor injuriesClosedCessna 172 · Madison, CTNov 28, 1964 · N1874Y · Substantial damage NTSB UNK65X0723Accident · No injuriesClosedCessna 182 · Madison, CTNov 15, 1964 · N5894B · Substantial damage NTSB UNK65X0692Accident · Serious injuriesClosedPiper PA-28 · Burlington, CTNov 13, 1964 · N7665W · Destroyed NTSB UNK65X0240Accident · No injuriesClosedBeechcraft T34 · Stafford, CTOct 11, 1964 · N4113A · Substantial damage NTSB UNK64X3839Accident · No injuriesClosedCessna 172 · Madison, CTSep 7, 1964 · N6471E · Substantial damage NTSB UNK64X3832Accident · 2 fatalClosedRyan Navion · Suffield, CTSep 6, 1964 · N5346K · Destroyed NTSB UNK64X3783Accident · No injuriesClosedStinson 108-3 · Tolland, CTSep 5, 1964 · N687C · Substantial damage NTSB UNK64X3642Accident · No injuriesClosedPiper PA28 · Madison, CTAug 28, 1964 · N5391W · Substantial damage NTSB UNK64X3449Accident · Serious injuriesClosedAeronca 7AC · Plainfield, CTAug 18, 1964 · N33P · Destroyed NTSB UNK64X3315Accident · No injuriesClosedAeronca 7AC · Danielson, CTAug 10, 1964 · N4592E · Substantial damage NTSB UNK64X3253Accident · No injuriesClosedCessna 206 · Terryville, CTAug 7, 1964 · N5008W · Substantial damage NTSB UNK64X3156Accident · Minor injuriesClosedErcoupe 415C · Wolcott, CTAug 1, 1964 · N99678 · Destroyed NTSB UNK64X2915Accident · No injuriesClosedCessna 172 · Waterford, CTJul 19, 1964 · N6434B · Destroyed NTSB UNK64X2901Accident · No injuriesClosedPiper PA22 · East Haddam, CTJul 18, 1964 · N5695Z · Destroyed NTSB UNK64X2698Accident · No injuriesClosedAeronca L16 · Meriden, CTJul 7, 1964 · N1316V · Substantial damage NTSB UNK64X2107Accident · No injuriesClosedCessna 170B · Plainville, CTJun 4, 1964 · N3452C · Substantial damage NTSB UNK64X2024Accident · No injuriesClosedPiper PA15 · Madison, CTMay 30, 1964 · N4344H · Substantial damage NTSB UNK64X1962Accident · No injuriesClosedTemco Swift GC1B · Ansonia, CTMay 26, 1964 · N2350B · Substantial damage NTSB UNK64X1931Accident · No injuriesClosedPiper PA12 · Harwinton, CTMay 24, 1964 · N3840M · Substantial damage NTSB UNK64X1928Accident · No injuriesClosedErcoupe 415C · Bethany, CTMay 24, 1964 · N87054 · Substantial damage NTSB UNK64X1637Accident · No injuriesClosedMooney M18LA · E Windsor, CTMay 6, 1964 · N4089 · Substantial damage NTSB UNK64X1589Accident · No injuriesClosedAeronca 11AC · Enfield, CTMay 3, 1964 · N86327 · Substantial damage NTSB UNK64X1565Accident · No injuriesClosedPiper PA24 · Danbury, CTMay 2, 1964 · N8291P · Substantial damage NTSB UNK64X1358Accident · No injuriesClosedGlobe Swift GC1B · New Haven, CTApr 18, 1964 · N711M · Substantial damage NTSB UNK64X1352Accident · No injuriesClosedCessna 172 · Woodstock, CTApr 17, 1964 · N7660X · Substantial damage NTSB UNK64X0775Accident · No injuriesClosedErcoupe 415C · Harwington, CTMar 1, 1964 · N93584 · Substantial damage NTSB UNK64X0666Accident · Minor injuriesClosedPiper PA22 · Meriden, CTFeb 22, 1964 · N5589Z · Substantial damage NTSB UNK64X0165Accident · No injuriesClosedStinson 108-2 · Ellington, CTJan 5, 1964 · N260C · Substantial damage NTSB UNK64X0164Accident · No injuriesClosedPiper PA22 · Danbury, CTJan 5, 1964 · N4967Z · Substantial damage NTSB NYC62A0108Accident · 1 fatalClosedConvair 340 · Windsor Lock, CTOct 19, 1962 · N8415H · Minor damage