Importing 2010 SF1 Census in PostgreSQL

The Census Bureau provides a template MS Access database for importing fixed width/comma delimited  SF1 files. Nice if you have MS Acess, which I don't.


With the help of MDBTools, I extracted the schema (using the mdb-schema command) from the Census provided template database. With a little bit of sed/awk/vi munging I converted the the schema into valid sql script that creates the SF1 tables in PostgreSQL.


Create a data base and run the script to create the tables

createdb SF1_2010
psql SF1_2010 -f create_SF1_tables.sql

You can import all or just the ones you need. For example, I wanted the total population count by age and sex by census tract. The 2010 Summary File 1 document shows the file containing the data I wanted (Chapter 6 The Data Dictionary), which was in xx000172010.sf1. Use the postgres copy command to import the data tables. 

COPY sf1_00017 FROM '/path/to/xx000172010.sf1' CSV;

The SF1 database also provides a geo_header table common to all SF1 data files. Unlike the SF1 data files, which are comma delimited, the geo_header file uses fixed length records. This can be imported with a bit file munging and some sql foo. The first step is to read the geoheader into a temporary table with each line as a record. There's a script for this also.

CREATE TABLE geo_header_staging (data text);
COPY geo_header_staging FROM '/path/to/dcgeo2010.sf1' DELIMITER AS '|';

Note that "|" is used as a delimiter to read the entire line.  


The second part is to use PostgreSQL's string functions to extract the values and insert them into the correct field in the geo_header table. Writing the sql would have taken some time, but fortunately the Census provides a SAS import script which I used as a starting point. With some more sed/awk/cut/paste/vi munging, I produced an import script.

psql SF1_2010 -f geo_header_to_postgresql.sql

This will create a PostgreSQL database with a valid geo_header table that you can use for querying by census geography. The primary key is logrecno, which ties together the geography with the data. Query for the logrecno for the geography of interest, then query for the data using the logrecno.

SELECT logrecno from geo_header_sf1 WHERE tract ='192200' AND sumlev ='140' AND name = ' Census Tract 1922';

SELECT * FROM sf1_00017 WHERE logrecno ='69229';

After selecting and summarizing the data, I produced a population pyramid. Whew!

* Thank you to @richgibson for corrections

Ranty, rant, rant: There ought to be a law ...

Correct if I'm wrong, but we are near the end of the first decade of the 21st century, right?  We've had more than a decade of federal initiatives to make geospatial data more available and interoperable.  Then why do I get handed a largish data set (4 to 7GB depending on the format) produced by a federal agency in a proprietary format that requires me to have a license to ESRI products?  

My task is to move this data set into PostgreSQL/PostGIS, but since I don't have the necessary SDE libraries on the system, I can't even compile ogr2ogr to accomplish this.  This really is a vendor problem, specifically an ESRI problem.  If I was handed a Oracle export file, I can easily move this data by compiling ogr2ogr because Oracle provides the necessary libraries in its client SDK, which is available by download.  

Ironically, the purpose of this exercise to make this data more easily available.  However, I can't make it past first base because I'm hamstrung by the data format.  This is such a 90's problem.  Although shape files are the defacto lingua franca for geospatial data, the format has its limitations, namely a 2GB limit for dbf files.  It was unusual for a data set to be larger than 1GB in the 90's; but 20 years later, data sets over 2GB are not uncommon.  The current Federal procurement language should be amended to mandate that geospatial software produce entire data sets that are in an open and accessible format.