Lab 06 - Selections Queries and Joins

As is more often the case than not, the information we need to answer a question are in separate places (read: shapefiles). Therefore, we need to join that data together. However, the concept of joins are communicated, implemented, and executed very differently within a computer/GIS context than they are in everyday language. Database joins are their own class in computer science/engineering, and we won’t cover all of them in this class. For this lab we will use two, a left join and geographic joins. You can think about left joins as joining two excel sheets together based on a common (or key) field. Spatial joins append entries that match the geographic filter (interest, buffer, ect.)

In this part we will execute queries by attribute, queries by space, and spatial joins. We will also conduct a simple analysis using these functions.

Materials

• Open ArcMap.
• Add bookstores.shp, counties.shp, and istates.shp from the Part01 folder to your data frame.
• Go to the data frame (Layers) properties and click on the General tab. Make sure the Map Units are set to Meters and the Display Units are set to Miles. Click OK.

• Go to Selection | Select By Attribute
  • Set the Layer to counties.
• Make sure the Method option is set to Create a new selection.
• The attributes for the counties layer (“FID”, “AREA”, …) are displayed in the upper half of the window. You can double click on the attributes of interest to make it part of the expression in the lower half of the window. In the middle are the various operators that you will use. On the right, you can click the Get Unique Values button, which will display all of the unique values for the attribute you selected.
  • An example of an expression is (“countyname” = ‘Douglas’), where Douglas county would be selected if we had a Kansas county dataset.
• Let’s select the counties where populations in 1990 were greater than 30,000 people.
  • In the box with the attributes, scroll down and double-click “POP1990”. Notice that it shows up in the lower box.
  • Now click the > button. In the lower box, type 30000 (no commas!) so that your expression reads: “POP1990” > 30000.
  • Note that you can always just type the entire expression yourself instead of double-clicking attribute names, etc.; the advantage of double- clicking attribute names, however, is that it reduces the probability of typos in your expression.
  • Check your expression by clicking the Verify button. If everything is correct, the program will tell you that “The expression was successfully verified.”
  • Click Apply once the expression is verified and then look at the map. You should see most of the counties selected.

• Go to Selection | Statistics. This function calculates statistics based on the attributes of the selected features. If you have five counties selected, for example, you can find out the average area of those counties by selecting Area in the Field menu and then looking at the statistics below it. Changing the attributes in the Field menu won’t change which features you have selected.
• Using the Select By Attributes and Statistics tools, answer Questions #1 through #5 on the assignment sheet. Note that the Select By Attributes and Statistics windows can be open at the same time.
• Clear the selected counties by going to Selection | Clear Select Features or by clicking the Clear Selected Features button.
• Close the Select By Attributes and Statistics windows.

• Open the counties layer properties. Click the Selection tab, click with this color, and select a bright red color. Click Apply and OK.
• Use the same procedure to make the bookstores layer selections green and the istates layer selections yellow.
• Use the Select By Attributes function to select Richland County.
  • Hint: Think about which attribute would contain the names of counties, select it, and then use the Get Unique Values button to get the list of county names.
• Now go to Selection | Select by Location… with the following parameters:
  • Selection method: select features from
  • Target layer(s): bookstores
  • Source layer: counties
  • Use selected features: (yes)
  • Spatial selection method…: are completely within the source layer feature
• Click Apply.
• All the bookstores in Richland County should now be green, indicating that they were properly selected.
• Go to Selection | Statistics to find how many bookstores are in Richland County.
  • Hint: Make sure the bookstores layer is selected!
• Answer Question #6 on the assignment sheet.
• Clear the selected features.
• Use the Select By Attributes tool to select Interstate 26 in the istates layer.
  • Hint: Don’t use the ISTATES_ or ISTATES_ID attributes.
• I-26 will be highlighted as yellow.
• Use the Select by Location tool with the following parameters:
  • Selection method: select features from
  • Target layer(s): counties
  • Source layer: istates
  • Use selected features: (yes)
  • Spatial selection method…: intersect (3d) the source layer feature
• Click Apply.
• The counties that I-26 passes through will turn red.
• Use the Statistics tool to find the mean median home income for those counties. (Yes, the mean of the median.)
• Answer Question #7 on the assignment sheet.
• Clear the selected features.
• Let’s find bookstores that are within 15 miles of I-26. As before, select I-26 from the istates layer.
• Use the Select By Location tool with the following parameters:
  • Selection method: select features from
  • Target layer(s): bookstores
  • Source layer: istates
  • Use selected features: (yes)
  • Spatial selection method: are within a distance of the source layer feature
  • Apply a search distance (this will be grayed out): 15 miles
