Geography 353 Cartography and Visualization

...to Geog 353 Main Page and Course Description
...to Geog 353 Syllabus
...to Geog 353 Course Schedule and Lecture Outlines
...to Geog 353 Laboratory Information and Student Projects


Geog 353 Lab 5: Data Processing, Part 2
Update: 9/16/19
50 points
ASSIGNED: Monday September 30
DUE: Wednesday October 7


The data (historical population figures, by county, for your state) downloaded from the WWW must be processed in order to use it in ArcGIS. There are two basic data processing tasks involved in this project. In Lab 3, you used Excel to clean up and combine your data into an Excel file (.XLS). You then used Open Office to convert the Excel file into a .DBF file.

In Lab 5 you will import the .DBF files into ArcGIS. ArcGIS does support .XLS files. But the .DBF file seems to work with less hassles, so we will work with the .DBF file. Once in ArcGIS, you will join your data to a county map of your state or states. You will then use ArcGIS's spreadsheet/database capabilities to calculate the data on population change that you will eventually map.

Lab 5 Goal: Your .DBF files from Excel successfully imported into ArcGIS, linked to a county outline map of your state or states, and population change data calculated.



PROBLEMS and SOLUTIONS: Below find a few common problems you may experience when joining tables and processing data in this Lab. Clicking on the problem here will take you to the solution below:



The Details:

1) Start by tidying up your 353 folders: using Windows Explorer delete old ArcGIS files (such as those generated in Lab 4) from your ArcGIS folder. If you want to save these files, please create a separate folder for them.

Make sure that you have your .DBF file, correctly processed and formatted, from Lab 3. Theses should be in your Data folder. You should back up both these files on a flash drive or some other device (it is always good to keep a back-up copy of your data files, especially those you have put a lot of work into).


2) Enter the realm of ArcGIS by starting ArcMap. Under the File menu hit New. Under File also Save. Navigate to your ArcGIS folder, and save the file there. I would call mine Wisconsin.mxd (after my home state, which sed to be, but is no longer a better state than Ohio).


3) Now we need to get a county map of our state or states. This, of course, will be a Layer in a Data Frame in ArcMap's Table of Contents. Under File select Add Data... or just hit the icon (yellow box with +). You may have to Connect to folder (as learned in the last lab). The file you need, counties.shp is in the ESRI2000 folder on the C: drive, in the USA folder.


4) You should be able to see the counties.shp layer in the data frame. Two problems should be FRIGHTENINGLY evident. First, we do not need the entire US, only our individual state or states. Second, the map projection is weird. Let's extract our state from the United States first, using the query and select features of GIS.

What you want, in essence, is a subset of the counties.shp layer. One way to do this is to select your state, and save it to a new layer file.

Once you have selected your state's counties, you need to save those counties in a new map layer.



Do this for xtra phun: Using the counties layer and the selection process reviewed above, please create a subset of at least three states together on the same layer. Some of you have to do this for your project, and all of you should know how to do it anyway. Save it as a layer (appropriately named), save a .jpg and place it in the Lab Log.


5) You should see your state (or states) on the screen. Change the projection of your map, and its center, to something more appropriate than the default (as discussed in the lectures on Map Projections / Geographic Framework). Note which projection you choose and why in your Lab Log.


Thrilling!: you now have your county level map, in a proper map projection.



Next step: Linking your Census data (.DBF file) to your base map.

6) Right click on your state layer and select Open Attribute Table. This is the attribute data that ESRI provides with the county level maps. This Table is akin to a simple version of Excel. You won't need most of this data (so we will hide it) and we need to join your population data for the 1900 to 2010 censuses (censi?) to this data.




Pause and ponder where we are: we have a base map (and linked data in a Table) in ArcGIS. We also have a .DBF file of the additional data we need to link to both the base map and data for our state. This is a very common occurrence in GIS and computer mapping. So what to do?

One of the most important ways of combining data files is through a common column (field) of data. This is the idea behind a relational database. It is vital, however, that the columns (fields) are EXACTLY the same: that the data in the column (field) is the same and that the format - numeric, text, etc., is the same).

Are there any columns of data that your .DBF files and the table for counties.shp share? Obliviously, you have to plan ahead in any GIS or mapping project and make sure that you have a common column (field) if you are going to join tables of data. We did that: FIPS and the county names are (we hope) the same. Recall the FIPS codes are unique numeric identifiers for each geographic unit (counties, states, census tracts) in the country.

The next step is to join your .DBF file to the data associated with your state map. This might be quick or it might be a nightmare! Worst case scenario, a failed join may start a monitor fire.

Please pause to ask for divine intervention from the God or Gods or Goddesses or Godettes of your choice, to ensure that our data joins with minimal mahem.


