Download American Community Survey data and put it into a Postgres database
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
.gitignore
Dockerfile
Drakefile
LICENSE
README.md
postprocess.sh
state.txt
tiger_create.sql

README.md

acs2pgsql

This repository imports American Community Survey (ACS) data into PostgreSQL. The hard work has already been done by Census Reporter. We add the following features:

  • Make the state (e.g. Illinois, California) a variable
  • Fix issues with the 2009 ACS 5-year data
  • Run the workflow using docker or drake

Usage

  1. Clone the repository and create the file acs2pgsql/default_profile by specifying PostgreSQL credentials and the desired U.S. State in the following template:
PGHOST=
PGDATABASE=
PGUSER=
PGPASSWORD=

STATE=Illinois
STATE_ABBREV=il

If the state name has multiple words, remove the space: e.g. for North Carolina use STATE=NorthCarolina.

  1. Change to the acs2pgsql directory. To use docker, run docker build --rm -t "image_tag" .. Docker will create an image and then extract, transform, and load the ACS data into your PostgreSQL database. To use drake, run drake.

  2. Consult the census-postgres documentation for full technical details. See also the census documentation for each dataset, especially the:

  • Appendix (e.g. 2015) for information about census tables, and the
  • Sequence Number and Table Number Lookup (e.g. 2015) for information about variables.

TODO

  • Expand to Decennial and ACS 1yr and 3yr
  • Rename folders per state