• Answer Question #8 on the assignment sheet.
• Clear selected features.
• Answer Question #9 on the assignment sheet.
• Save your map document.

In this part we will execute a spatial join and then conduct a simple analysis. The Loma Prieta earthquake (Richter 7.1) occurred on October 17, 1989 at 5:04pm Pacific Time. The epicenter was 37° 02' 00" N and 121° 53' 23" W. A large number of residential homes were damaged by the earthquake, and many were destroyed. You will be investigating the distribution of damaged homes in relation to active surface faults.

• Open a new ArcMap document or add a new data frame.
• Add shomes.shp, sszpoly.shp and insustatus.dbf from the Part02 folder to the workspace. If you get a warning about an Unknown Spatial Reference, just click OK.
• Set the data frame map units to Meters and display units to Miles.
• Open the attribute table of the shomes layer. Answer Question #10 on the assignment sheet.
• Right-click the shomes layer and select Joins and Relates | Join…. Use the following parameters:
  • What do you want to join to this layer?: Join attributes from a table
  • Choose the field in this layer…: PID
  • Choose the table to join…: insustatus
  • Choose the field in the table…: ID
  • Join Options: Keep all records
• Click Validate Join. This is similar to the Verify button we saw in the Select By Attributes tool; it makes sure that all the right conditions are in place for the join operation to execute successfully. When you become more comfertable with the workflow/system, this is something you may be tempted to skip, but unless you are sure that the join will work, it’s usually best to just check.
  • If you are asked whether to create an index, click Yes.
• Open the attribute table of the shomes layer. Answer Question #11 on the assignment sheet.
• Use the rest of this handout to fill in Question #12 on the assignment sheet.
• Right-click on shomes and select Selection | Select All. Write down the total number of surveyed homes in the table on the assignment sheet.
  • Hint: Use the Statistics* tool or just look at the bottom of the attribute table.
• Use Select By Attributes to select the homes that were insured before the Loma Prieta earthquake: Using the PRELOMAINS attribute, 1 = Insured and 2 = Not insured. Write this information down in the table.
• Do the same for homes that were insured after the earthquake (POSTLOMAIN).
• Clear the selected features.

• Right-click the shomes layer and select Joins and Relates | Remove Join(s) | insustatus.
• Right-click shomes again and select Joins and Relates | Join… and use the following parameters:
  • What do you want to join to this layer?: Join data from another layer based on spatial location
  • Choose the layer to join to this layer…: sszpoly
  • Each point will be given all the attributes of the polygon that: is closest to it (Don’t press OK yet.)
• Click on the open folder icon and save the output housepolysj.shp in your Part02 folder.
• Now you can click OK
• Open the attribute table of your new layer and notice the Distance attribute that was created. This tells you how many meters that particular point is away from the nearest polygon.
• Select all features in housepolysj and use Statistics to find the average (mean) distance between the homes and San Andreas Special Study Zone (SSZ). Write this down in the assignment sheet table.
• Right-click the housepolysj layer and select Joins and Relates | Join…. Use the following parameters:
  • What do you want to join to this layer?: Join attributes from a table
  • Choose the field in this layer…: PID
  • Choose the table to join…: insustatus
  • Choose the field in the table…: ID
  • Join Options: Keep all records
• Use Select By Attribute on housepolysj to determine the number of homes that are within 4 miles of San Andreas SSZ (1 mile = 1609.344 meters). Write this down in the table.
• Determine the average distance to the San Andreas SSZ for the homes that are within 4 miles of San Andreas SSZ. Write this down in the table.
• Open the housepolysj attribute table, click the Table Options button (upper left corner) and select Switch Selection. This will invert your selection to (logically) select everything greater than 4 miles from the SSZ.
  • Note: There is also a button on the top of the mune that inverts your selection.
• Determine the number of homes that are farther than 4 miles of San Andreas SSZ. Write this down in the table.
• Determine the average distance to the San Andreas SSZ for the homes that are farther than 4 miles of San Andreas SSZ. Write this down in the table.
• Using similar steps as before to determine how many homes in the housepolysj layer were insured before and after the earthquake. Write this down in the table and then calculate the percentages for each catagory.

Submit the answer sheet on blackboard.