PROBLEM: I am mapping more than one state and my data for each state are still in separate files. This means you skipped a step in the last exercise. Badness! If you are mapping more than one state you should combine your census data in a single file in Excel then open and save it as a single .dbf in Open Office before you bring it into ArcGIS:

  • Open Excel
  • Create a New Excel file (very important!)
  • Open your (alphabetically) first state .XLS file, select, and copy all the data including the headers (FIPS, Y1900, etc.).
  • Paste into the new .XLS file
  • Open your (alphabetically) second state .XLS file, select, and copy all the data, but NOT the headers.
  • Paste into the new .XLS file and save.

  • You now have a combined .XLS file. Save and open with Open Office and save a copy as a .DBF file.

Join the new combined file in ArcGIS as noted in step 7 above.



Join the .DBF file.

7) Please make sure that your .DBF file is not open in Open Office and close the attribute table in ArcGIS. Your state or states should be visible on the screen in ArcMap.


Some of you will forget to do this important step, which when not done will screw stuff up later, so I have highlighted it for your convenience.


8) Export your state layer (map and attribute table): This combines your joined data attribute tables and will make it easier to work with your table.

  • right mouse click on the layer containing your state map

  • select data, then export data. Save the file in your file geodatabase created when you exported your state map earlier in this exercise.

  • add this new file as a layer in your ArcGIS project and delete all other layers.


If everything seems kosher at this point - cool.


9) Open and tidy up your Attribute table. Notice that you have many columns (fields) of data that you don't need. To make it easier to work with, hide all the fields you don't need: this won't change the table, only what you see.


At this point you have joined your historical census data to the existing ArcGIS Table which is linked to the county map of your state (or states). You have learned how to link different data files together (very common in GIS work) and how to view only part of a base map and data file (using queries and selecting).

Recall that our project entails mapping out percent population change. What we have done so far is appended total values of historical Census figures to the base map and associated table for our state. We can calculate population change from the data we have. Percent population change from, say, 1900 to 1910 can be calculated as such:

We could do this with pencil and paper, or crayon and construction paper, for each pair of years, or we could turn to one of the marvels of modern technology and let the computer calculate this additional data for us. We can do this in ArcGIS using ArcGIS's database/spreadsheet capabilities.


10) To complete the processing of our data, we want to generate some additional data (population change) from the data we have (total population).

Think about what you are doing: The Field Calculator is telling you that your chg_00_10 field - whatever number shows up there - is a number that results from the equation you build. You will not see the equation in the Table, but you will see the number that results from the equation.

Copy the equation below into the field calculator:

You should see your percentages. They could be negative (sometimes counties lose population) or could be very large (hundreds, even thousands percent increase).

Calculate the rest of the years (1910-1920, etc.) by following the same procedure of creating a field and calculating the values. Be extremely careful that you do this right; the syntax and order of the numbers matters much!


PROBLEM: When I calculate % population change, ArcGIS chokes and claims I am dividing by 0. You cannot divide by 0! If there are any zeros in your table, that means you forgot to change these to 9999999999 in Lab 3. You can fix this problem in ArcGIS by editing the table. Be careful, as any changes you make while editing an ArcGIS table are permanent. Do this:

  • Under the Editor menu (left side of screen, below File menu) select Start Editing. In the dialog box, make sure you tell ArcGIS which layer you want to edit.
  • Select the offending zero and change it to 9999999999 (ten nines).
  • Under the Editor menu Save Edits and Stop Editing.
  • Try calculating population change again.
  • Note: if this does not work you may have to un-join the tables; ask instructor!


When all your population change data is calculated:



In this exercise, you have learned how to join data you found on the WWW and processed in Excel (.XLS) then Open Office (.DBF) to an existing ArcGIS Table and base map provided by ESRI. You have also learned how to change the map projection of the base map, and how to view only part of the base map and data file (using query and select functions). Finally, you have calculated additional data (percent population change) using ArcGIS's spreadsheet/database capabilities. These are all common GIS and computer mapping activities.

Prepare to show your instructor your correctly linked data and your new fields (columns) of population change data on the lab due date. Make sure you hide all the fields we are not using. Also define the following ArcGIS terms in your Lab Blog:

Next: you will learn to make important decisions about how to classify and symbolize your population change data as a choropleth (value by county), dot, and graduated symbol map in Lab 6. But first, the beloved mid-term evaluation!


When you are done, by end of class on due date: email me the link to your blog entry for Lab 5. This should include



E-mail: jbkrygier@owu.edu

...to Geog 353 Main Page and Course Description
...to krygier teaching page.
...to krygier top page.

OWU Home
OWU Geology and Geography Home