[UPDATE: WE NO LONGER OFFER FREE TRIALS FOR THE WEB APP]

You have 2 options for figuring out which Census tracts are in your city. Important Note: What you and me and other normal people would call cities or towns, the Census Bureau calls places.

Option 1. Census Tracts by City for Complex Cities (i.e. New York City, Los Angeles, Chicago)

    • Go to the Census Bureau’s website here: http://www.census.gov/cgi-bin/geo/shapefiles2010/main
    • Download the 2010 Places File for your State & unzip the file.
    • Download the 2010 Census Tract File for the county that your city is in & unzip the file.
    • Open the files in your favorite GIS. My current favorite GIS is the free, light-weight MapWindow.
    • Use the GIS to calculate which Census Tracts intersect your city. I wrote down step-by-step instructions for how to do this via Map Window. You can download these instructions here.

These instructions are a great example of what really happens when you use open source GIS to solve a problem, because I ran into an error when I was using MapWindow. So I included the error & 2 different ways to solve the problem in the instructions.

Also, you can follow the instructions above to figure out block groups by city as well as Census blocks by city.

Option 2. Census Tracts by City for Simple Cities (i.e. Worchestor, MA, Shoshoni, WY)

  • Sign up for a 7 day free trial of Cubit.
  • In Cubit, type the name of the city into the Find Box to zoom to your city (see the Orange Circle).
  • Zoom out slightly until you can see the entire City in the interactive map. The city outline will be a slightly different color than the rest of the map (see the Orange Arrow-the darker shade is the city limits).
  • Click on the Polygon tool and draw a polygon slightly inside of the city limits (see the Orange box). Select Tract as the Geography Level if you want Census tract data (see the Purple box). Click Save.

On the next page, you’ll get a labeled map that shows the Census tracts in your city as well as demographic data reports.

Note: if you save your project and Cubit returns a Census tract that you can see is outside of your city, don’t panic. All that means is that some part of your line touched an area that is outside of the city. Click on the Edit Project button. Then, click on the Polygon button. Now, click on the points on your original line to adjust your line inside of the city so that you’ll only get tracts inside of your city. If nothing happens when you click on your original line, try clicking on different points along the line.

Again, you can follow the instructions above to figure out block groups by city as well as Census blocks by city.

The Really Nerdy Background

This blog post came about when I received a request to pull all Census tracts for a city. “No problem,” I thought, “I’ll just go to the Census 2010 data and look up the Places code for Census tracts.”

So I pulled up the Census 2010 data in MySQL and displayed all of the Census Tracts for my state of interest, Massachusetts. Great! There 1478 Census Tracts in Massachusetts. Then, I selected all Census Tracts in Massachusetts where the Place FIPS was not empty. Oh no! That returned 0 rows! That means there are no relationships in the Census 2010 data between Census tracts & Place.

Just to make sure I wasn’t missing something, I double checked the Census 2010 Redistricting PDF Summary Level Sequence Chart, and sure enough, there were no SumLevels with relationships from Cities to Census Tracts (or to BlockGroups or to Blocks for that matter). But don’t take my word for it–click on the link above in this paragraph and go to Summary Level Sequence Chart on page 4-1 to see for yourself.

So you’ll have to use either of the 2 methods above to figure out which Census tracts by City instead using relationships in the raw Census data. Was this post about Census tracts by City helpful or confusing? Any questions here?

3 replies on “2 Ways to Figure Out Census Tracts by City”

  1. So glad I found this – proof that I’m not the only one trying to DL data and work with it in MySQL. Did you have any trouble with the collation of the last column of the second .pl file?

    I am downloading data from all states, but some of them aren’t playing nice. I can see all of the data in notepad as a .pl file, then I save it as a .csv and try to import it, but get the last column truncated no matter what I try. So far I’ve lengthened that column (a VARCHAR), changed it to a BIGINT, and changed the collation to utf8_general_ci but still to no avail. Only the first line goes in on import.

    Tried turning off STRICT_TRANS_TABLES in sql_mode variables, too. Still not working. I get SQL Error 1265: Data truncated for column c150 (the last column in the table).

    Any advice?? I will *so* follow your blog, twitter feed or anything else if you can answer this for me … ;-)

  2. Hey loranstefani,
    I’m glad you found this too!

    I don’t remember having that problem with the Census 2010 data, but I have had that problem in the past.

    The way I normally fix it is pretty lame, but it works.

    1. Convert the file to .csv,
    2. Open it in Excel,
    3. Add a comma or some other character to the last column of the file & save
    4. Reimport into MySQL and add an additional column to your database
    5. Once you’ve imported the data, you can drop the last fake column.

    Hope that helps!

    Kristen

  3. I was stubbornly persistent and figured out late in the day that I can change “lines terminated by ‘\r\n'” to just \n since the Unix platform doesn’t carry over a carriage return.

    Thanks for the response and the follow-up!

Comments are closed.