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

Sharing options
selective focus photography of licensed plate with open text hanged

Experiments • Dan Ormsby

FOSS4G UK 2022 – Sparkgeo represent at key open-source conference

The UK “free and open-source software for geo” (FOSS4G) community came together on November 17th to celebrate PostGIS Day.

closeup photography of clear glass window closed

Experiments • Darren Wiens

How to use SVG Filters on Web Maps

Here at Sparkgeo, while we often prefer to provide answers over visualizations, we still make a lot of web maps. And when we do, we take pride in…

Experiments • Tomer Rockman

STAC it up – A STAC tutorial

In this post we describe the process of taking a federal Canadian dataset and creating a SpatioTemporal Asset Catalog (STAC) using python.

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

Sharing options