Census data published by the U.S. Census Bureau is not readily transformed for Relational Database Management System (RDMS) insertion. Here we provide an archive file to create a United States 2010 Census Short Form 1 (SF1) relational database for my favorite open source RDMS PostgreSQL. Check back here for 2020 Census updates, coming soon.

Database Features

Census Bureau instructions pertain to Microsoft Access 2007 database creation but then only to separate databases for each state. Here we create data tables that combine all 50 states (plus District of Columbia and Puerto Rico). Tables are inked with a PRIMARY KEY defined on stusab (state) and logrecno. An example table definition appears below.

create table sf1_00001 (
fileid varchar(510),
stusab varchar(510),
chariter varchar(510),
cifsn varchar(510),
logrecno integer,
p0010001 integer,
PRIMARY KEY (logrecno, stusab)
);

Restoring the DB

Download link (11GB): Census_2010_sf1_pg This 2010 Census PostgreSQL database archive was created with the following Pg commands:

pg_dump -Fc -f /archive/Census_2010_sf1_pg -d sf1_2010r &

The pg_dump custom format (-Fc) allows flexibility to restore one or more objects. The database user is not saved so you can easily restore your own user. The following Pg commands will restore the database.

createdb sf1_2010r
pg_restore -Fc -d sf1_2010r -v < Census_2010_sf1_pg

The size of the final database is 230GB, so be prepared.

Testing

Try this query at the psql prompt:

select sf1_00003.stusab as “State”,
sf1_00003.p0030001 as “Population”,
geo_header_sf1.geocomp,
geo_header_sf1.logrecno
from geo_header_sf1,sf1_00003
where geo_header_sf1.sumlev=’040′ and
geo_header_sf1.geocomp = ’00’ and
geo_header_sf1.logrecno = sf1_00003.logrecno and
geo_header_sf1.stusab = sf1_00003.stusab
order by “Population” desc;

If everything is working, you should see all 50 states (+DC, PR)

References

1. Census Bureau data set
2. 2010 Census Summary File 1 technical documentation.