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:
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!