Building a US Census Tracts PostGIS Database

This article shows how you (yes, you!) can build your very own US Census Tracts PostGIS database. I had looked over the interwebs for some time under the false assumption that someone, somewhere would have already done this, but it seems not. So this article documents my process. I am running a mac, so some things could be done faster / smarter using other technology refinements, but I have tried to keep things pretty generic for ‘nix users. Windows people; you will find this process is pretty simple, I am sure you can replicate it using what you have available.

You will need:

• PostGIS running (locally in my case), that means you should look at the PostGIS requirements for your machine.
• ogr2ogr available at the command line
• familiarity with FTP

And that is kinda it! This is actually quite simple, but there is a great deal of data involved, so consider that you are downloading a fair bit then processing a fair bit.

Step 1 – get data

The US census bureau has a great deal of data, so I will short cut your process a touch. Navigate to the directory of your choice in your terminal, then:

ftp
> open ftp.census.gov
user - anonymous (when asked)
pass - anonymous (when asked)
> cd geo/tiger/TIGER2012/TRACT
> ls (just to make sure)
> mget *
mget t1_2012_01_tract.zip [anpqy?] a (you respond "a" here or you will have to answer this question 56 times)
>quit

That should get you the data you need, in the directory in which you reside. Now unzip it:

unzip *.zip

Step 2 – make data useful

Once that is complete, you should have a directory full of shapefiles. I found I have 56 shapefiles, check your too using:

ls *.shp | wc -l

but as the census bureau are the custodians I could not comment on if you will get exactly the same number or not. Now, we want to load up a database. Go to postgres and set up a database. I called mine censustract2012, make sure it is built using your PostGIS template, perhaps like this:

createdb -h localhost -T template_postgis censustract2012

Then to start populating that database, you can try this:

$ LIST="$(ls *.shp)"
$ for i in $LIST
> do ogr2ogr -update -append -f PostgreSQL PG:"dbname=censustract2012 user=postgres password=postgres" $i -nlt MULTIPOLYGON25D -nln CENSUSTRACTS_EXAMPLE -progress
> echo "done:" $i
> done

the first instance will create the actual “CENSUSTRACTS_EXAMPLE” table for you.

That, ladies and gentlemen, should be just about it. You can get a visual by plugging in QGIS to the database, and it should look a little like this

Hooray, now you can analyze away!

This blog post is brought to you with my enormous appreciation of the US Census Bureau, the PostGIS wizards, and the OGR / GDAL wizards