Massachusetts Institute of Technology Department of Urban Studies and Planning 11.520: A Workshop on Geographic Information Systems 11.188: Urban Planning and Social Science Laboratory Lab Exercise 3: Database Operations in ArcMap Due Lab 4 Overview In this exercise, you will use the Cambridge housing sales and census block group data to explore ArcMap's database query capabilities involving query selection, tabular joins, and spatial joins. The purpose of this part of the lab exercise is to introduce ArcMap's ability to perform analyses that depend upon data queries and the juxtaposition and manipulation of spatial features. Much of the analytic power of a GIS comes from its capacity to compute and then manipulate and visualize the geospatial relationships among selected events and locations. We'll illustrate a few basic spatial analysis techniques using data sets from the previous ArcMap lab exercises --namely, the 1989 housing sales data for Cambridge and 1990 Census data for Cambridge block groups. Subsequent labs will cover additional capabilities. Also this exercise provides opportunities to set appropriate data coordinate system, which was covered in the lecture. In this particular lab you will turn in your answers by filling out a form. As you work on the exercise, please print out (or edit) the answer form and write in your answers. (1) Merging, editing, and saving tables: To do this lab, we'll have to learn a few more ArcMap techniques for merging and saving tables. There are hundreds of census variables and any Cambridge blockgroup map is likely to include only a small subset of them. To use other census data, we'll have to load these data into ArcMap and "join" them with the attribute table of the Cambridge blockgroup theme using a common geographic reference variable (such as the state-county-tract-blockgroup identifier, Stcntrbg). We may also need to compute new fields that normalize or otherwise combine multiple columns of the original data. But the official class datasets are "read-only" and ArcMap requires that you have write access to attribute tables in order to create and calculate new fields. So we'll also need to learn how to save local "writeable" copies of (extracts of) our class datasets.(2) Spatial Joins: Suppose we'd like to compare the sales prices of Cambridge homes with the socio-economic characteristics of their neighborhoods. For example, we might want to know whether the high-priced sales tended to be in neighborhoods with highly educated adults. By drawing a pin-map of high-priced 1989 sales locations on top of a Cambridge block group map, we may be able to see a pattern. The block group map can be thematically shaded by educational attainment levels. With this map, we can 'see' whether high/low-priced sales cluster in neighborhoods with, for example, more (or less) educational attainment. But the pattern may be misleading or hard to interpret and we may want to quantify the relationship to measure the degree of association and to tag the sales data with the some of the characteristics of their neighborhoods. Much of the benefit of using GIS software depends on its tools for cross-referencing and reinterpreting data based on spatial location. We'll defer until later labs the more general and advanced tools for tagging data based on spatial proximity and we'll focus in this lab on simpler spatial joins and buffer creation. (3) Setting coordinate system: So far, the coordinate system of the data has already been preset for your use. However, in typical settings, you may need to specify or change the coordinate systems associated with GIS datasets. In this exercise, you will learn how to apply a suitable coordinate system to your datasets. I. Setting up your Work Environment Follow the usual routine to set up your work environment: o Log onto Athena PC o Attach drive M: by using the DOS command "attach -Dm 11.520". o Launch a web browser and open the web page for the current lab. o Start ArcMap II. Examining Attribute Data As in previous labs, we will use Cambridge data for this exercise. In ArcMap, start a new map document and add Cambbgrp.shp in M:\www\labs\lab3 directory to the data frame. Open the data frame property window by doubling clicking data frame name layers, rename it to "Lab Exercise 3" and set the "Display Units" to miles. Remember: you need to set the map and Display units in every new view you create in order for ArcMap to interpret the coordinates properly and generate correct scale bars and distance measurements. Make a habit of doing this immediately after adding the first layer to a new map document. A. Simple Queries In an earlier lab, we've already learned how to use the Info button, , to provide attribute information about particular spatial features. We have also learned how to open an attribute table of the geographical layer.Now we will experiment with querying the data. Select "Select by Attributes" under the "Selection" menu. On the "Select by Attribute" window, make sure choose Cambbgrpfor the "layer" window. In this case, we don't need to worry about that because we have only one layer. Otherwise, we have to pick up the correct layer from the drop-down list. In the method box, keep the default one "create a new selection". Now you can type your query manually into the box in the bottom left corner of the window, or you can use the tools at the top of the window. Let's find the block groups with median household income in excess of $50,000 per year. In the "Fields" list, double-click on "Med_hh_inc". Then single-click on the ">" button. Next, you can type in 50000 or scroll down "Values" list until you can double-click on 50000. The query entry window should now show "Med_hh_inc"> 50000 Fig. 1. Query Window The window finally looks like the Fig. 1. Click "Apply" button to run the query and 'select' these block groups. Notice that all block groups where the median householdincome is greater than $50,000 are now highlighted in the data display area. If you open the attribute table, you can find out the associated records are also highlighted. You may need to scroll up or down in the attributes window to see the highlighted records. If you click the Selected button, which is at the bottom of the attributes window, only the selected records will be visible. Next to the Selected button, you can find "9 out of 94 are selected". B. Statistics and Selected Sets Now close the query window. Make sure the