Skip to content


Switch branches/tags

Latest commit


Git stats


Failed to load latest commit information.
Latest commit message
Commit time


Download FARS (Fatality Analysis Reporting System) data and load it into a PostgreSQL database.

This repo also contains many look-up tables derived from the FARS User’s Manual.

Requires: make, postgresql (9.3+).


First, clone or download the repo. Then download the FARS CSV zip file from the NHTSA. Place the resulting file, which should have a name like .

To set up the database and load into the database:

make init load YEAR=2018

This assumes that your Postgres database has the same name as your system user. To customize Postgres connection options, use the Postgres environment variables:

export PGDATABASE=my_database_name PGUSER=myname

By default, the database tables will be created in a schema named fars. To customize the schema name, use the make variable SCHEMA:

make init load SCHEMA=myschema

The load task will attempt to create a geometry-type column in the fars.accident table, but if you're not using PosGIS, that step will fail and you can safely ignore the error.

Data consistency

The current table schema is valid for the most recent year available (2018), and may be fail for previous years. Even when table structures haven't changes, the meaning of the various attribute may have been altered.

Example queries

These queries are meant to demonstrate the relationships between the different tables in FARS.

First run the following to add the fars schema into the search path:

set search_path to public, fars;

Retrieve information about a crash:

    st_case, state,
    make_timestamp(a.year, a.month,, a.hour, a.minute, 0) as timestamp, route_type, road_owner, functional_system,
    nullif(, 'No Special Jurisdiction (Includes National Forests)') special_jurisdiction, manner_of_collision, first_harmful_event, as lighting_condition, rur_urb, relation_to_road,
    concat_ws('; ',, nullif(, 'No Additional Atmospheric Conditions')) weather,
    concat_ws('; ',, nullif(, 'None'), nullif(, 'None')) relatedfactors
from accident as a
    left join state using (state)
    left join route using (route)
    left join functional_system func_sys using (func_sys)
    left join road_owner owner using (rd_owner)
    left join special_jurisdiction sj using (sp_jur)
    left join harmful_event using (harm_ev)
    left join manner_of_collision man_coll using (man_coll)
    left join light_condition lgt using (lgt_cond)
    left join atmospheric_condition weather1 on ( = a.weather1)
    left join atmospheric_condition weather2 on ( = a.weather2)
    left join related_factors_crash rcf1 on ( = a.cf1)
    left join related_factors_crash rcf2 on ( = a.cf2)
    left join related_factors_crash rcf3 on ( = a.cf3)
    left join rural_urban rur_urb using (rur_urb)
    left join relation_to_road rel_road using (rel_road)
where st_case = 10845;

Retrieve information about persons involved in a particular crash:

