Discovering food deserts

Greensboro, North Carolina, has a problem with food availability. Namely, there are large portions of the city where the nearest source of fresh food is several miles away. Unsurprisingly, these often tend to be the poorer parts of the city where not everyone has ready access to a car.

The Greensboro Code for America Brigade wanted to look into the issue, so I set about getting the data into a form we could work with locally. Below are the steps to follow to load the dataset into PostgreSQL.

Retrieving the food desert dataset

The food desert data and documentation can be downloaded as a single Excel file. However, it is keyed by census tract which means we will need the census tract information.

Loading 2010 US Census tracts

We will work off a modified version of these instructions to load U.S. Census tracts into a Postgres database.

First, assuming you are on Mac OS X, install lftp and gdal via Homebrew:

$ brew install lftp
$ brew install gdal --with-postgresql

and in Postgres, install the PostGIS extensions:

-- Enable PostGIS (includes raster)
CREATE EXTENSION IF NOT EXISTS postgis;
-- Enable Topology
CREATE EXTENSION IF NOT EXISTS postgis_topology;
-- fuzzy matching needed for Tiger
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
-- Enable US Tiger Geocoder
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder;

Next, download the Tiger data files from the FTP server:

$ mkdir uscensus
$ cd uscensus
$ lftp -e 'mirror -i "tl_2010_[0-9]{2}_tract10\.zip$" /geo/tiger/TIGER2010/TRACT/2010 .; bye' -u anonymous,anonymous ftp.census.gov

Finally, load the data into Postgres. Change the YOUR... variables to match your system.

$ unzip \*.zip
$ LIST="$(ls *.shp)"
$ for i in $LIST; do ogr2ogr -update -append -f PostgreSQL PG:"dbname=YOURDB user=YOURUSER password=YOURPASSWORD" $i -nlt MULTIPOLYGON25D -nln census_tracts_2010 -progress; done

Querying to find food deserts

Given a latitude and longitude, you can determine whether it falls into a food desert or not with this query:

SELECT *
FROM census_tracts_2010
JOIN food_access ON geoid10 = "CensusTract"
WHERE ST_Contatins(wkb_geometry, ST_SetSRID(ST_Point(longitude, latitude), 4269))