No one inconvenience can be dubbed the bane of a Census reporter's existence since there is a whole list of banes, from shifting release schedules to questionnaire wording changes to margins of error. One nominee for the worst, however, is the shifts in Census geography that can make calculating changes over time a challenge. I once naively thought that tracts either split or combined. Oh, if it were only true. They shift, re-combine in parts and otherwise heave to and fro like a sailboat tossed at sea.
Census reporters have tried various approaches for reconciling the moving tracts. Richard O'Reilly at the Los Angeles Times devised a brilliant approach in SAS that read the raw text of the TIGER\Line mapping files, looked at the vertices of each polygon and divvyed them up. Others have chosen to make the comparisons right in their GIS programs by stacking the geographic layers and computing how much the polygons overlap.
I have chosen, however, to go with the Census Bureau's tract-to-tract relationship files. While the files can be tricky to explain and are befuddling to look at, the good news is that they're not difficult to use. For each 1990 tract, the files show how much of its population went into any 2000 tract. So if a tract was divided with 20 percent of its population going into one tract, 30 percent into a second tract and 50 percent into a third, the file would indicate that. You can then use those percentages to assign any 1990 count data into 2000 tracts and easily compare changes over time.
Inside the files
The 1990 to 2000 tract relationship files are available at the Census Web site at www.census.gov/geo/www/relate/rel_tract.html. The files I used are the Population-based Census Tract Relationship files. You can find files for each state and a national file, all in fixed-width ASCII, along with the record layouts you'll need to import them into a spreadsheet or database program.
There are 18 fields. Four fields make up the 1990 FIPS code (state, county, tract base and tract suffix), and another four make up the codes for 2000. There are additional fields for 2000 with the state abbreviation and county name.
I suggest importing the relationship tables into the same database program where you store your Census data. That way you can easily link to the tables. Make sure you format the tract FIPS codes the same way you have them in your data. So if you put periods between the tract base and the tract suffix, make sure you do the same in the relationship tables. You'll be linking these tables to your 1990 and 2000 Census data.
Each record describes the relationship between a 1990 tract and a 2000 tract. Tracts that do not overlap have no record. The key parts of each record are two fields that describe the relationship between the 1990 and 2000 tracts. One is labeled "Percentage of 1990 census tract population." The other is "Percentage of 2000 census tract population." If a tract didn't change, both of those numbers will be 100 percent, since none of the population has been split up.
There are also flags for 1990 and 2000 indicating whether the record represents part of a tract. If a 1990 tract was split over two 2000 tracts, there will be two records in the table for that 1990 tract - one record to describe the relationship to each of the 2000 tracts. In that case, each of the records will have a "P" on the 1990 tract FIPS code to show that the record only describes a portion of the tract.
There's one very important limit to using these files: You can use them only on counts, not on median values, averages or any other calculated fields. You can, however, use them to assign data and generate your own calculated fields.
An example with the files
I wrestled with the partial-tract flags and the two numbers describing "Percentage of 1990 census tract population" and "Percentage of 2000 census tract population" for a while until I realized that I could throw most of them away. If I want to assemble 1990 data in 2000 Census tracts the only number I need is the "Percentage of 1990 Census Tract population." I named that field "tract90mult," which means that is the multiplier that I use for the 1990 tract to get the proper share for the 2000 tract.
Let's look at a small example.
The relationship table tells us that 1990 Tract 0001 has 40 percent of its population in 2000 Tract 0002 and 60 percent of its population in 2000 Tract 0003. The pertinent parts of the table would have two records:
1990 Tract FIP (simplified) 2000 Tract FIP (simplified) Tract90mult 0001 0002 40 0001 0003 60
So if I'm doing a story about African-American homeowners and I want to have a tract-to-tract comparison, I would divide the 1990 tract into the 2000 tracts using the tract90mult value. Note that the value is expressed as a percent. But if you multiply by those values, you'll get results 100 times too large. You have to divide the tract90mult by 100 first. I did that when I imported it. So if the tract90mult was 100, I turned it into 1. In this case, the values become 0.4 and 0.6.
Here are the three steps needed to calculate:
(1) Link the tract relationship file to the 1990 data by tract FIPS and multiply the 1990 value by the tract90mult. (2) Sum the new values grouping by the 2000 tract FIPS. (3) Link the result to the 2000 data using the 2000 tract FIPS to do comparisons.
Here is how to calculate the rate of African-American homeowners using SQL. The 1990 data is in STF1 table H9. The 2000 data is in SF1 table H15B.
select .tractfip90, b.tractfip00, tract90mult*a.H0090002 + b.tract90mult*a.H0090007 as black_hhlds_share, b.tract90mult*a.H0090002 as black_own_share, from tblSTF190 a join lkpTractConvert90_00 b on tractfip90,tractfip00 a.tractfip90 = b.tractfip90 where a.sumlev = '140'
The first step creates the values black_hhlds_share and black_own_share that have adjusted values. Now I have to aggregate those 1990 values into the 2000 tracts.
select tractfip00, sum(black_hhlds_share) as black_hhlds90, sum(black_own_share) as black_own90, from [the previous SQL query] group by tractfip00
Now I have a table with one record for each 2000 tract with a properly apportioned share of the 1990 data. All I have to do is link it to my 2000 data compare by tract.
select h15bi1 as black_hhlds00, h15bi2 as black_own00, black_hhlds90, black_own90, h15bi2 - black_own90 as black_owngain from tblSF12000 a join [the previous SQL query] b on a.tractfip00 = b.tractfip00
You can loop these three queries into one, which is how I did it. It looked something like this:
select h15bi1 as black_hhlds00, h15bi2 as black_own00, black_hhlds90, black_own90, black_rent90, h15bi2 - black_own90 as black_owngain from tblSF12000 h00 join (select tractfip00, round(sum(black_hhlds_share),0) as black_hhlds90, round(sum(black_own_share),0) as black_own90, from (select tract90mult*h0090002 + tract90mult*H0090007 as black_hhlds_share, tract90mult*h0090002 as black_own_share from tblSTF190 t0 join lkpTractConvert90_00 cv on t0.tractfip00 = cv.tractfip90 where t0.sumlev = '140' and localyn = 'Y') a group by tractfip00) b on h00.tractfip00 = b.tractfip00
Getting the stories
The first waves of Census stories have come and gone. Tools like the tract-to-tract relationship files can enable news organizations to delve deeper into patterns. The Washington Post used a version of the query above to help map home ownership for a story that looked at how African-Americans had fared over the 1990s in the Washington, D.C., region and elsewhere.
By using dot density representation of the counts at the tract level, we were able to display the 1990 values and additions in the decade on the same map. It showed that the old pattern of African-American homeowners concentrated in the District of Columbia had been replaced by a wide belt of suburban homeownership.
By the way, if tracts are too big for you, the Census has block relationship files that work the same as the tract relationship files. They're on the Census Web site at www.census.gov/geo/www/relate/rel_blk.html. There are not any block group relationship files.
Contact Dan Keating by e-mail at keatingd@washpost.com.