select a.st_case,
    make_date(a.year, a.month, as date,
    make_time(nullif(a.hour, '99'), a.minute, 0) as timestamp,
    str_veh struck, persontype,
    person.age, as sex,
    nullif(, 'Not A Fatality (Not Applicable)') as race,
    nullif(, 'Not A Fatality (Not Applicable)') as hispanic_origin,, as restraint_use,
    air_bag, as alcohol_determination_method,
    drinking as police_reported_alcohol_involvement, alc_test_status,, injury_severity,
    make_date(nullif(death_yr, 8888), nullif(death_mo, 88), nullif(death_da, 88)) death_date,
    coalesce(nullif(, 'Not a Cyclist'), crash_group,
    concat_ws('; ',, nullif(, 'None/Not Applicable-Driver'), nullif(, 'None/Not Applicable-Driver')) relatedfactors,
    concat_ws('; ', nullif(, 'Not Tested for Drugs'), nullif(, 'Not Tested for Drugs'), nullif(, 'Not Tested for Drugs')) drug_test_result
from accident as a
    left join person using (st_case)
    left join pbtype pb using (st_case, veh_no, per_no)
    left join person_type using (per_typ)
    left join sex using (sex)
    left join race using (race)
    left join hispanic_origin using (hispanic)
    left join vehicle using (st_case, veh_no)
    left join transported_treatment tt using (hospital)
    left join crash_group_pedestrian using (pedcgp)
    left join crash_group_bike using (bikecgp)
    left join injury_severity using (inj_sev)
    left join seating_position seating using (seat_pos)
    left join restraint_use using (rest_use)
    left join alc_test using (alc_status)
    left join method_alc_det alc_det using (alc_det)
    left join drug_test_result drugres1 on (person.drugres1 = drugres1.drugres)
    left join drug_test_result drugres2 on (person.drugres2 = drugres2.drugres)
    left join drug_test_result drugres3 on (person.drugres3 = drugres3.drugres)
    left join related_factors_person sf1 on (p_sf1 = sf1.p_sf)
    left join related_factors_person sf2 on (p_sf2 = sf2.p_sf)
    left join related_factors_person sf3 on (p_sf3 = sf3.p_sf)
where st_case = 40792;

Query vehicles involved in a crash:

    v.deaths, make,
    model, body_type, trafficway, roadway_surface,
    nullif(nullif(trav_sp, '0'), '999')::numeric as travel_speed,
    numoccs occupants, as owner,
    dr_drink driver_drinking, registration,
    dr_zip drivers_zipcode, trailing_vehicle, hazardous_material, as bus_use, speeding, pre_event_movement, critical_precrash_event, attempted_avoidance, as manner_of_collision,
    concat_ws('; ',, nullif(, 'None'), nullif(, 'None'), nullif(, 'None')) driver_related_factors, most_harmful_event, as damage_extent, as accident_type,
    vin.bodystyl_t bodystyle,
    vin.drivetyp_t drivetype,
    vin.fuel_t fueltype

from accident as a
    left join vehicle v using (st_case)
    left join vindecode vin using (st_case, veh_no)
    left join pre_event_movement using (p_crash1)
    left join critical_precrash_event using (p_crash2)
    left join attempted_avoidance using (p_crash3)
    left join vehicle_owner owner using (owner)
    left join vehicle_make make using (make)
    left join state on (state.state = l_state)
    left join body_type using (body_typ)
    left join trailing_vehicle towing using (tow_veh)
    left join hazardous_material_class haz using (haz_cno)
    left join bus_use using (bus_use)
    left join harmful_event on (m_harm=harmful_event.harm_ev)
    left join manner_of_collision man_coll on (v.man_coll=man_coll.man_coll)
    left join related_factors_driver vsf1 on (vsf1.dr_sf = v.dr_sf1)
    left join related_factors_driver vsf2 on (vsf2.dr_sf = v.dr_sf2)
    left join related_factors_driver vsf3 on (vsf3.dr_sf = v.dr_sf3)
    left join related_factors_driver vsf4 on (vsf4.dr_sf = v.dr_sf4)
    left join roadway_surface using (vsurcond)
    left join speeding using (speedrel)
    left join trafficway trafficway using (vtrafway)
    left join accident_type using (acc_type)
    left join damage_extent using (deformed)

where a.st_case = 10845
order by veh_no asc;

Query information about the sequence of events in a crash:

    vnumber1,, vehicle1make, body_type1, reg_state,
    v1.model v1model,
    v1.trav_sp travel_speed,
    v1.vspd_lim speed_limit, area_of_impact1, as event,
    NULLIF(NULLIF(vnumber2, 5555), 9999) vnumber2, vehicle2make, body_type2, area_of_impact2
from cevent c
    left join sequence_events soe using (soe)
    -- vehicle 1
    left join vehicle v1 on (c.st_case = v1.st_case and c.vnumber1 = v1.veh_no)
    left join area_of_impact aoi1 on (aoi1=aoi)
    left join state s1 on (s1.state = v1.reg_stat)
    left join vehicle_make m1 on (v1.make = m1.make)
    left join body_type b1 on (v1.body_typ = b1.body_typ)
    -- vehicle 2
    left join vehicle v2 on (c.st_case = v2.st_case and c.vnumber2 = v2.veh_no)
    left join area_of_impact aoi2 on (c.aoi2 = aoi2.aoi)
    left join state s2 on (s2.state = v2.reg_stat)
    left join vehicle_make m2 on (v2.make = m2.make)
    left join body_type b2 on (v2.body_typ = b2.body_typ)

where c.st_case = 40399;

Retrieve the number of pedestrian and bicyclist fatalities for different crash situations:

SELECT person_type,
    COALESCE(NULLIF(, 'Not a Cyclist'), crash_group,
FROM person
    INNER JOIN pbtype USING (st_case, veh_no)
    LEFT JOIN person_type USING (per_typ)
    LEFT JOIN crash_group_pedestrian pcg USING (pedcgp)
    LEFT JOIN crash_group_bike bcg USING (bikecgp)
WHERE inj_sev = 4 -- fatal injury

Retrieve information about violations:

SELECT st_case, veh_no, name
FROM fars.violatn
    LEFT JOIN violations_charged USING (mviolatn)
WHERE st_case = 63300
    AND veh_no = 1;

Retrieve information about safety equipment:

FROM fars.safetyeq
    LEFT JOIN safety_equipment USING (msafeqmt)
WHERE st_case = 62269
ORDER BY per_no

Get non-motorist actions:

SELECT st_case, veh_no, per_no,
    FROM fars.nmprior
    LEFT JOIN nonmotorist_action USING (mpr_act)
WHERE st_case = 220455
ORDER BY per_no;

Select non-motorist actions for a specific crash:

SELECT st_case, veh_no, per_no, name
FROM nmcrash
    LEFT JOIN nonmotorist_contributing USING (mtm_crsh)
    WHERE st_case = 40079
ORDER BY veh_no, per_no

Select damage to vehicles for a specific crash:

SELECT st_case, veh_no, name
FROM damage
    LEFT JOIN damaged_area USING (mdareas)
WHERE st_case = 10041
ORDER BY veh_no;

Get driver distractions for a specific crash:

SELECT st_case, veh_no, name
FROM distract
    LEFT JOIN driver_distracted using (mdrdstrd)
WHERE st_case = 100024
ORDER BY veh_no;

Get vehicle factors

SELECT st_case, veh_no, name
FROM factor
    LEFT JOIN motor_vehicle_factor USING (mfactor)
WHERE st_case = 10024
ORDER BY veh_no;

Get driver impairments relavent to a crash:

SELECT st_case, veh_no, name
FROM drimpair
    LEFT JOIN impairment on (drimpair = impair)
WHERE st_case = 10079
ORDER BY veh_no;

Get vehicle maneuvers prior to a crash:

SELECT st_case, veh_no, name
FROM maneuver
    LEFT JOIN driver_maneuver using (mdrmanav)
WHERE st_case = 10134
ORDER BY veh_no;


load FARS data on fatal traffic crashes from the NHTSA into a PostgreSQL database






No releases published


No packages published