{"id":49,"date":"2015-11-04T22:02:33","date_gmt":"2015-11-04T22:02:33","guid":{"rendered":"http:\/\/www.thedatajanitor.com\/?p=49"},"modified":"2015-11-04T22:06:46","modified_gmt":"2015-11-04T22:06:46","slug":"the-neighborhood-map-of-u-s-obesity-available-as-data","status":"publish","type":"post","link":"http:\/\/www.thedatajanitor.com\/?p=49","title":{"rendered":"The Neighborhood Map of U.S. Obesity and Food Deserts Available as Data"},"content":{"rendered":"

RTI International released obesity data<\/a> for the US at the neighborhood level in 250×250 meter grid squares. I downloaded the state files and joined them together into a single PostGIS layer.<\/p>\n

wget 'http:\/\/synthpopviewer.rti.org\/obesity\/downloads\/AL.zip'\r\n7za e AL.zip\r\n\r\n\/Applications\/Postgres93.app\/Contents\/MacOS\/bin\/shp2pgsql -s 3857 '\/Users\/sparafina\/projects\/obesity\/AL.shp' obesity | \/Applications\/Postgres93.app\/Contents\/MacOS\/bin\/psql -U sparafina -d food_access\r\n\r\n\/Applications\/Postgres93.app\/Contents\/MacOS\/bin\/psql -U sparafina -c 'delete from obesity' -d food_access\r\n\r\nrm AL.s* AL.cpg AL.dbf AL.prj AL.zip\r\n\r\nfor s in {'AL','AZ','AR','CA','CO','CT','DE','DC','FL','GA','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY'}\r\n do\r\n f='\/Users\/sparafina\/projects\/obesity\/'$s'.shp'\r\n echo $f\r\n wget 'http:\/\/synthpopviewer.rti.org\/obesity\/downloads\/'$s'.zip'\r\n 7za e $s'.zip'\r\n \/Applications\/Postgres93.app\/Contents\/MacOS\/bin\/shp2pgsql -a -s 3857 $f obesity | \/Applications\/Postgres93.app\/Contents\/MacOS\/bin\/psql -U sparafina -d food_access\r\ndone\r\n\r\n\/Applications\/Postgres93.app\/Contents\/MacOS\/bin\/psql -U sparafina -c 'create index obesity_gist on obesity using gist (geom)' -d food_access<\/pre>\n
I converted the grids into points by generating the centroids of the grid.<\/p>\n
CREATE TABLE obesity_point AS\r\n SELECT popgte20, popbmige30, bmivsus, pctbmige30,      \r\n ST_Centroid(ST_Transform(geom, 4269)) AS geom FROM \r\n obesity;\r\n\r\nCREATE INDEX obesity_point_gist ON obesity_point USING gist(geom);<\/pre>\n
The points were then joined to Census tracts and their values were averaged by tract.<\/p>\n
CREATE TABLE obesity_tract AS\r\nSELECT AVG(o.popgte20) as avg_popgte20, \r\n AVG(o.popbmige30) AS avg_popbmige30, \r\n AVG(o.bmivsus) as avg_bmivsus, \r\n AVG(o.pctbmige30) as avg_octbmige30, \r\n MAX(o.popgte20) as max_popgte20, \r\n MAX(o.popbmige30) AS max_popbmige30, \r\n MAX(o.bmivsus) as max_bmivsus, \r\n MAX(o.pctbmige30) as max_octbmige30, \r\n MIN(o.popgte20) as min_popgte20, \r\n MIN(o.popbmige30) AS min_popbmige30, \r\n MIN(o.bmivsus) as min_bmivsus, \r\n MIN(o.pctbmige30) as min_octbmige30,\r\n COUNT(*) as total_points, \r\n t.censustract\r\nFROM obesity_point as o, food_access_tracts as t \r\nWHERE ST_Intersects(o.geom, t.geom)\r\nGROUP BY t.censustract;<\/pre>\n
The tracts with the obesity data were then joined to the food desert tracts from the USDA.<\/p>\n
CREATE TABLE access_obesity_tract AS\r\nSELECT \r\n f.gid,\r\n f.statefp,\r\n f.countyfp,\r\n f.tractce,\r\n f.affgeoid,\r\n f.geoid,\r\n f.name,\r\n f.lsad,\r\n f.aland,\r\n f.awater,\r\n f.foodaccess_id,\r\n f.state,\r\n f.county,\r\n f.lilatracts_1and10,\r\n f.lilatracts_halfand10,\r\n f.lilatracts_1and20,\r\n f.lilatracts_vehicle,\r\n f.urban,\r\n f.rural,\r\n f.la1and10,\r\n f.lahalfand10,\r\n f.la1and20,\r\n f.latracts_half,\r\n f.latracts1,\r\n f.latracts10,\r\n f.latracts20,\r\n f.hunvflag,\r\n f.groupquartersflag,\r\n f.ohu2010,\r\n f.numgqtrs,\r\n f.pctgqtrs,\r\n f.lowincometracts,\r\n f.pop2010,\r\n f.uatyp10,\r\n f.lapophalf,\r\n f.lapophalfshare,\r\n f.lalowihalf,\r\n f.lalowihalfshare,\r\n f.lakidshalf,\r\n f.lakidshalfshare,\r\n f.laseniorshalf,\r\n f.laseniorshalfshare,\r\n f.lahunvhalf,\r\n f.lahunvhalfshare,\r\n f.lapop1,\r\n f.lapop1share,\r\n f.lalowi1,\r\n f.lalowi1share,\r\n f.lakids1,\r\n f.lakids1share,\r\n f.laseniors1,\r\n f.laseniors1share,\r\n f.lahunv1,\r\n f.lahunv1share,\r\n f.lapop10,\r\n f.lapop10share,\r\n f.lalowi10,\r\n f.lalowi10share,\r\n f.lakids10,\r\n f.lakids10share,\r\n f.laseniors10,\r\n f.laseniors10share,\r\n f.lahunv10,\r\n f.lahunv10share,\r\n f.lapop20,\r\n f.lapop20share,\r\n f.lalowi20,\r\n f.lalowi20share,\r\n f.lakids20,\r\n f.lakids20share,\r\n f.laseniors20,\r\n f.laseniors20share,\r\n f.lahunv20,\r\n f.lahunv20share,\r\n o.avg_popgte20,\r\n o.avg_popbmige30,\r\n o.avg_bmivsus,\r\n o.avg_octbmige30,\r\n o.max_popgte20,\r\n o.max_popbmige30,\r\n o.max_bmivsus,\r\n o.max_octbmige30,\r\n o.min_popgte20,\r\n o.min_popbmige30,\r\n o.min_bmivsus,\r\n o.min_octbmige30,\r\n o.total_points,\r\n o.censustract\r\nFROM obesity_tract as o INNER JOIN food_access_tracts as f ON o.censustract = f.censustract;<\/pre>\n
Each PostGIS layer was export to a sql file via pg_dump. The data is available as a zip file compressed with 7zip. The archive contains the following:<\/p>\n