EXERCISE 2 -- "G + I = Cool"

Environmental Resources 372:362
Intermediate Environmental Geomatics

Due Monday, February 9th

---

So you can get around in ArcGIS, huh? As you will see, you have only just begun to tap its resources. As we have and will discussed in class, one of the most significant powers of GIS (and ArcGIS) is the mix of G (geographic or spatial data) and I (information or attributes or tabular data). This powerful mix provides us with great analytical potential.

Tables

In ArcGIS, Tables are generally the I that match up with our G. As you've seen before, many of our layers include some tabular attribute data. We can see this by clicking on Preview in ArcCatalog or opening the Attribute Table In ArcMap. In ArcMap when you select individual records, whether they are for points, lines, or polygons, they should become highlighted in both the view and the table. Plus, as you learned previously you can select records using the selection tool, or the identification tool. When describing tables we will refer to "records" and "fields". A record is all of the different attributes for a single feature (point, line, polygon), and is contained in a single horizontal row of a table. The fields are contained in vertical columns, and each field represents a different attribute. In our states layer, each record represents a different state and with fields containing data like name, area, and identification numbers.

In ArcMap add \\ad-rsc\data\teach\intgeo\ClassWork\avdata\usa\counties and states. Outline the counties with the state boundaries. What type of information do we have for each state? For each county? Select counties named Washington – how many are there. How many of the counties named Washington have a 2003 population density greater than 10,000?

Note the power you have at the tip of your fingers when you right click on the field headings. You can now sort the table's fields and create summary tables (worth exploring after class). For example, you can explore which state has the most counties.

Information Overload – Relating and Joining

Did you notice how little information we had about each of the states in our table? How can we change that without typing in new data for each record? Answer: Join or Relate!

Joining is the manipulation by which we appear to add fields of data from another table to an existing table. This works really well when we have a one-to-one match of our tables - a single record in the destination table matches with a single record in the source table.

Relating is used for a one-to-many relationship. For example, if you have 10 wells in the county that you sample each week, then in your .dbf table you would make each sample a separate record so that after 5 weeks you have five different records (many) for each (one) well.  In relating tables, the tables are not actually combined but rather linked such that when a record in your source table is selected the matching record in the destination table is also selected.

Let’s delve deeper into joining and relating information in ArcMap:

• Open the table for the states layer. Notice that it does have population by state but not income or gender.  Add the table located at \\ad-rsc\data\teach\intgeo\Classwork\avdata\usa\tables\stdemog.dbf.  Notice that when a table is added to a map, the table of contents (TOC) view switches from Display to Source. 
• Open stdemog.dbf and compare it with the states table. When the table is opened you will see that it has lots of new fields, but also has the STATE_NAME and STATE_FIPS fields in common with the states layer. Notice how many records are in the table. Is it the same or different than in the states layer?  Will this give us a one-to-one or one-to-many match between our new demographic table and original layer table (states.shp)?
• Close the tables and in the TOC right-click on the states layer. Select Joins and Relates, and then Join to bring up the Join dialogue box.  Fill in the boxes using the STATE_FIPS field in both the state layer and in the stdemog table.  (Be sure that you are joining attributes from a table rather than a location.)  Hit OK.

• Now look at the state layer table. Use your Layer Properties to make US maps of all sorts of demographic information. Joins and relates are temporary to your map and are not permanently added to the file, so if you wanted to keep the shapefile and the table combined then you would need to save the states shapefile as a new layer (right-click and select Save as Layer File) or create a new shapefile (right-click and select Data | Export Data). When you are done, remove all joins by right-clicking on the states layer and selecting Remove Joins stdemog.
• Now add the codemog.dbf table. Notice that there can be many county records for a state while the state layer contains one record per state.  This is an example of a one-to-many relationship.  If you were to ‘join’ the codemog table with the state layer, ArcMap would find the first matching record in the codemog table, join its attributes to the state layer and ignore any further matching records.  What to do?  We need to use a “Relate”.  Close the codemog table and for the state layer bring up the dialog box for Relating.  Carefully read and fill in the boxes in the dialogue box including a name of your choosing and hit OK.
• What?  No change in appearance!!  Select a state – say Montana.  Open the attribute table for the state layer and click on Selected to show selected records.  Click on Options | Relate Tables | the relate you just made (mine is ‘County Data: codemog’). The codemog table should appear. At the bottom of the codemog table click on Selected to see the counties in Montana. 
• We can also work backward by relating in the other direction.  For example, which states contain counties named “Boone”?  To find out, open the codemog table and click Options | Select by Attribute. Now create an SQL query to select records from codemog where the county is named “Boone”. (Remember to click Get Unique Values for the Name field.) Once the counties are selected, go to Options | Relate Tables | County Data: stdemog. In the states table you should see the states that have counties named “Boone.”
• Remove the Relate between states and codemog. Try relating the states.shp with the counties.shp.

