{"id":496,"date":"2025-01-31T18:11:02","date_gmt":"2025-01-31T18:11:02","guid":{"rendered":"https:\/\/commons.mtholyoke.edu\/emarcano\/?page_id=496"},"modified":"2025-03-17T14:16:23","modified_gmt":"2025-03-17T14:16:23","slug":"geog210-ex5","status":"publish","type":"page","link":"https:\/\/commons.mtholyoke.edu\/emarcano\/gismhc\/geog210\/geog210-ex5\/","title":{"rendered":"Geog 210 \u2013 Exercise 5 &#8211; Census &amp; joins"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\" id=\"top\"><strong>Census Data and Join Operations<\/strong><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Contents<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong><a href=\"#part1\">Part 1: Tutorial &#8211; Learning Data Manipulation and Join Operations<\/a><\/strong>\n<ul class=\"wp-block-list\">\n<li><strong>Objective:&nbsp; <\/strong>Use census data and map population at different levels, perform table operations and spatial queries, and execute attribute and spatial joins.&nbsp;<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong><a href=\"#part2\">Part 2: Case Study &#8211; Addressing maintenance problems in the city of Chicago<\/a><\/strong>\n<ul class=\"wp-block-list\">\n<li><strong>Objective: <\/strong>Practice joining and manipulating tables<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/commons.mtholyoke.edu\/emarcano\/geog210\/\">Return to Geog 210 page<\/a><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"part1\"><strong>Part 1: Data Manipulation and Join Operations<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">In this exercise, you will be working with <a href=\"https:\/\/www.census.gov\/\">US census data<\/a>.&nbsp; To understand this data, read the short document <a href=\"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-content\/uploads\/sites\/965\/2025\/01\/US-Census-and-GIS.pdf\" target=\"_blank\" rel=\"noreferrer noopener\">\u201c<strong>US Census and GIS<\/strong>\u201d<\/a> AND the section \u201c<strong>The main geographical hierarchy<\/strong>\u201d in pages 21-24 of the book <a href=\"http:\/\/downloads2.esri.com\/ESRIpress\/images\/84\/Ch01_Census.pdf\">\u201cUnlocking the Census with GIS &#8211; Chapter 1\u201d<\/a>  (the rest of the document is recommended but optional).  Copies of both documents are also available in <a href=\"https:\/\/moodle.mtholyoke.edu\" target=\"_blank\" rel=\"noreferrer noopener\">Moodle<\/a>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The data provided in this exercise IS NOT in geodatabase format but in a common open format called \u201cshapefile\u201d which consists of a group of files (from 3 to &gt;10) with the same name (e.g. <em>US_cities<\/em>) but different extensions (shp, dbj, sbn, prj, shx, etc.).&nbsp; A shapefile in ArcGIS Pro looks like only one file (not multiple files).&nbsp; With Windows Explorer, you can see all the files associated with each respective shapefile.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><em>CENSUS2010_TOWNS_POLY.shp<\/em>, shapefile of 2010 census <strong>town<\/strong> data for Massachusetts.&nbsp; Obtained from the Office of Geographic Information of Mass., MassGIS&nbsp; (link in Moodle \u2013 Exercise 5 resources)<\/li>\n\n\n\n<li><em>CENSUS2010_BG_POLY.shp<\/em>, shapefile of the 2010 census bureau <strong>Block Group<\/strong> data for Massachusetts.<\/li>\n\n\n\n<li><em>CENSUS2010_BG_SF1_POP_RACE.dbf, <\/em>databasetable (dBase format) of the 2010 census containing race information of the Block Groups in Massachusetts.<\/li>\n\n\n\n<li><em>Mass_outline.shp,<\/em> this is the outline of the state of Massachusetts, used for display purposes.<\/li>\n\n\n\n<li><em>patients_zipcode.shp,<\/em> patients of hospital in Worcester, geocoded according to zipcodes.<\/li>\n\n\n\n<li><em>US_cities<\/em>, a point layer of cities and towns, in NAD83 geographic coordinates.<\/li>\n<\/ul>\n\n\n\n<p class=\"has-light-blue-background-color has-background wp-block-paragraph\">Unless noted, data are in Mass. State Plane Coordinate System &#8211; NAD83 &#8211; meters, coordinates. This means that the data is not in Geographic Coordinate System (lat-long) but in a projected (lineal) system. (This doesn\u2019t mean much to you now, but it\u2019ll come later in the semester).&nbsp;<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Task 1: Census data representation<\/strong><\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Copy the data folder, create a new map and add the census data <em>CENSUS2010_BG_POLY.shp <\/em>to your new empty map frame.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Each polygon is <strong>a block group<\/strong>, a unit of aggregation in collecting census data.<\/p>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li>Open the attribute table and inspect the values for the column labeled POP100_Re. This is the total population (100%) for each block group (RE stands for redistricting) in Massachusetts.<\/li>\n\n\n\n<li>Next, right click on the column name (POP100_RE), and select <strong><strong>Explore<\/strong> <strong>Statistics (or Visualize Statistics).<\/strong>&nbsp;<\/strong><\/li>\n<\/ol>\n\n\n\n<p class=\"has-yellow-background-color has-background wp-block-paragraph\"><em>Question 1. What is the total population (Sum)  in Massachusetts, reported in this dataset, in 2010? <\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Along with the Statistics pane, a new pane should have appeared in the attribute table window showing the Frequency Distribution chart. Looking at this chart, note that the data has many small values (tall bar = many entities), and a few very large values (the histogram has a tail to the right).&nbsp; This \u201clong-tailed\u201d distribution is common in some types of data, and often displays better with a non-uniform set of symbol ranges. We will demonstrate here by example.<\/p>\n\n\n\n<ol start=\"4\" class=\"wp-block-list\">\n<li>Close the table and chart, and open the Symbology tool (click in the theme in the CP and find Symbology under \u201cFeature Layer\u201d tab in the ribbon interface; or find this tool by right clicking on the <em>CENSUS2010_BG_POLY<\/em> layer in the CP and selecting the tool )<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Since POP100_RE is a numeric field, what kind of palette (primary symbology or color scheme) would you use to represent it?&nbsp; (Unique value?&nbsp; Graduated colors\/symbols?)<\/p>\n\n\n\n<ol start=\"5\" class=\"wp-block-list\">\n<li>Display the <strong>POP100_RE<\/strong> field with a graduated color palette. Keep the default number of classes (5), natural breaks, and select a single color gradient color scheme. Study the result.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Notice how little information this map shows (it\u2019s rather messy). Total population values are commonly not too informative, as usually large units (block groups, towns, states, and countries) tend to have larger total population than smaller ones.&nbsp; Population density is usually a more informative parameter. To create a map of population density you need to divide the total pop of each unit by its corresponding area. Our shapefile contains a column with the number of acres for each block group, so we\u2019ll use this.<\/p>\n\n\n\n<p class=\"has-light-blue-background-color has-background wp-block-paragraph\">NOTE: Generally, it\u2019s a bad idea to use an \u201carea\u201d column that you have not calculated yourself, especially with shapefiles.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">There are two ways to do this. First, creating a new column in the attribute table and calculating new values for this column, and the other is using \u201cNormalization\u201d in the Symbology dialog for quantitative data (normalization divides the value field by the normalization field, in this case total pop divided by area is equivalent to pop density or people per acre).&nbsp; You know how to do the first method from the previous exercise.<\/p>\n\n\n\n<ol start=\"6\" class=\"wp-block-list\">\n<li>To use the first method open the attribute table of the census block groups:<\/li>\n<\/ol>\n\n\n\n<ol style=\"list-style-type:lower-alpha\" class=\"wp-block-list\">\n<li>Add a field called \u201cPopdensity\u201d<\/li>\n\n\n\n<li>For \u201cData Type\u201d choose <strong>Double <\/strong>(this is a Real Number)<\/li>\n\n\n\n<li>Close the Fields pane in the attribute table and select \u201cyes\u201d in the pop-up window that asks to save all changes<\/li>\n\n\n\n<li>Use the Calculate Field tool to calculate values for the Popdensity column<\/li>\n\n\n\n<li>First, select Popdensity in the Field Name bar. Then, in the list of Fields (columns of the table), double click on POP100_RE, \n<ul class=\"wp-block-list\">\n<li>then click on the division symbol \/ (you can enter it using the keyboard as well),<\/li>\n\n\n\n<li> then double click on the field AREA_ACRES.<\/li>\n\n\n\n<li> Click OK. Make sure that there is nothing selected before doing the calculation. Your expression should look like:<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<p class=\"has-text-align-center wp-block-paragraph\">!POP100_RE! \/ !AREA_ACRES!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Now you have a calculated column that shows population density in number of <strong>people per acre <\/strong>(remember this unit, it is important for the legend).<\/p>\n\n\n\n<ol start=\"7\" class=\"wp-block-list\">\n<li>Now use this new field to plot the block group shapefile using this column instead of POP100_RE. Keep the same color scheme as before.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Zoom in around Boston and notice the rather useless selection of classes: it doesn\u2019t show much contrast; also the grey outlines of the polygons obscure the colors for the small units.&nbsp;<\/p>\n\n\n\n<ol start=\"8\" class=\"wp-block-list\">\n<li>In the Symbology pane, change Methods to <strong>Geometrical Interval<\/strong> and change the value in <strong>the Classes bar<\/strong> to 10.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Notice how this improves color contrast somewhat, but polygon outlines still obscure the small polygons.&nbsp;<\/p>\n\n\n\n<ol start=\"9\" class=\"wp-block-list\">\n<li>To remove the outlines, return to the Symbology pane, click on <strong>Color scheme options <\/strong>tool (looks like a gear) located next to the Color scheme bar and check \u201cApply to fill and outline\u201d.<\/li>\n<\/ol>\n\n\n\n<p class=\"has-light-blue-background-color has-background wp-block-paragraph\">NOTE: When representing layers don\u2019t necessarily accept the default representation by the software. Make sure you represent the map the way you want it.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">While we\u2019re working with the symbology, let\u2019s fix the number of decimal places that ArcGIS Pro uses by default.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"alignright size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"37\" height=\"31\" src=\"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-content\/uploads\/sites\/965\/2025\/01\/fig5.png\" alt=\"advanced symbology\" class=\"wp-image-521\" style=\"width:55px;height:auto\" \/><\/figure>\n<\/div>\n\n\n<ol start=\"10\" class=\"wp-block-list\">\n<li>In the Symbology pane, go to the \u201cAdvanced symbology options\u201d tab &nbsp;and open Format Labels. Next to Decimal places, change value to 2. From now on, always use a sensible and logical number of decimal places.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Now the map should be displayed with no lines separating the polygons and with more reasonable legend labels. This symbology provides a clearer view of the variation in the data, due to removal of the borderlines, and using a geometric interval in the classification method of the data representation.<\/p>\n\n\n\n<ol start=\"11\" class=\"wp-block-list\">\n<li>Add the shapefile <em>Mass_outline<\/em> on top of the table of content and adjust its color to a medium gray to show a light outline of the state (for better looks only).<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Task 2: Data extraction and map preparation<\/strong><\/h3>\n\n\n\n<ol start=\"12\" class=\"wp-block-list\">\n<li>Add the <em>US_cities.shp<\/em> point theme to the current data frame.<\/li>\n<\/ol>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"alignright size-full is-resized\"><a href=\"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-content\/uploads\/sites\/965\/2025\/01\/fig12.png\"><img loading=\"lazy\" decoding=\"async\" width=\"36\" height=\"67\" src=\"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-content\/uploads\/sites\/965\/2025\/01\/fig12.png\" alt=\"Full extent and zoom to previous extent\" class=\"wp-image-503\" style=\"width:64px;height:auto\" \/><\/a><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\">This shapefile contains data on cities for the entire U.S. and territories.&nbsp; Use the <strong>Zoom to Full Extent<\/strong> button &nbsp;to view the entire dataset, and then <strong>Zoom to Previous Extent<\/strong> button to return to Massachusetts.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It is burdensome to work with a large data set when we only want a small portion or subset.&nbsp; Now we will select just the cities data that correspond to our census data set.&nbsp;<\/p>\n\n\n\n<ol start=\"13\" class=\"wp-block-list\">\n<li>In the Select by Location dialog box, make <em>US_cities<\/em> the Input Feature and the block groups the Selecting Feature. Set the Relationship to intersect and click OK.<\/li>\n\n\n\n<li>Create a new theme from this selection (right-clicking Data\\Export Features). Save it as<em> Mass_cities.<\/em><\/li>\n<\/ol>\n\n\n\n<p class=\"has-yellow-background-color has-background wp-block-paragraph\"><em>Question 2 &#8211; How many census block groups are there in Massachusetts? <\/em><\/p>\n\n\n\n<p class=\"has-yellow-background-color has-background wp-block-paragraph\"><em>Question 3 &#8211; What is the average pop. density per census block groups in Massachusetts?<\/em><\/p>\n\n\n\n<p class=\"has-yellow-background-color has-background wp-block-paragraph\"><em>Question 4 &#8211; How many cities are within the state?<\/em><\/p>\n\n\n\n<ol start=\"15\" class=\"wp-block-list\">\n<li>Make a map suitable for a report showing the population density of the block groups of Massachusetts and the cities of the state.&nbsp;\n<ol style=\"list-style-type:lower-alpha\" class=\"wp-block-list\">\n<li>Make sure to show the whole state and to fix the labels, decimal places, and units in the legend.<\/li>\n\n\n\n<li>Insert all the required components of the map that you know (Title, author, scale bar with reasonable units, legend, north arrow)<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Before you finish, let\u2019s learn how to add another component, a <strong>Grid<\/strong>.<\/p>\n\n\n\n<ol start=\"16\" class=\"wp-block-list\">\n<li>While looking at your layout, in the Insert tab of the ribbon, look in the Map Frames group for the \u201cGrid\u201d button.<\/li>\n<\/ol>\n\n\n\n<ol style=\"list-style-type:lower-alpha\" class=\"wp-block-list\">\n<li>Select a Measured Grid. Notice that the Grid is added in the CP (Don\u2019t use a Graticule; that\u2019s for Lat Long, and we want to use the local Mass State Plane Coordinate System<\/li>\n\n\n\n<li>Right click on the Grid in the CP and get the Properties and you\u2019ll see a \u201cFormat Map Grid\u201d pane on the right<\/li>\n\n\n\n<li>Observe that the coordinate system is \u201cNAD 1983 StatePlane Massachusetts Mainland\u201d (you\u2019ll learn about this later)<\/li>\n\n\n\n<li>Uncheck the tick box under \u201cInterval \u2013 Automatically adjust\u201d<\/li>\n<\/ol>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"alignright size-full is-resized\"><a href=\"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-content\/uploads\/sites\/965\/2025\/01\/fig3.png\"><img loading=\"lazy\" decoding=\"async\" width=\"109\" height=\"57\" src=\"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-content\/uploads\/sites\/965\/2025\/01\/fig3.png\" alt=\"\" class=\"wp-image-504\" style=\"width:129px;height:auto\" \/><\/a><\/figure>\n<\/div>\n\n\n<ol start=\"5\" style=\"list-style-type:lower-alpha\" class=\"wp-block-list\">\n<li>Go to the Component page of the pane by clicking the second icon on top of the pane<\/li>\n\n\n\n<li>Click Gridlines in the Components field and enter 50,000 (no commas) for intervals in X and Y<\/li>\n\n\n\n<li>Now click Labels and, again, enter 50000 in both X and Y<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Now you have a correct map with all the required components.&nbsp;<\/p>\n\n\n\n<p class=\"has-light-blue-background-color has-background wp-block-paragraph\">NOTE: From now on, always include a grid (or Graticule if Lat-Long is more appropriate) with your maps, along with the other necessary components.<\/p>\n\n\n\n<p class=\"has-yellow-background-color has-background wp-block-paragraph\"><em>Question 5 \u2013 Upload ex5_map1_yourname.pdf (in PDF format) to Moodle.<\/em><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Task 3: Joining Tables &#8211; Attribute Join<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Sometimes the information we want to map is not available in the attribute table of the spatial data we have.&nbsp; This information can be found in external tables (like census tables) that don\u2019t have the spatial component. To visualize that particular data we have to \u201cjoin\u201d the information from the table into the attribute table of the spatial data.&nbsp; <strong>Tabular joins<\/strong> use a common unique identifier to attach an attribute table to a spatial layer.&nbsp;<\/p>\n\n\n\n<ol start=\"17\" class=\"wp-block-list\">\n<li>Return to the Map windows by closing the Layout tab.<\/li>\n\n\n\n<li>If not loaded in your view, load the shapefile containing the Census Block Groups (BG) plus the corresponding table containing Population and Race (<em>CEN2010_BG_SF1_POP_RACE.dbf<\/em>).<\/li>\n\n\n\n<li>Open this latter table and study its fields. It should contain total population and races.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Notice that this <em>dbf <\/em>file is just a table, it doesn\u2019t have spatial information.&nbsp; .<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Identifying a Key<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">To join tables, you must identify a field that is common to both tables (attribute and data table). This field is known as a<strong> key<\/strong> because it <strong>uniquely<\/strong> identifies each record in a table. The values must be formatted in an identical way (<em>i.e. <\/em>numerical, string, date).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The attribute table of the block group layer (shapefile) contains multiple fields that uniquely identify each record. This table has attributes of census block groups but no actual population data (other than POP100_RE, which is a total value). Therefore, we cannot map the different races from a census dataset until we bring the information from the other table.&nbsp; If you read the information from the <a href=\"https:\/\/www.mass.gov\/info-details\/massgis-data-2010-us-census\" target=\"_blank\" rel=\"noreferrer noopener\">MassGIS website<\/a> about this dataset you would find that there is one field that uniquely identifies records and, thus, it can be used to match fields in the census Block Group shapefile: <strong>LOGSF1<\/strong>.&nbsp; The corresponding field in the population table is called <strong>LOGRECNO<\/strong>.&nbsp; Fields do not need to have same names in table and layer in order to join them; they <strong>must<\/strong> be formatted in the same way.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Keep in mind that ArcGIS Pro does not check to make sure that the key fields or their formats match, so you should double check them (by opening both tables and getting the properties of the corresponding columns by hovering your cursor over the headings; they should be the same) before performing the join.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>What type of field is LOGRECNO in the census table?&nbsp;<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>What type of field is LOGSF1 in the block group layer table?&nbsp; <\/em><\/p>\n\n\n\n<ol start=\"20\" class=\"wp-block-list\">\n<li>Right click on the Block Groups shapefile in CP and select <strong>Joins and Relates\\Add Join<\/strong>.<\/li>\n<\/ol>\n\n\n\n<ol style=\"list-style-type:lower-alpha\" class=\"wp-block-list\">\n<li>In the \u201cAdd Join\u201d window: the Input Table should be CENSUS2010_BG_POLY<\/li>\n\n\n\n<li>Under Input Field, use the drop down box to select the key field in your shapefile, LOGSF1 (careful, there is another field with similar name).<\/li>\n<\/ol>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"alignright size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"148\" height=\"268\" src=\"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-content\/uploads\/sites\/965\/2025\/02\/ex5-fig7.png\" alt=\"warning message\" class=\"wp-image-932\" style=\"width:152px;height:auto\" \/><\/figure>\n<\/div>\n\n\n<p class=\"has-light-blue-background-color has-background wp-block-paragraph\">NOTE: You might get a little yellow warning triangle on the Input Field. If you click it, it tells you that &#8220;the join field LOGSF1 is no indexed&#8230;&#8221; You don&#8217;t need to worry about this. It&#8217;s an internal procedure for the database part of the software.<\/p>\n\n\n\n<ol style=\"list-style-type:lower-alpha\" class=\"wp-block-list\">\n<li>Under Join Table, use the drop down box to select CEN2010_BG_SF1_POP_RACE (the table doesn\u2019t have to be added to the CP)<\/li>\n\n\n\n<li>Under Join Field (of the Join Table), use the drop down box to select the key field in your table that you wish to join, LOGRECNO<\/li>\n\n\n\n<li>Leave the \u201cKeep all input records\u201d box checked<\/li>\n\n\n\n<li>Click on Validate Join. Read the validation results window, it should indicate how many records would be joined, this is important! It can\u2019t be \u201c0\u201d! Then close it, and click OK to proceed with the Join.<\/li>\n\n\n\n<li>Open the attribute table of the shapefile and notice that the columns of the population table have been added at the end. Make sure there are numbers in these extra columns and not all cells with value of zero or . It you find empty fields, make sure no objects are selected before the join, and then repeat the join<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">When you perform an attribute join the data is dynamically joined together. Nothing is written to disk and the join is not permanent. To make the join permanent (JUST FOR INFO, NO NEED TO DO IT), export the theme (right-click\\Data\\Export) in the Contents Pane. It can be exported as a new shapefile or into our default GeoDB, and it will include attributes from both (or all) joined tables.&nbsp;<\/p>\n\n\n\n<p class=\"has-light-blue-background-color has-background wp-block-paragraph\">NOTE: Several tables or layers can be joined to a single table or layer.<\/p>\n\n\n\n<p class=\"has-light-blue-background-color has-background wp-block-paragraph\">DON\u2019T DO THIS, but explore: Joins only exist within the confines of project file (Map).&nbsp; They can be \u201cremoved\u201d by right-clicking the layer name in the CP and selecting \u201c<strong>Joins and Relates<\/strong>\u201d and clicking \u201c<strong>Remove Join<\/strong>\u201d. You have the option of removing a specific join or all joins.<\/p>\n\n\n\n<ol start=\"21\" class=\"wp-block-list\">\n<li>Prepare a map layout showing the distribution of <strong>Hispanics<\/strong> (HISP) in the state. Choose a single color ramp and remove the outlines from the polygons, and add the outline of the state.&nbsp; DO NOT include <em>Mass_cities<\/em>. Use a <strong>Geometric Interval classification and 10 classes <\/strong>(like map 1); these are No. of people so make sure labels are integer numbers (no decimal places). The map should be a good composition with all the necessary components.&nbsp; Save as ex5_map2_yourname.pdf<\/li>\n<\/ol>\n\n\n\n<p class=\"has-light-blue-background-color has-background wp-block-paragraph\">Note that geometric interval will show \u201cdecimals\u201d even though it\u2019s a count variable; We should probably use a different classification, but here we\u2019re just trying to be consistent with the previous map.<\/p>\n\n\n\n<p class=\"has-yellow-background-color has-background wp-block-paragraph\"><em>Question 6 &#8211; Upload ex5_map2_yourname.pdf <\/em>(<em>in PDF format) to Moodle.&nbsp;<\/em><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Task 4: Table joining with aggregation<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The map you previously made suffers from too much \u201cgranularity\u201d.&nbsp; Sometimes we want to show the data (<em>i.e.<\/em> Number of Hispanics) in a more general way.&nbsp; Let\u2019s map the population data by towns (instead of block groups).<\/p>\n\n\n\n<ol start=\"22\" class=\"wp-block-list\">\n<li>Add the <em>CENSUS2010_TOWNS_POLY <\/em>theme to your map and familiarize yourself with the content of its attribute table.<\/li>\n\n\n\n<li>Open the table of the census block groups shapefile and check the existing columns.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">The <em>Towns <\/em>shapefile contains a column called \u201cTOWN\u201d which is unique (also TOWN_ID, and TOWN2). The block group shapefile also contains one column called \u201cTOWN\u201d which is unique to each town, but since there are multiple block groups in each town, they have to be <strong>summarized<\/strong> before they get joined to the town theme. We need the total number of people or races, etc. in each town; therefore, we need to add up all the block groups in the town, first. If we don\u2019t summarize the block groups before joining this would be a case of a \u201cmany to one\u201d join, where there are multiple options to join to the one town\/city and ArcGIS Pro would just pick the first one it finds.&nbsp; Since we want the population race data for each town, we need a join, with data summarized by town.<\/p>\n\n\n\n<ol start=\"24\" class=\"wp-block-list\">\n<li>In the table of the block group shapefile with the joined data (from previous task), right click on TOWN and choose Summarize.&nbsp; Summarize the <strong>SUM<\/strong> for the variables:<\/li>\n<\/ol>\n\n\n\n<ol style=\"list-style-type:lower-alpha\" class=\"wp-block-list\">\n<li>POP_2010 (notice that for a \u201cjoined\u201d column its name is preceded by the name of the table it comes from.&nbsp; So this is called CEN2010_BG_SF1_POP_RACE. POP_2010)<\/li>\n\n\n\n<li>POP_WHITE, POP_BLACK, HISP (make sure Not to pick _NOT_HISP), and any other race that might interest you<\/li>\n\n\n\n<li>The table will be save in your default GeoDB. Call it something like \u201c<em>Race_Town_Summary<\/em>\u201d. It will be added to the map (if not, you can add it yourself).<\/li>\n\n\n\n<li>Open the table (it should be at the bottom of the CP) and familiarize yourself with its contents. Notice that now there is only one line per town with the corresponding totals for POP_2010 and the totals of the different races.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Now answer the following questions:<\/p>\n\n\n\n<p class=\"has-light-blue-background-color has-background wp-block-paragraph\"><strong>Hint<\/strong>: Calculate the total population of Massachusetts using this table; it should be equal to the answer of question 1. (Hint of a hint: Statistics)<\/p>\n\n\n\n<p class=\"has-yellow-background-color has-background wp-block-paragraph\"><em>Question 7- How many towns contain no Hispanics? <\/em><\/p>\n\n\n\n<p class=\"has-yellow-background-color has-background wp-block-paragraph\"><em>Question 8- How many towns have a white population larger than the sum of the Black and Hispanic populations? (Hint: you might need to add\/calculate fields and select by attributes) (Hint 2: you can select by attribute using the table menus) <\/em><\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"362\" height=\"40\" src=\"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-content\/uploads\/sites\/965\/2025\/01\/fig4.png\" alt=\"\" class=\"wp-image-505\" style=\"width:426px;height:auto\" srcset=\"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-content\/uploads\/sites\/965\/2025\/01\/fig4.png 362w, https:\/\/commons.mtholyoke.edu\/emarcano\/wp-content\/uploads\/sites\/965\/2025\/01\/fig4-300x33.png 300w\" sizes=\"auto, (max-width: 362px) 100vw, 362px\" \/><\/figure>\n<\/div>\n\n\n<p class=\"has-yellow-background-color has-background wp-block-paragraph\"><em>Question 9- How many towns have a total population over 10,000 that is, at least, one quarter Hispanic (Hint: use &#8220;Double&#8221; to create a column with decimals)? <\/em><\/p>\n\n\n\n<p class=\"has-yellow-background-color has-background wp-block-paragraph\"><em>Question 10- Of the above towns (Q. 9), how many are also at least one quarter Black?&nbsp;<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Now we\u2019re going to join the summary data to the town polygons. Notice that the TOWNS shapefile has two fields with town names, written in different ways (one <em>UPPERCASE<\/em>, the other with <em>Capitals<\/em>).&nbsp; Fields to be joined must be written\/spelled the same way.&nbsp; Which column in the Town shapefile would you use?&nbsp; TOWN or TOWN2?&nbsp;&nbsp; What would you do if one of the tables doesn\u2019t have the names spelled the same way?<\/p>\n\n\n\n<ol start=\"25\" class=\"wp-block-list\">\n<li>Clear all selections before proceeding.<\/li>\n\n\n\n<li>Join the summary table you just created (<em>Race_Town_Summary<\/em>) to the towns theme, NOT the block group (BG) theme.<\/li>\n\n\n\n<li>Prepare a map layout showing the distribution of Hispanics (or Blacks, etc.) in the state (no cities!). Choose the same color ramp, classification, and number of classes used in Question 6 (Map2). DO NOT remove the outlines of the towns.&nbsp; The map should be a good composition with all the necessary components (don\u2019t forget the grid). Save as ex5_map3_yourname.pdf.<\/li>\n<\/ol>\n\n\n\n<p class=\"has-light-blue-background-color has-background wp-block-paragraph\">NOTE: some towns might show up empty. Don\u2019t worry about this but, can you guess why?<\/p>\n\n\n\n<p class=\"has-yellow-background-color has-background wp-block-paragraph\"><em>Question 11 &#8211; Upload ex5_map3_yourname.pdf (in PDF format) to Moodle.<\/em><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Task 5: Spatial joins<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Spatial joins use common geography (location) to append fields from one layer to another layer. This allows you to assign the characteristics of an area\u2014such as a census tract or school district\u2014to individual houses, individuals, or events as well as to aggregate points by areas.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Aggregating Points to Polygons<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Using a spatial join, you can determine how many points fall in each polygon feature. For example, you might need to determine how many crimes occurred within each police district. You must have a point layer and a polygon layer in ArcGIS in order to do this.<\/p>\n\n\n\n<ol start=\"28\" class=\"wp-block-list\">\n<li>Load the <em>patients_zipcode.shp<\/em> into your view.&nbsp; This layer contains the zip codes of patients of a Worcester hospital that participated in one particular study.<\/li>\n\n\n\n<li>Open the attribute table and notice that the only location information is the zip code. The location of the points have been \u201cgeocoded\u201d using the zip codes and placed in a geographic context.<\/li>\n<\/ol>\n\n\n\n<p class=\"has-light-blue-background-color has-background wp-block-paragraph\">IMPORTANT: Multiple patients share the same zip codes, therefore, their points will fall one on top of another. E.g. in Worcester you can only see a few points, but if you examine the table you\u2019ll see there are many more patients than points visible.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We want to create a map of patient distribution in the towns of Massachusetts.&nbsp; For this we have to \u201cjoin\u201d the data based on their location rather than on some common field, <em>i.e.<\/em> we want to produce a map that shows the number of patients per town (notice that some patients are outside Massachusetts. Don\u2019t worry about those, they won\u2019t be counted).<\/p>\n\n\n\n<p class=\"has-light-blue-background-color has-background wp-block-paragraph\">Note: Always make sure there are no objects selected before doing any operation, unless you\u2019re working with the selected subset.<\/p>\n\n\n\n<ol start=\"30\" class=\"wp-block-list\">\n<li>In the Contents Pane right-click the census TOWNS layer (NOT the block groups!), select <strong>\u201cJoins and Relates\u201d <\/strong>and click<strong> \u201cRemove all joins\u201d <\/strong>(we don\u2019t need the race data anymore; if in doubt, then create a new project and add the two previous layer and continue).<\/li>\n<\/ol>\n\n\n\n<ol style=\"list-style-type:lower-alpha\" class=\"wp-block-list\">\n<li>Go, again, to <strong>\u201cJoins and Relates\u201d <\/strong>and click<strong> \u201cAdd Spatial Join\u201d<\/strong><\/li>\n\n\n\n<li>Under Target Features choose <strong>CENSUS2010_TOWNS_POLY<\/strong><\/li>\n\n\n\n<li>Under Join Features choose <strong>patients_zipcode<\/strong><\/li>\n\n\n\n<li>Under Match Option study the different options and select \u201c<strong>Contains<\/strong>\u201d<\/li>\n\n\n\n<li>Click OK<\/li>\n<\/ol>\n\n\n\n<ol start=\"31\" class=\"wp-block-list\">\n<li>Open the attribute table of the towns theme and study it. Notice that there is a new column called \u201cJoin_Count\u201d; this is the number of patients per town.<\/li>\n<\/ol>\n\n\n\n<p class=\"has-light-blue-background-color has-background wp-block-paragraph\"><strong>NOTE:<\/strong> This join, as in the case of attribute join, is also temporary and can be removed.&nbsp; There is another way of doing Spatial Join, under the Geoprocessing toolbox that create a new output theme thus making the join permanent.&nbsp; We will not use that method here.<\/p>\n\n\n\n<p class=\"has-light-blue-background-color has-background wp-block-paragraph\"><strong>NOTE 2: <\/strong>if you ever want to make the join permanent, all you have to do is to export the joined data into a new theme.<\/p>\n\n\n\n<p class=\"has-yellow-background-color has-background wp-block-paragraph\"><em>Question 12 &#8211; How many towns\/cities contain one or more patients in Massachusetts? <\/em><\/p>\n\n\n\n<p class=\"has-yellow-background-color has-background wp-block-paragraph\"><em>Question 13 \u2013 Which town\/city has the most patients? <\/em><\/p>\n\n\n\n<p class=\"has-yellow-background-color has-background wp-block-paragraph\"><em>Question 14 &#8211; How many towns\/cities have a patient-to-population (2010) ratio greater than the town chosen in question 13? (Hint: Add a \u201cDouble\u201d field to the table and expand the column to see all decimal places) <\/em><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Assigning Area Characteristics to Points<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Using a spatial join, you can identify in which area a point falls. For example, you might need to determine in what town each of your participants live. You must have a point layer and a polygon layer in ArcGIS Pro in order to do this.&nbsp; So let\u2019s add the town information <strong>to<\/strong> the patient data:<\/p>\n\n\n\n<ol start=\"32\" class=\"wp-block-list\">\n<li>From the Contents Pane:<\/li>\n<\/ol>\n\n\n\n<ol style=\"list-style-type:lower-alpha\" class=\"wp-block-list\">\n<li>Right-click the <em>Patients_zipcode<\/em> theme (NOT the towns!), select <strong>\u201cJoins and Relates\u201d <\/strong>and click<strong> \u201cAdd Spatial Join\u201d<\/strong><\/li>\n\n\n\n<li>Under Target Features select <strong>patients_zipcode<\/strong><\/li>\n\n\n\n<li>Under Join Features select <strong>CENSUS2010_TOWNS_POLY<\/strong><\/li>\n\n\n\n<li>Under Match Option select \u201cWithin\u201d<\/li>\n\n\n\n<li>Click OK, open the attribute layer of the patient\u2019s layer, and notice that the town information has been joined to each patient record.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Notice that only the points that fell inside a polygon will have information. Study the attribute table and notice the rows with no data or &lt;Null&gt;.<\/p>\n\n\n\n<p class=\"has-yellow-background-color has-background wp-block-paragraph\"><em>Question 15 &#8211; How many patients live <strong>outside<\/strong> Massachusetts (i.e. didn\u2019t get any town data joined)? ____ (Hint:&nbsp; there is an option to query for \u201cis null\u201d)<\/em><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/commons.mtholyoke.edu\/emarcano\/geog210\/geog210-ex3\/#top\">Return to Top of page<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/commons.mtholyoke.edu\/emarcano\/geog210\/\">Return to Geog 210 page<\/a><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"part2\"><strong>Part 2: Case Study: Practicing Joins in Chicago<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Now that you have learned how to do attribute\/spatial joins, it is time to practice the skills you have learned so far.&nbsp; Say that you are a city employee in Chicago and it is your job to decide where to direct maintenance funds. Your supervisor has asked you to choose a zip code in particular need of attention as part of an effort to equalize public resources across all neighborhoods.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">How will you pick a place, without taking to the sidewalks and looking yourself?&nbsp; Chicago has a 311 system, where residents can call and make complaints or requests about maintenance problems.&nbsp;&nbsp; Rather than examining all 311 complaints, you have chosen a few to study:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Street Lights (all lights on pole out)<\/li>\n\n\n\n<li>Pot Holes<\/li>\n\n\n\n<li>Sanitation Code Complaints<\/li>\n<\/ul>\n\n\n\n<p class=\"has-light-blue-background-color has-background wp-block-paragraph\">NOTE: The city of<a href=\"https:\/\/data.cityofchicago.org\/\" target=\"_blank\" rel=\"noreferrer noopener\"> Chicago GIS data <\/a>website, the source of data for this part, has many more GIS themes about Chicago, in case you&#8217;re interested.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To begin, add the shapefile and tables (in dbf format) in the folder for Part 2 to a new ArcGIS Pro map and explore the information they contain.&nbsp; Notice that the shapefile shows the boundaries of each zip code in Illinois, while each of the tables contains the \u201c311\u201d complaints, in the three categories above, for the city of Chicago<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Your goal is to find out which zip code has the greatest number of open (unresolved) complaints.&nbsp;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A few hints before you get started:<\/p>\n\n\n\n<ol style=\"list-style-type:lower-alpha\" class=\"wp-block-list\">\n<li>As you learned in Part 1, you will need to summarize the complaints by zip code before joining them to the shapefile.<\/li>\n\n\n\n<li>Not all zip codes will have values.<\/li>\n\n\n\n<li>Once all three tables are joined to the shapefile, you can create a new field in the attribute table to add up the complaints from all three categories.&nbsp; (Note: you will get some error messages because not all zip codes will be included in all tables.&nbsp; That\u2019s fine\u2014these zip codes\u2019 sums will turn into zeros and you can ignore them.) This process should give you the zip code with the most open complaints!<\/li>\n<\/ol>\n\n\n\n<p class=\"has-yellow-background-color has-background wp-block-paragraph\"><em>Question 16. What zip code did you select for special maintenance attention? <\/em><\/p>\n\n\n\n<p class=\"has-yellow-background-color has-background wp-block-paragraph\"><em>Question 17. How many complaints were located in the selected zip code? <\/em><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"#top\">Return to Top of page<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/commons.mtholyoke.edu\/emarcano\/geog210\/\">Return to Geog 210 page<\/a><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<p class=\"wp-block-paragraph\">Featured image on top of page from<a href=\"https:\/\/www.boredpanda.com\/funny-terrible-maps\/\" target=\"_blank\" rel=\"noreferrer noopener\">Terrible Maps<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Census Data and Join Operations Contents Return to Geog 210 page Part 1: Data Manipulation and Join Operations In this exercise, you will be working with US census data.&nbsp; To understand this data, read the short document \u201cUS Census and GIS\u201d AND the section \u201cThe main geographical hierarchy\u201d in pages [&hellip;]<\/p>\n","protected":false},"author":793,"featured_media":507,"parent":204,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_monsterinsights_skip_tracking":false,"footnotes":""},"class_list":["post-496","page","type-page","status-publish","has-post-thumbnail","hentry"],"_links":{"self":[{"href":"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-json\/wp\/v2\/pages\/496","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-json\/wp\/v2\/users\/793"}],"replies":[{"embeddable":true,"href":"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-json\/wp\/v2\/comments?post=496"}],"version-history":[{"count":32,"href":"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-json\/wp\/v2\/pages\/496\/revisions"}],"predecessor-version":[{"id":1093,"href":"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-json\/wp\/v2\/pages\/496\/revisions\/1093"}],"up":[{"embeddable":true,"href":"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-json\/wp\/v2\/pages\/204"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-json\/wp\/v2\/media\/507"}],"wp:attachment":[{"href":"https:\/\/commons.mtholyoke.edu\/emarcano\/wp-json\/wp\/v2\/media?parent=496"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}