1

The National Highway Traffic Safety Administration has a database of fatal crashes called Fatality Analysis Reporting System (FARS) http://www.nhtsa.gov/FARS One can download each years data going back to 1975.

I have been assigned to build a SQL database of the entire FARS. Unfortunately over the course of the past 38 years there have been many changes to the data structure. What is the best way to build a queryable copy of the entire data?

3

First you will need to define the schema of your final database and determine how you will map each file to your schema.

Then I recommand using ETL tool (Extract Transform and Load) such as Talend Open Studio (TOS) or Penthao (both are open source solution). Using such tools will allow you to:

  1. automate the process (and not process each file individually)
  2. create mapping for each file between the original format and your SQL schema

In the case of talend you will do something like this:

  1. find the right component to connect to the input file (I don't know the format made available by FARS)
  2. connect this component to a tmap
  3. connect the tmap to a tSQLout component to update your database

Then using a tLoop and a putting your file name as a variable, you can loop through the different file and process them and define a different route depending on the file format from FARS (and update the tmap accordingly).

  • Thank you for sending me in that direction. I have installed Penthao Spoon and started skimming documentation. Can you point me to some documentation (or add to your answer) on mapping the file to the new schema? – Jacob May 14 '14 at 18:31
  • I am a talend user, I've updated my answer with a draft process – magdmartin May 15 '14 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.