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 o o o Log onto Athena PC Attach drive M by using the DOS command attach Dm 11 520 Launch a web browser and open the web page for the current lab 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 Cambbgrp for 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 household income 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 selected records are still highlighted Select Statistics from the Selection menu You should see a selection statistics window Choose Med hh inc in the field drop down list Fig 2 You should