Importing Census Data into ArcMap

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.

Advertisement

Charlie Really Did It

Charlie Wilson’s War is a political black comedy about a hard-drinking, cocaine snorting, womanizing congressman who conspires with a CIA agent with a tendency to tell his superiors to go fuck themselves, along with the Saudis, Israelis, Egyptians, and Pakistanis to wage a secret guerilla war in Afghanistan. It might seem at home on the shelf next to movies like Wag the Dog, or even Canadian Bacon.

Except for one thing . . . it’s all true.

Even the most unbelievable parts are true. Charlie Wilson really did take a Texas belly dancer to Egypt to entertain the Egyptian defense minister. Israel really did conspire with three Muslim countries to help aid an Islamic resistance movement. And Charlie Wilson really did almost singlehandedly set the U.S. on the path towards defeating the Soviets in Afghanistan over the vehement objections of some people in the CIA.

There’s much more to the story than can be told in a two hour movie of course. The book Charlie Wilson’s War, by George Crile, on which the movie is based, does an excellent job of telling the behind the scenes story of the funding of the Afghan war. Other books, like Steve Coll’s Ghost Wars, and The Main Enemy, by Milt Bearden, do an excellent job of telling how events unfolded in Afghanistan and Pakistan, rather than Washington. However, Aaron Sorkin’s script really hits the high points and the important events. Unlike The Golden Compass, Charlie Wilson’s War is a textbook example of how to adapt a book for the screen, condensing the tale and capturing the feel, without needing to include every detail.

The movie also vividly portrays Charlie Wilson and Gust Avrakotos, the two larger than life characters who turned American aid to the Afghans from an ineffective drizzle into a massive flood. Tom Hanks manages to capture both “Good Time Charlie”, the womanizing boozer, and the serious Charlie, who admired Winston Churchill and desperately wanted to accomplish something significant. Julia Roberts turns in an excellent performance as Joanne Herring, the conservative Christian socialite who set Charlie Wilson on course to become the Afghan’s greatest champion in Washington. Though Hanks and Roberts provide stiff competition, the best performance in the movie belongs to Phillip Seymour Hoffman, as blue collar Greek-American CIA agent Gust Avrakotos. Hoffman is the star of every scene he’s in and his performance as Gust is definitely Oscar-worthy.

Charlie Wilson’s War has a snappy, funny, wittily worded Aaron Sorkin screenplay and excellent performances from three of the best actors around. It also provides a window into events that a lot of people in this country have forgotten about, or never knew in the first place. With America’s support, the Afghan war not only helped bring the Soviet Union to it’s knees, but our lack of follow through after the Red Army withdrew laid the seeds for the Taliban regime and provided a base for Al’Qaeda. As we face choices about how to proceed in Iraq and Afghanistan, the lessons of these events loom large.

The Golden Compass whirls so fast it will make you dizzy

A few weeks ago I went to see The Golden Compass, which is based on the first book of the His Dark Materials trilogy by Phillip Pullman. I’d read the trilogy a while back and thought it was pretty good, in part because it didn’t try to be Tolkienesque epic fantasy, unlike so many multivolume fantasy works these days. Pullman wrote it in part because he didn’t like the religious message in The Chronicles of Narnia, so it is often compared to the Narnia series, or labeled as the “anti-Narnia”, but I don’t think this comparison is particularly apt either. If you want a real anti-Narnia story, read “The Problem of Susan” by Neil Gaiman. His Dark Materials is really quite different and unique, which is a big part of its appeal for me.

The filmmakers have diligently scrubbed every bit of Pullman’s anti-religion message from The Golden Compass, though this hasn’t prevented religious groups from slamming the film. While I don’t agree with the religious criticisms, I still can’t recommend this movie. Even though His Dark Materials definitely isn’t Tolkienesque epic fantasy, it’s pretty clear that was what New Line Cinema was looking for when they bought the rights. They made fortune from The Lord of the Rings and they were clearly looking to replicate that success here. Despite His Dark Materials non-Tolkienesque origins, the filmmakers try quite hard to turn The Golden Compass into a Jacksonesque epic fantasy movie. Unfortunately Writer/Director Chris Weitz isn’t up to Peter Jackson’s standard.

The fundamental problem with the movie is its relentless pace. Scenes proceed only long enough to advance the plot, cramming in as much expository dialog as possible. Characters are shuffled on and off stage so quickly that only the primary protagonist Lyra Belacqua gets any real character development. Daniel Craig, Sam Elliot and Eva Green play characters who had major roles in the novel, but are reduced to little more than extended cameos in the film. The special effects team creates some stunning visuals that are a match for Weta Digital’s work on the Lord of the Rings, but we are rarely given enough time to appreciate these fantastic sights.

