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

US census tracts

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

Experiments • James Banting

STACLint: Validate your STAC

STACLint is an online validation tool for STACs. With it, you are able to validate STAC catalogs, collections, and items by supplying either a URL or JSON. The tool will…

Experiments • James Banting

COG—Huh! What is it good for?

Cloud Optimized GeoTIFF (COG) are geotiff files, like satellite imagery, that have been processed in a manner that makes it easy to consume for internet processing applications (read cloud). It…

Experiments • Will Cadell

Disruption in the Geospatial Age (Part 1)

How grand I am to throw around terms like ‘the geospatial age’ as if I’m some kind of luminary. It sounds wonderful that we might be in some kind of…

Need a geospatial partner?

Our team complements organizations like yours—by providing on-tap access to geospatial, analytics, and mapping expertise.

Let’s talk

Join our team?

We’re always looking for skilled technologists to help us build the future of geospatial. Got a minute to find out more about us?

Working Here