Editing Tables

Often you will find that you need to add additional data in your database tables. For example, let’s find out how the number of children under 5 per household in each state. This information does not exist in the stdemog table, so we’ll have to calculate it ourselves using the data we already have.

• In ArcMap, join the stdemog.dbf table to the states layer, then open the states layer attribute table and go to Options | Export to export it to your home directory as stedit.dbf. Make sure you export all records and not a selection. Click ‘OK’ when asked if you want to add the new layer to your map. Now you should see the new table in the TOC located in your folder.
• Open stedit.dbt and go to Options | Add Field. Call the field Und5perhh. It should be type ‘float’ with a precision and scale of 4. Click ‘OK’.
• 
• We will populate the values for the new field using the Field Calculator, which is very useful for making quick calculations but it can also bedangerous as it will write over your data with no option to undo. Right click on your new field and select Field Calculator. We want to divide the number of children 5 years old and younger by the number of households in each state. Come up with the SQL statement to do this.      
• Remove all joins and relates to the states layer. Next, join your edited table (stedit.dbf) to the states layer table.
• Look at the map of the under 5 year olds per household by state (maybe classified by Standard Deviation)

Who would have ever thought we'd see that kind of spatial distribution?

Now let’s say we want to add new records to our table to show some of the United States’ territories, such as Puerto Rico.

• In ArcMap start an edit session. You may have to turn on the Editor toolbar: View | Toolbars | Editor.  Start an edit session by clicking on Editor | Start Editing. Be sure to select the folder that contains stedit.dbf.
• Open your stedit.dbf file*, move to the end of the records and add a record by typing into the blank spaces in a new row. You don’t have to add information to every field; just fill in the name and abbreviation for now. (*If you get an error in this step, you have two solutions, neither elegant.  1. Try closing ArcMap and starting it again. 2. Copy the stedit file to c:\temp and proceed)
• You could also change individual values in the table, if you received more up-to-date information for one of the states.  When you are done, you will want to save your edits. Close the table and go to Editor | Save Edits. To stop editing, go to Editor | Stop Editing. If you make a mistake, stop editing without saving your edits if ‘undo’ doesn’t work.

If you wanted to, you could type in new text values or numbers for every record. You could add or delete a record or a field. But please, whatever you edit, only do it to your own copies of files in your own directories. Remember that the files in \avdata\ are shared by everyone in the class.

Assignment 2

a. Which state has the highest average family size?

b. Which county has the greatest total number and highest density of Asians (fieldname [ASIAN]) and American Indians and Eskimos (fieldname [AMER_ES])? Which has the least and lowest? Fill in the following table. We've provided an example to get you started. If there's more than one county with the same number or density, just provide the name and value for one county. Use a precision of 8 and scale of 2 for your density field, which should be of type Float.

		Total Number	Density (per sq. mile)
Asians	Highest	Los Angeles, CA - 1,137,500
	Lowest		Greene, AL - 0.0
American Indians and Eskimos	Highest
	Lowest

c. Using the county demographic data, develop a definition of ethnic diversity. Using your definition, determine which counties have the highest concentration of ethnic diversity and which have the lowest? Make a map, and explain how you derived your measure of ethnic diversity. We don't care about how statistically or technically correct your definition of diversity is, just be sure to explain to us how you derived it.

d. Which county is the largest and what state is it in? Which state has the most counties?

e. You are a single female and you want to increase the odds that you will find Mr “Right”. You want to find a county where there are more males than females. Where are your odds the best? What if you are 33 and you also want to live where there are more people in your age range? Using any and all of your mapmaking tricks, create a map that shows the places which best meet both criteria. 

Please turn in the maps and the answers to the above questions on paper by the start of class on Monday, February 9^th. Be sure you name is on all pieces of unstapled paper.