The Golden Compass is a very plot-heavy book, and virtually every plot point makes it into the movie. Some scenes have been reordered, but only one significant plot point was cut. In contrast, when Peter Jackson adapted the Lord of the Rings trilogy for the screen, he cut significant portions of the story. Tolkien fans howled at the omission of Tom Bombadil and the Scouring of the Shire, but the movies were the better for it. These cuts, combined with the Lord of the Rings films’ greater length, and the fact that Tolkien’s work was less plot heavy in the first place give the films of the Rings trilogy a very different pace. Fantastic scenes and creatures remain on the screen long enough for the audience to appreciate them. Secondary characters are well developed and not every line of dialog is dedicated to advancing the plot. The Golden Compass desperately needed similar treatment. Cutting some of the plot points, adding another hour to the running time, or some combination thereof would have made this a much better movie.

The one major plot point the filmmakers did leave out is the book’s ending. Since I want to avoid spoiling the book, I’ll just say that the book’s unexpected and fairly brutal conclusion plays a big part in setting the tone for the entire trilogy. Omitting it fundamentally changes the story in a way that cutting some of the intermediate plot points would not have. This is one plot point I would rather they hadn’t cut.

The Golden Compass had the potentially to be a good movie, but the relentless pacing and poor decisions on how to adapt the book for the screen have turned it into yet another disappointing fantasy film.

A Phantom Ranch Christmas

Just before Christmas my parents and I hiked down and spent a couple of nights at Phantom Ranch. The plan was to hike down the South Kaibab, spend the next day doing a day hike, and hike back out the Bright Angel on the third day.

Down the Kaibab

We didn’t get going until about 11 o’clock the first day. The forecast was for morning snow and we were hoping that starting later in the day would give us better weather. The trip down the South Kaibab is relatively short, only about seven miles, so we figured it wouldn’t take a whole day. As it turned out, the snow stopped considerably earlier, so we could have gotten an earlier start. I forgot my camera and had to drive back to get it, so I didn’t get started until about 11:30.

Even before I learned it would snow the night before our backpack, I figured it would probably be snowy or icy on the upper parts of the trails. I picked up a pair of Yaxtrax at REI before coming to the canyon. They seemed to work a lot better at providing traction on snow and ice than the icewalkers I’ve used on previous occasions. In addition, think they’re probably better in areas of patchy snow, when you’re moving back and forth between snowy/icy surfaces and bare ground. Snow continued off and on all the way down to Cedar Ridge. In fact, the sliperiest portion of trail was probably the path leading up to the Cedar Ridge outhouse.

I caught up with my parents on Cedar Ridge where we rested a bit and took a couple of pictures before continuing on. I considered taking the picture of the raven with Wotan’s Throne in the background, which would have been mythologically appropriate, but it was shadowed by a cloud and wouldn’t have made a good picture.

Descending through the redwall I met up with a fellow geographer who works for the USGS. It’s a small enough world that we turned out to have several acquaintances in common, and I ended up talking with him all the way down to the tipoff.

At the tipoff there were a trio of female Desert Bighorn Sheep browsing on the sagebrush. Bighorn Ewes aren’t as impressive as the rams, since their horns are much smaller and less curled, but they are still an interesting, and fairly uncommon, sight.

We started down into the Inner Gorge about 3:00, but this time of year the low sun angle meant leaving sunlight behind. It started getting kind of chilly at this point, even with the long underwear I was wearing, so we hurried down the trail towards Phantom Ranch. We did stop a few places to look at some of the geology. Here the South Kaibab passes throught the Grand Canyon supergroup, a set of colorful sedimentary rock layers that are found between the Tapeats Sandstone of the Tonto Plateau and the Vishnu Shist and Zoraster Granite of the inner gorge. After crossing the Colorado on the black bridge, we stopped briefly to look at the Anasazi ruins there and found our way to the bunkhouse.

The Bunkhouse

My parents work for the park, so rather than staying in Bright Angel Campground or paying a fortune to get a bed at Phantom Ranch, we were able to stay in the trail crew bunkhouse. At other times of year, this is occupied by trail crews working on the canyon’s maintained trails, but when they’re not in residence it’s open to other park employees for a nominal fee. The bunkhouse was recently rennovated, and it’s very nice. Hardwood floors, a fully equipped kitchen, and a dozen bunks in two bunkrooms.

Since temperatures were dropping into the 30s at night, the bunhouse’s electric heat was quite welcome. As were the electric lights, particularly given how little daylight there is this time of year. Staying in the bunkhouse also lightened our packs considerably by allowing us to go without tents, foam pads, stove, and utensils. However, the best feature of the bunkhouse was probably the flexibility that the kitchen gave us with the menu. Normally our backpacking food is pretty bland (oatmeal or granola for breakfast, noodles or rice with some sort of canned meat for dinner). On this trip we got things like biscuts, pancakes, popcorn, and other stuff quite different from our usual backpacking fare.

