USDA Food Desert data with Census tracts

USDA provides data on food deserts in the US. The data is provided as an Excel spreadsheet and can be joined to Census  tract Cartographic Boundary Files. A PostgresSQL dump of the table containing the food desert data joined to the Census tract geography is available for download.

Processing steps are document below.

# Food access data from USDA:
# data is in Microsoft Excel format, convert to CSV in your favorite spreadsheet software (note: too large for Google Sheets)

# create database in postgres

createdb -h localhost -p 5432 -U postgres food_access
password ******

# use your favorite CLI to postgress to create the table

CREATE TABLE food_access (
 censustract varchar(50),
 state varchar(50),
 county varchar(50),
 lilatracts_1and10 integer,
 lilatracts_halfand10 integer,
 lilatracts_1and20 integer,
 lilatracts_Vehicle integer,
 Urban integer,
 Rural integer,
 LA1and10 integer,
 LAhalfand10 integer,
 LA1and20 integer,
 LATracts_half integer,
 LATracts1 integer,
 LATracts10 integer,
 LATracts20 integer,
 HUNVFlag integer,
 GroupQuartersFlag integer,
 OHU2010 integer,
 NUMGQTRS integer,
 PCTGQTRS numeric,
 LowIncomeTracts integer,
 POP2010 integer,
 UATYP10 varchar(10),
 lapophalf numeric,
 lapophalfshare numeric,
 lalowihalf numeric,
 lalowihalfshare numeric,
 lakidshalf numeric,
 lakidshalfshare numeric,
 laseniorshalf numeric,
 laseniorshalfshare numeric,
 lahunvhalf numeric,
 lahunvhalfshare numeric,
 lapop1 numeric,
 lapop1share numeric,
 lalowi1 numeric,
 lalowi1share numeric,
 lakids1 numeric,
 lakids1share numeric,
 laseniors1 numeric,
 laseniors1share numeric,
 lahunv1 numeric,
 lahunv1share numeric,
 lapop10 numeric,
 lapop10share numeric,
 lalowi10 numeric,
 lalowi10share numeric,
 lakids10 numeric,
 lakids10share numeric,
 laseniors10 numeric,
 laseniors10share numeric,
 lahunv10 numeric,
 lahunv10share numeric,
 lapop20 numeric,
 lapop20share numeric,
 lalowi20 numeric,
 lalowi20share numeric,
 lakids20 numeric,
 lakids20share numeric,
 laseniors20 numeric,
 laseniors20share numeric,
 lahunv20 numeric,
 lahunv20share numeric

# import the food_desert.csv file

COPY food_access FROM '/path/to/food_desert.csv' DELIMITER ',' CSV HEADER;

# prepare database for census tract spatial data


# script to download, extract, and import Census Boundary Files of tracts into postgres.
# note 1: if you are using, you might have to provide the full path to executables
# note 2: I use 7zip for zip files. If you use a different program, change accordingly
# begin script

# create table structure then delete data to prevent duplicates
shp2pgsql -s 4269 '/Users/sparafina/Downloads/cb_2014_01_tract_500k.shp' tracts2010 | psql -U username -d food_access
psql -U username -c 'delete from tracts2010' -d food_access

# download, unzip and import
for s in {'01','02','04','05','06','08','09','10','11','12','13','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','44','45','46','47','48','49','50','51','53','54','55','56'}
 wget ''$s''
 7za e 'cb_2014_'$s''
 shp2pgsql -a -s 4269 '/Users/sparafina/Downloads/cb_2014_'$s'_tract_500k.shp' tracts2010 | psql -U username -d food_access

# create index
psql -U username -c 'create index tractis2010_gist on tract2010 using gist (geom)' -d food_access

# end script

# switch back to postgres CLI

# add column to join tables 
# note 1: added this step to clarify but not necessary with a bit more sql
ALTER TABLE tracts2010 ADD COLUMN foodaccess_id varchar(50);

# populate join key in tracts2010
UPDATE tracts2010
 SET foodaccess_id = statefp || countyfp || tractce;
# join food_access to tracts2010 to create a new table
CREATE TABLE food_access_tracts AS
 FROM tracts2010 INNER JOIN food_access 
 ON tracts2010.foodaccess_id = food_access.censustract;

# export the table, if desired
pg_dump -t food_access_tracts food_access > '/path/to/food_access_tracts.sql';