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 4: Database Aggregation and Chart Creation in ArcMap In this lab exercise, you will explore using ArcMap's data manipulation and charting tools. The motivating example is one of the complications that we listed when discussing GIS data models. When we obtain maps and data from different sources, there may not be a one-to-one match between the spatial objects in the map and the rows in the data table. For example. there are 351 towns in Massachusetts, but the MassGIS map of town boundaries has 631 rows! Why so many? Because some towns are split into two or more parts by rivers. Other towns include islands with boundaries that don't connect with the other parts of the town. But our data source for town-by-town population probably has only the total population for each town. So, the usual way to create a population density map won't work well. If we choose a thematic map of population divided by area, the Boston harbor islands will show up as the densest part of the state - with an average of 170+ people per square meter of land area! Try it! In order to develop an appropriate thematic map of population density, we must aggregate the area across all parts of each town before we compute density as town-population divided by town-area. We will use the 'Summarize Table' function to create a new table from the Attributes-of-Town table. We will also use the Chart functions to create business graphics of Town density. Producing the charts will involve another complication because of the multiple rows per town in the Attributes-of-Town table. Handling this density-map problem will give us a better understanding of ArcMap and, moreover, a better understanding of the one-to-many problems that often complicate good data analysis when we mix and match data from different sources. As we did in previous labs, attach Drive M: before you start the exercise. I. Fixing the One City-Multiple Polygon Problem First let's generate a quick-n-dirty (but wrong) population density map. Add the MassGIS shapefile of Massachusetts city and town boundaries from M:\data\matown00.shp andproduce a thematic map of the 1990 population (pop90) divided by 'area'. When zoomed in on Boston, the thematic map will look something like this: Fig. 1. Population density map without considering one-to-many relationshipThe results look strange and we notice that the density of some small areas appears to be extremely high. On looking more closely, we find that small islands in Boston Harbor have a small area but are associated with all of Boston's population, thereby creating the impression that these islands were extraordinarily densely populated. In fact, many of these islands have no population at all. The basic problem is that the population data counted people in each entire city (or town) whereas the map sometimes had multiple polygons for a single city. In the case of Boston, for example, East Boston and Charlestown are separated from the rest of Boston by Boston Harbor and the Charles River and each of the harbor islands is represented by a its own polygon - and a separate row in the Attributes of matown00 table. Hence, we have a 'one-to-many' database problem whereby each city or town may be associated with more than one spatial feature. We can find a way to resolve this problem using ArcMap's 'Summarize Table' function. In this part of the lab, you will solve this problem, with some modest variations in the basic procedure. In particular, we will strive to obtain a better estimate of population density by excluding small islands of 100,000 square meters (10 hectares) or less. Open ArcMap and add a major road layer from M:\data\majmhda1.shp. Modify the layer properties of the majmhda1.shp layer so that it only shows the limited access highways ("Class" = 1) and rename the layer to Limited Access Highways. Make a copy of the matown00 layer by copying and pasting into the same dataframe. Now you have two matown00 layers in your dataframe. Rename one of the layers to Towns and modify its layer properties so that the layer's definition is defined by this query:. ( "Area"> 100000 ) Now modify the other copy of the matown00 layer so that it is defined by this query: ( "Area"<= 100000 ) Rename this theme to Small Islands. Zoom into Boston and the harbor and turn the Small Islands layer on and turn the Towns layer off. Now you can see the smaller harbor islands that you excluded from Towns. Next we want to create a new table that aggregates the area by town for the Towns layer. The new table will contain the name of the town, the total area for that town (exclusive of the islands), and the town's population for 1980 and 1990. Open the attribute table for the Towns layer first. Since we want to do the aggregation by town, right click on the column name "Town_Name". On the pop-up menu, select Summarize. The "Summarize" window appears.On the "summarize" window, select the Town field in the table as the field to summarize. (The "TOWN-NAME" field shown in the attribute table is an alias of the field "TOWN"). Make sure that none of the towns are selected; if any are, then your summary table will contain records for only those selected towns. Then, change the name of the output file from the default to I:\[your working directory]\town_density.dbf. For the statistics to summarize, select the Sum for AREA field, Minimum for POP1980, and POP1990 fields, (Why don't we use sum for POP1980, and POP1990? Can we use Maximum or average for POP1980, and POP1990? ) ArcMap will ask you whether you want to add the new table to the map document. Select Yes. town_density.dbf will be added to your document. We now have the raw data we want -- the town areas and the populations -- but we have not yet computed the densities. Let's do that now. Using the techniques you learned in Lab 3 for editing data tables, add these fields to your table: AreaMi2 Town's area in square miles PopDen80 Population density per mile in 1980 PopDen90 Population density per mile in 1990To convert square meters (the units of area used in Towns layer) to square miles, divide by 2589988. When you're finished with the calculations, join the town_density.dbf table to the Towns layer, as described in the section entitled "Merging Data Tables" in Lab 3. Now create a thematic map of