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
- Extract .sql file from `sr24.sql.gz`
- Open MySqlWorkbench
- File -> Open sql script
- 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.