...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

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:

• I am mapping more than one state and my data are still in different files (one for each state).
  
• When I calculate % population change, ArcGIS chokes and claims I am dividing by 0.

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.

• Click once on Counties to highlight the layer.

• From the Selection menu choose Select by Attributes.

• A window appears which allows you to construct a query that will select your state from all the states in the US layer.

• From the fields list double click STATE_NAME. It is added to the query window below, and all the available states are listed in the Unique sample values window on the upper right.

• Click once on the = sign to add it to the query.

• If your state is not in the Unique sample values window, hit Get Unique Values. Double click on your state to add it to the query.

• You now have a simple query: Select from Counties where "STATE_NAME" = 'Wisconsin' The quotes and syntax are all important in queries.

• If you have more than one state, say Wisconsin and Minnesota, select both. It's important that all your states are together on one layer. The syntax is thus: "STATE_NAME" = 'Wisconsin' OR "STATE_NAME" = 'Minnesota'

• Click on Apply then Close: only the counties in your state should be selected on the map. Saucy!

• Right click once on the counties layer, and from the menu select Data and Export Data... As you are creating a new map layer, navigate to your ArcGIS folder

• You have to create a Geodatabase in your personal ArcGIS folder before saving the map. Click on the New File Geodatabase icon in the upper right corner of the window (a yellow cylinder). Name the file geodatabase something logical (mine is Wisconsin Geodatabase). A geodatabase is a storage space for ArcGIS - the digital map files, tabular data, etc.

• You can now save your exported data layer in the newly created File Geodatabase. Please change the name to indicate the state: mine would be wi_counties.shp As usual, never delete the file extension.

• You should be asked if you want to add the new data: hell yes!

• Un-check the Counties layer and you should see your state. Remove the Counties layer (the entire US map).

• Save your work.

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.

• Right-click on the data frame (probably called Layers if you did not rename it) and select Properties.

• Select the Coordinate System tab.

• Projected Coordinate Systems >> Continental >> North America will take you to a list of awesome choices.

• Select the projection you think appropriate (please consult the Making Maps book for guidance), then right mouse click and select copy and modify.

• In the window that pops up, change the Central_Meridian (probably -96 to the central meridian of your state or states. You can use Google Earth or an atlas (!) to figure out the approximate central meridian. Wisconsin's approximate central meridian is 90 degrees West so I changed -96 to -90 and hit Apply. If the state seems to be centered (not tipped) hit OK and OK.

• Save your work.

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.

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.

• Right click on your state layer and select Joins and Relates and Join...

• You will Join attributes from a table so make sure that is selected in the window at the top of the page.

• In 1: Choose the field... scroll down to FIPS which is the column (field) of county FIPS numbers in the attribute table associated with your state map.

• In 2. Choose the table to join... you must let ArcGIS know that you want to join data from outside of ArcGIS: click on the folder icon and navigate to your .DBF file. Use the copy in your ArcGIS folder.

• In 3. Choose the field... do that...select FIPS which is the name of the column (field) of county FIPS numbers in your .DBF file.

• Hit OK.
  • If asked to Create an index? Sure! Why not. Can't hurt.

• Open up the attribute table for your state, and you should see all the columns (fields) of your historical Census data joined to the end (right) of your attribute table. Carefully examine the attribute table, and check that the data has joined correctly. If this is not the case, or if there is a problem, please let your instructor know.

• Save your work.

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.

• Right click on your state layer and select Properties.

• Select the Fields tab.

• Keep Name and FIPS from your state file, and of course all the population fields (Y2000, Y1990, etc.) but make the rest invisible. Hit Clear All and select just the fields (columns) you want to see. You can make the invisibles visible again by returning to this dialog box.

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:

• 1910 population - 1900 population

• then divide that number by 1900 population

• then multiply by 100 to get a percent for population change from 1900 to 1910

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).

• Open up your attribute table

• Using the Table Options button (upper left corner) of the table window, select Add Field...

• Name it thus: chg_00_10 and set the Type to Float.

• Right click on the field name and select Field Calculator. You will build up a statement here which will calculate population change from one decade to the next. Much like with the query used earlier, syntax is vital. You are, in essence, using ArcGIS like Excel at this point.

Copy the equation below into the field calculator:

• ( ( ( [ Y1910 ] - [ Y1900 ] ) / [ Y1900 ] ) * 100 )

• Click and drag highlighting [Y1910] then double click on the actual title of the field (in the Fields list) to replace with the actual name of the field. This is vitally important! Do the same for [Y1900].

• What the flap? If you get an error, make sure you are selecting the brackets and the name of the column, otherwise you will get an extra set of brackets and ArcGIS no likey.

• Hit OK

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

• Save your work.

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:

• Save your work.

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:

• .DBF / Dbase file
• Select by Attributes
• Query
• Fields (in a Table)
• Records (in a Table)
• Attributes
• Relational Database
• Join function
• Monitor Fire
• Calculate / Field Calculator

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

• Embedded image of the three states together (between steps 4 and 5)
• Definitions from the last paragraph of the exercise
• .dbf file of your data - when you have successfully joined your DBF and calculated all population change data
  • Open your .mxd file
  • Right-mouse-click on the layer with your state(s) map
  • Open attribute table
  • Click on the right menu (table options), Export. Change file format to .dbf
  • Save the .dbf with your last name (eg., krygier.dbf) and email as an attachment to me.

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