About the data

If you are planning to make a diet or nutrition web/phone app, this data may help you.

This is the data from the USDA National Nutrient Database for Standard Reference - Release 24.
The raw data can be found here: http://www.ars.usda.gov/Services/docs.htm?docid=8964 (but we will work with the data already transformed into sql).

[Note: the current release, as of July 2016, is the #28]

There is also a very useful script that lets you download the .txt directly from the source (as a single .zip), and then insert those .txt into a mysql database: find the .sh script here.

Downloading the database

Scuzzbopper has nicely gathered all the necessary info in his repo:

https://github.com/Scuzzbopper/SR24

  • sr24.sql.gz: contains the "all in one" sql script to create the database.
  • sr24_doc.pdf: the documentation in order to fully understand what is in the database.
  • sr24_import.sql: will let you see the description of the fields of each one of the tables.

Setting up the db in MySQL

Importing the data

  1. Extract .sql file from `sr24.sql.gz`
  2. Open MySqlWorkbench
  3. File -> Open sql script
  4. Execute

It will automatically create the database, under the name "r24".

Correcting the data

First let's make a little correction here: value units are in BLOB format. First of all, let's change them into to human-readable text:

ALTER TABLE nutrient_definition ADD units_text VARCHAR(10);
UPDATE nutrient_definition SET units_text=CAST(units AS CHAR(10000) CHARACTER SET latin1);

Query example

Now let's create a query as an example: what are the nutrients inside 100gr of blue cheese?

SELECT 
    long_desc, nutrdesc, nutr_val, units_text
FROM
    nutrient_data ndata
        JOIN
    nutrient_definition ndef ON ndata.nutr_no = ndef.nutr_no
        JOIN
    food_desc fdesc ON fdesc.ndb_no = ndata.ndb_no
    WHERE 
    long_desc = 'Cheese, blue';

You will notice that the query takes a while to complete. This is because the foreign keys are not set up yet.

Foreign keys

Let's set up the basic foreign keys. Execute this code in mysql:

-- Food description & nutrient description foreign keys:
ALTER TABLE `sr24`.`nutrient_definition` 
ADD PRIMARY KEY (`nutr_no`)  COMMENT '';
 
ALTER TABLE `sr24`.`food_desc` 
ADD PRIMARY KEY (`ndb_no`)  COMMENT '';
 
ALTER TABLE `sr24`.`nutrient_data` 
ADD INDEX `FK_nutrient_definition_idx` (`nutr_no` ASC)  COMMENT '',
ADD INDEX `FK_food_desc_idx` (`ndb_no` ASC)  COMMENT '';
 
ALTER TABLE `sr24`.`nutrient_data` 
ADD CONSTRAINT `FK_nutrient_data2nutrient_definition`
  FOREIGN KEY (`nutr_no`)
  REFERENCES `sr24`.`nutrient_definition` (`nutr_no`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `FK_nutrient_data2food_desc`
  FOREIGN KEY (`ndb_no`)
  REFERENCES `sr24`.`food_desc` (`ndb_no`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Food group description

Let's set up the foreign key:

-- Food group description foreign key:
ALTER TABLE `sr24`.`food_grp_desc` 
ADD PRIMARY KEY (`fdgrp_cd`)  COMMENT '';
 
ALTER TABLE `sr24`.`food_desc` 
ADD INDEX `FK_food_desc2food_grp_desc_idx` (`fdgrp_cd` ASC)  COMMENT '';
ALTER TABLE `sr24`.`food_desc` 
ADD CONSTRAINT `FK_food_desc2food_grp_desc`
  FOREIGN KEY (`fdgrp_cd`)
  REFERENCES `sr24`.`food_grp_desc` (`fdgrp_cd`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Now we can add "food group description" to the query:

SELECT 
    long_desc, fdgrp_desc, nutrdesc, nutr_val, units_text
FROM
    nutrient_data ndata
        JOIN
    nutrient_definition ndef ON ndata.nutr_no = ndef.nutr_no
        JOIN
    food_desc fdesc ON fdesc.ndb_no = ndata.ndb_no
        JOIN
    food_grp_desc fgrpdesc ON fgrpdesc.fdgrp_cd = fdesc.fdgrp_cd 
WHERE
    long_desc = 'Cheese, blue';

Comments

Becoming a Wikidot member is free and easy: create an account.

You have to be a Wikidot member to add comments to a Wikidot blog.

Add a New Comment