The soft mattresses in the bunkhouse were also nice for my aching calf muscles. An 11:30 start gave enough time to get down to Phantom Ranch before dark, even when the sun sets before 5 o’clock, but it necessitated a pace that really trashed my downhill muscles. This was definitely a night that required some vitamin I.

Ribbon Falls

The next day we slept in a bit, had our biscuts for breakfast, and set off for a day hike. My parents gave me the choice of where to go, since they’ll be back down here for a hike on the Clear Creek Trail in a few months. My basic choices were to hike up the Clear Creek Trail to an overlook with a nice view of Phantom Ranch, or to hike up the North Kaibab to Ribbon Falls. The trail up to the overlook was much shorter, but my calves were still hurting from our hurried descent yesterday. A steep uphill hike to the overlook, followed by a steep descent back to Phantom Ranch didn’t seem too appealing. The hike to Ribbon Falls, on the other hand, was much longer at twelve miles round trip. However, aside from the Tonto Trail, the North Kaibab upstream of Phantom Ranch is probably the levelest stretch of trail anywhere below the rim. So I decided we would go with Ribbon Falls.

The trail follows Bright Angel Creek quite closely, crossing back and forth several times. Some of the bridges are made of aluminum, which might seem like an odd and expensive material for a footbridge, but really makes quite a bit of sense when you consider that all the materials for the bridges were brought in my helicopter. This day hike was a rather different experience than my last trip on this part of the North Kaibab. About ten years ago, in late may, I hiked it as part of a north to south rim to rim backpack. This part of the trail was the last six miles of a fourteen mile day coming down from the north rim. The sun was beating down on the black vishnu schist, turning the Bright Angel Canyon into a 100 degree oven. On this hike I’m wearing long underwear, a fleece and a jacket for the entire day. The only direct sunlight we got all day was up near Ribbon Falls where canyon opened up to more of the sky.

Unfortunately, the low angle winter sun made this a less than ideal time to see Ribbon Falls. When I first spotted it, the falls were about to go into shadow, and it was mostly shadowed by the time we worked our way over to it. Stems of grass near the base of the falls were coated in ice up to half and inch thick where spray had landed and frozen. Needles to say, the idea of going and standing in the spray from the falls wasn’t as appealing as it was in the summertime.

While the falls weren’t the attraction they would have been in the summer, this hike did give me a chance to appreciate the Bright Angel gorge in a way that I didn’t have the chance to when I was staggering down it in triple digit temperatures. There are parts of the gorge that are almost like a slot canyon, particularly the Phantom Creek side canyon. However, the feel is very different since this slot canyon is carved through black schist shot through with intrusions of pink granite, rather than the usual slot canyon sandstone. There were some places down near Phantom Ranch where the vishnu was heavily eroded, pockmarking it with holes and leading me to make the inevitable, and groan inducing, “Holy Shist!” joke. I also got some decent pictures, something the rushed pace didn’t leave much time for on the first day.

Up the Bright Angel

After another nice night in the cabin, we ate our pancake breakfast and cleaned out the bunkhouse before leaving. We crossed the silver bridge and headed down river on the Bright Angel Trail. On the way up the Devil’s Corkscrew (a set of switchbacks where you climb from Pipe Creek up to Garden Creek) we saw a pair of California Condors. These aren’t the first condors I’ve seen in the park, three or four of them were flying around near Cedar Ridge last spring and I’ve also seen one out at Navajo Bridge in Marble Canyon. These weren’t quite as impressive since they were just perched on the cliff rather than airborne showing off their enormous wingspan. Still, they’re pretty neat birds (if somewhat ugly looking) and it’s not every day you can see an endangered species in the wild.

The hike up to Indian Garden was otherwise uneventful. We stopped there for a break and were accosted by a tiny bird hopping around, rather than the usual squirrels begging for handouts. The bird even hopped up on by boot! I guess it was pretty acclimated to human contact.

After Indian Garden we were back in the shade again, so it got quite a bit cooler. Given the 3000 foot climb up to the rim, this was actually quite welcome. Snow started showing up in patches around the 3 mile resthouse and I had my Yaktrax back on after the mile and a half resthouse. As usual, the hike out of the canyon was really less strenuous than the hike down into it. People who haven’t hiked mile after mile of steep downhill may not believe it, but going uphill really is easier.

While that’s the end of the hike, I did take a few nice pictures on the East Rim Drive on my way out of the park a couple of days later.