A friend of mine recently asked for some help getting census data into ArcMap. He wanted some income data for a project he was working on. ESRI has done a good job putting the spatial census data on their website in easily accessible form. So getting census blocks or census tracts is relatively easy. They also have basic demographic data available from Summary File 1 (SF1) and PL94. Unfortunately, they don’t include income data. If you want more detailed data, such as income, there’s none to be had at the ESRI site. SF2, SF3, and SF4 are available from the census, but it requires a bit of work to get them in a format that ArcMap can read.
I couldn’t find any quick and easy instructions for how to get SF3 data into ArcMap. The census’s own documentation, while quite complete, isn’t exactly user friendly. I thought I’d document the process, if for no other reason than to make it easier next time I need to do this. While these instructions are specifically for SF3, they will probably be helpful for SF2 and SF4 as well. These instructions are written for ArcGIS 9.3 and Microsoft Access 2008, some adaption may be necessary for other versions of the software.
1. Figure out which tables you want
Summary File 3 is huge. It’s based off the longer survey that goes out to 1 in 7 households, rather than the shorter census form that everyone gets. The list of tables in the documentation goes on for 43 pages in the documentation. You need to go into this knowing which table or tables you need. The best way to do this is to download the documentation. The list of tables is in Chapter 5. Chapters 6 and 7 go into a little more detail about each table. Figure out which table or tables has the data you want to use.
2. Figure out which file the tables are in
Becuase there are so many tables, the Census didn’t just do the simple thing and put each table into a seperate file. Instead, they broke the tables up into 76 different files for each state. Chapter 2 of the documentation describes, among other things, which tables are in each file. Figure out which file or files the tables you want are in.
3. Download the data
The files are available from the Census Bureau website. Choose the appropriate state and download the files you identified in Step 2. In addition to downloading the files with the tables you want, download xxgeo_uf3.zip (where xx is the state abbreviation). Unzip these files in the directory where you’ll be working. Change the filename extenstion on all files with a “.uf3” extension to “.txt”.
4. Download the Access File Template
Download the Summary File 3 template file for Microsoft Access. Unzip this file in the directory where you’ll be working.
5. Import the data into Access
Open the Summary File 3 template file (sf3.mdb) in Microsoft Access. You should see a list of tables, one for each of the 76 files in SF3. Select table for the file you downloaded and click on the “External Data” tab and Import the Text File you downloaded and renamed with a .txt extension. When you import the file, append the records to the existing table that matches your file. This file is comma delimited. If the tables you want are in more than one file, repeat this for each file.
Once your data files are done you need to import the Geo ID file. You want to append these records to the “SF3GEO” table. Unlike the data files, the Geo ID file is fixed with, rather than delimited. Click on the Advanced button, then Specs and select the SF3GEO Import Specification to but the columns in the right places.
Open up the SF3GEO sheet and click on the Datasheet tab. Right click on one of the column headings and add a new field. Name this field “STFID”.
Go to the External Data tab and Export the SF3GEO table as a Text File. This file should be comma delimited and include the field names on the first row.
5. Download Spatial Data
The sf3.mdb file now contains all the tabular census data, but if you want to use this in ArcMap, you need some spatial data to associate it with. The best source for spatial census data is the ESRI Census 2000 TIGER/Line Data page. Most of the data in SF3 only goes down to the Block Group level, so that’s probably the best choice. Add the downloaded data to a map in ArcMap. If your study area covers more than one county, you may want to merge the counties together into a single shapefile.
6. Add Census Data to ArcMap
Click on the add data button and open the sf3.mdb file. Select the tables you imported your data into in Step 4 and add them to the map.
Add the SF3GEO table from the text file (not from the .mdb). Open the SF3GEO table and right click on the STFID column you created in Step 4. Right click on the SF3GEO table and go Data > Export to export it as a .dbf. Add the .dbf to the map and remove the text file. Open the .dbf version of the table, right-click on the STFID column, and choose the Field Calculator. In the large text box under “STFID =” enter:
[STATE]+ [COUNTY]+ [TRACT]+ [BLKGRP]
Click OK and it wil populate the STFID field.
7. Join the Census Data to the Spatial Data
Join the Block Group layer to the SF3GEO table using the STFID field for the join in both tables. Selelect “Keep only matching records” under Join Options. Now join the other tables containing the census data with the Block Group layer using the LONGRECNO field. Again, keep only matching records.
Congratulations! Your census data is now (finally) loaded into ArcMap. At this point you may want to rename some of the cryptic column names into something more meaningful, delete some of the columns you’re not interested in, etc.
GeoTales 