Mapping Place-Name Data

Application: The Structure of an ArcGIS-Compatible Table

1. In the Windows Explorer, navigate into the folder  Mapping Place Names.
2. Locate the file  stateinfo.xlsx, which is a Microsoft Excel workbook, and double-click on it to open it in Excel.
3. Examine the workbook’s structure:
• The simple rectangular arrangement of the data;
• The initial row containing the attribute names;
• Subsequent rows each containing the data of one feature (called a record);
• Each column containing a single attribute as it varies from feature to feature (called a field).
• The worksheet names, e.g. Dates, in the tabs at the bottom of the window. You can have many worksheets in the same workbook.
4. Close the workbook.

Procedure : Joining a Table to a Layer

Any map layer can have its attribute table extended by joining another table to it, as long as they have an exactly matching attribute.

1. In  ArcGIS Pro, in the Catalog, navigate into the folder with the table to be joined, e.g.  Mapping Place Names.
2. If the join table is in an Excel workbook, e.g.  stateinfo.xlsx, the latter will have a disclosure button to its left.
   
   In ArcGIS Pro, Excel workbooks are workspaces, and like folders they can be opened to see a list of their different tables.
   
   Click on the disclosure button or double-click on the workbook to reveal its contents.
   
   Both Excel worksheets and named regions (discussed below) will appear as separate “tables”, as in the image at the right.
   
   Worksheets will all have a $ at the end of their names.
3. Drag the desired join table onto your map, e.g.  Dates$.
   
   After adding a table, the Contents will list it at the very bottom in the section labeled Standalone Tables; the reason is that a table by itself is not displayable on the map.
4. If no records are selected in the join table and in the target layer, ArcGIS Pro will attempt to match every record in the former to one or more records in the latter’s attribute table.
   
   However, if any records are selected in either, it will limit the join to just those records.
   
   A limited match usually isn’t what you want, so in that case look in the ribbon Map and its section Selection for the button  Clear, and if it’s active (not dim), click on it.
   
5. In the table of Contents, right-click on the target layer to which the table will be joined, e.g. states.
6. The layer’s contextual menu will now appear; select the menu item Joins and Relates, followed by the menu item  Add Join.
7. In the dialog Add Join, in the menu Input Table, every table and layer will be listed; make sure that the desired target layer is selected, e.g. state.
8. In the menu Input Field, select the field in the target layer that will be used for the join, e.g. STATE_NAME.
9. In the menu Join Table, select the table to be joined, e.g.  Dates$.
10. In the menu Join Field, select the matching field in the join table, e.g. State.
11. It’s usually worth checking to see if the join will do what you expect, so click on the button Validate Join and read its description of what it will be able to join, e.g. “A one - to - one join has matched 51 records. The input table has 51 and the join table has 51 records”. A join table may have fewer records or more records than the target layer, but that’s OK.
12. Click on the button OK.
    

Exercise: Mapping the Presidential Election

On the Internet, search for a table of the electoral votes by state in the most recent presidential election, and map them. Note that they are extensive quantities!

Nebraska and Maine are unique in that they distribute their electoral votes both statewide (2) and by congressional district (1 each). How might you represent that on your map? (Hint: Look at some of the patterns available to fill polygons in the standard symbology dialog — can you determine how they’re constructed?)

Set Up: Adding a Census Tract Layer

1. In  ArcGIS Pro, click on the button  New Map File.
2. Click on the button  Add Data.
3. In the dialog Add Data, navigate into the folder  Mapping Place Names.
4. In the folder  Mapping Place Names, click on the file  CENSUS2020TRACTS_POLY.shp.
5. Click on the button Add.
6. Save your map document in the folder  Mapping Place Names, with a name such as Census.mxd.

Procedure : Downloading Census Data from the U.S. Census Bureau

1. If necessary, start up a web browser:
1. Click on the menu  Start.
2. Point at the menu item All Programs.
3. Locate your preferred web browser,  Chrome or  Firefox, and click on it.
2. In your web browser, visit the web address www.census.gov.
3. On the top of the web page Census.gov, point at the menu Data & Maps to let it drop down its menu, then cin the lower left corner, click on the link Explore data on data.census.gov (you can also just start on this page once you’re used to it).
4. On the Web page Explore Census Data, click on the link Advanced Search.
   
5. On the left side of the web page Advanced Search there are several ways to access census data: by topics, by geography, by years, by surveys, etc.:
   



You can approach your search for data by starting with any one of these parameters, but you’ll eventually need to specify all of them, and what you find can depend on where you start, as that can narrow the availability of later parameters.

In this example we will start with geographies, then topics, then years and surveys.

6. Geography must be described in multiple steps, by specifying:
• The geographic type, the unit for which data will be summarized, for example the census tracts in the image to the right; and
• The geographic area, a larger “containing” geography within which the above data is located. It is generally specified by place name, for example Hampden County in Massachusetts, shown at the right.

In the column BROWSE FILTERS, click on the item Geography:




7. In the column GEOGRAPHY, select, for example, Tract.
8. In the column WITHIN (STATE), select, for example, Massachusetts.
9. In the column WITHIN (COUNTY), select, for example, Hampden County, Massachusetts (this is the county south of Amherst, containing Springfield, Chicopee, and Holyoke).
10. In the column HAMPDEN COUNTY, MASSACHUSETTS click on, for example, the checkbox  All Census Tracts within Hampden County, Massachusetts.
     
    In the upper left corner of the window, a list of the filters you’ve selected will appear.
11. In the column BROWSE FILTERS, click on the item Topics:
    
    
12. In the column TOPICS, select, for example, Income and Poverty.
13. In the column INCOME AND POVERTY, select, for example, Poverty.
14. In the column POVERTY, click on, for example, the checkbox  Poverty.
     
    In the upper left corner of the window, the list of the filters you’ve selected will now include this topic.
15. Finally, click on the button Search.
16. A new dialog will appear, displaying a list of the various tables of information available for this topic and at this level of geography:
    
    
    
17. In the list of Results, click on, for example, the button S1701 | POVERTY STATUS IN THE PAST 12 MONTHS, which opens a table that provides data from the American Community Survey (ACS) on the poverty status of individuals in different categories:
    
    
18. Below the table button there’s a link, e.g. ⊕ View All 12 Products, and if you click on it, it will expand to reveal the source of the data, by default the most recent source, e.g. 2023: ACS 5-Year Estimates Subject Tables. Choose a different year if appropriate.
    
    With five years of data there are enough samples to provide useful information for this geographic type (tract), but with a margin of error on the order of 10%.
    
    A much larger geography type, e.g. County, would let you see a more up-to-date table, the 1-year estimates, which would also have enough samples to reduce the margin of error to roughly 5%. A smaller geography type would require a longer time period to collect enough data to maintain a reasonable margin of error, and not all of that data is available.
    
    You can now review the data and scroll through it to make sure it has the information in which you’re interested, e.g. the attribute Below poverty level for all individuals.
    
    Here you can see the data that’s available for this combination of parameters: scroll down to see different subsets of the Population for whom poverty status is determined (breakdowns by age, gender, and race), and scroll to the right to see the different tracts and their number of individuals total and living in poverty. Note the subheadings of Estimate and Margin of Error.
    
    You can also click on the different tables in the list on the left side to see their content.
19. At the top of the page, click on the link Download Table Data.
20. The list of tables now reveals checkboxes next to them; click on the box next to the desired table(s), e.g.  POVERTY STATUS IN THE PAST 12 MONTHS.
21. Click the button ⤓ Download.
22. In the dialog Select Table Vintages to Download, note that one survey is selected, but you could add others if you wanted. The data file type will be CSV, inside a compressed ZIP file. Click on the button DOWNLOAD .ZIP.
    
    
    
23. In the next dialog Downloads, it will inform you that it’s preparing your download, then triggering the download.
24. The data will be downloaded as a single compressed file named something like  ACSST5Y2023.S1701.zip. ArcGIS cannot see inside zipped files, so:
1. Locate this file in your downloads folder, right-click on it to bring up its contextual menu, and select the menu item Extract All… ;
2. In the dialog Extract Compressed (Zipped) Folders:
1. Click on the button Browse…;
2. In the dialog Select a destination, navigate to your folder  MappingPlaceName;
3. Click on the button New folder and give it a relevant name, e.g. ACSST5Y2023.S1701 or simply Poverty;
4. Click on the button Select Folder.
3. Now back in the previous dialog, click on the button Extract.
25. The decompressed folder contains three files, with names like:
• ACSST5Y2023.S1701_data_with_overlays.csv — the actual census data, structured differently than above for use with GIS, with each tract in its own row.
• ACSST5Y2023.S1701_metadata.csv — a list of the various fields in the previous file providing explanations of what they represent. Such ”data about data” is known as metadata.
• ACSST5Y2023.S1701_table_title.txt — additional metadata providing background information such as source, methdology, etc.

Procedure : Downloading Census Data from the National Historical Geographic Information System

1. If necessary, start up a web browser:
1. Click on the menu  Start.
2. Point at the menu item All Programs.
3. Locate your preferred web browser,  Chrome or  Firefox or  Internet Explorer, and click on it.
2. In your web browser, visit the web address http://www.nhgis.org/.
3. In the upper right corner of the web page Welcome to NHGIS, click on the link Login.
4. On the web page Login, log in with an existing account or create a new one and log in with it.
5. Back on the web page Welcome to NHGIS, on the left side there are a number of useful links that provide general information about the data provided by NHGIS, including a User’s Guide.
6. To get to the data, click on the link Select Data.
   
7. On the web page NHGIS data finder there are several ways to access census data: by geographic level, by years, by topic, by datasets (see the image at the right).
   
   At some point you must specify all of these, but in this example we will start by clicking on the button Geographic Levels.
8. Geography must be described in two steps, by specifying:
• The geographic level, the unit for which data will be summarized, for example the census tracts in the image at the right; and
• The geographic area, a larger “containing” geography within which the above data is located. It is generally specified by place name, for example Hampden County at the right. In NHGIS you specify the geographic area later.

In the dialog Geographic Levels, select one or more levels, e.g. Census Tract:



Your selection(s) appear in the section Selected Geographic Level Filters at the top of the dialog.


9. Click on the button Submit.
10. A very, very long list of matching data sets appears:
    
    
    
    To filter the list further, click on the button Years.
    
11. In the dialog Years, select one or more years, e.g. 1970.
12. Click on the button Submit.
13. Your selection(s) in the section Selected Geographic Level Filters at the top of the web page will now be updated with the year, and the list of matching data sets will be substantially filtered:
    
    
    
    
    To filter the list further, click on the button Topics.
    
14. In the dialog Topics, in the list on the left side, click on a general subject area, e.g. Population , and then click on a particular subject , e.g. Poverty (Income Relative to Poverty Level).
    
    
    
    
15. Click on the button Submit.
16. Note the box Apply Filters in the upper left corner of the web page, which now also shows your topic choice:
    
    
    
17. In the area Select Data, click on the list item NT11. Aggregate Income Deficit for Unrelated Individuals Below Poverty Level, which is noted as being from 1970_Cnt4Pb, the decadal census.
18. Census tract boundaries and designations often change from decade to decade, so to make sure you get the appropriate set, click on the tab GIS Files. These are provided as Tiger Line files, sometimes updated, so select the most recent version. Note that these files are nationwide, and can be quite large.
19. In the upper right corner of the Data Finder window you should see the Data Cart pane. Click on the button Continue.
20. In the window Data Options, your selections will be displayed. Click again on the button  Continue.
21. In the window Review and Submit, under the section Table File Structure and the button Comma delimited (best for GIS), check on the button Include additional descriptive header row (best for spreadsheets). You may also wish to provide a Description for later reference.
22. Click on the button Submit.
23. In the next window, a list of your submissions will appear. The status of your current submission will initally be queued, but it will eventually change to completed. At this time you will also receive an e-mail. In both cases links will appear to download your data and GIS files.

Procedure : Making a Census File Compatible with ArcGIS Using Excel

1. Double-click on the file ACSST5Y2019.S1701_data_with_overlays.csv to open it automatically in  Excel and inspect its contents:



The first two rows have two versions of column headers, row 1 with short names, row 2 with longer, more descriptive names. The actual data begins in row 3.

The selected cell C2 has its content displayed in full in the formula bar above columns B and C: it’s the estimate of the total population for whom poverty status is determined (which is a subset of all survey respondents).

To easily see these descriptions, you have to either make the columns wider or make the rows higher and let the text wrap. For the latter:

1. Select row 2 by clicking on the row header (i.e. the numbered box 2);
2. Right-click on any one of them, and in the contextual menu that appears select  Format Cells…;
3. In the dialog Format Cells, click the tab Alignment (if necessary), and in the area Text control check on Wrap text;
4. Click the button OK and the row heights should increase to display their contents;
5. Adjust the row heights and column widths to the desired amount by clicking and dragging the bottom and right borders of their headers, respectively.
2. The descriptive labels in the second row of a Census Excel file are important for understanding the meaning of the attributes. However, they are too complex to be used as column headers by ArcGIS — these names cannot include spaces, exclamation points, hyphens, etc.; they must include only letters (specifically the first character), numbers, and the underscore _ . So we will use the first row instead.
   
   Because column headers must be immediately above the data, we will swap the first two rows:
1. In the application  Excel, right-click on the row header 1, just to the left of the column header GEO_ID, and in its contextual menu click on the menu item  Cut.
2. Right-click on the row header 3, and in its contextual menu click on the menu item Insert Cut Cells. The cut row will be inserted before row 3, and the column header GEO_ID into the cell A2.
3. Because we are retaining the descriptive labels in the worksheet, there will be non-table data present in this worksheet. We must therefore name the region of cells covered by the table, and use that name when joining, instead of the worksheet name (here called ACSST5Y2019.S1701_data_with_ove).
1. Click on the cell in the first row and first column of the table, here the cell A2 containing the column header GEO_ID.
2. Select the entire first row of data by simultaneously holding down the keys Control and Shift and then pressing the key → (right arrow). The combination Control → by itself jumps to the end of the first contiguous block of data, while the combination Shift → adds just one cell to the original selection of the cell A2.
3. Now add all of the other rows to your selection by again simultaneously holding down the keys Control and Shift and then pressing the key ↓ (down arrow).
4. Click in the Name Box  that’s located above column A, which should contain the first cell name, A2, type a descriptive name, e.g. Poverty, and press the key Enter. Note that spaces and most special characters aren’t allowed in these names.

Named regions are known everywhere inside a workbook, including within other worksheets. The Name Box  has a menu button at its right end that lets you select any named regions you’ve defined. Names can also be defined, edited, and deleted by selecting the menu Formulas , looking in the section Defined Names, and then clicking on the menu item Name Manager.

4. Modifications such as cell wrapping and named regions are not saved with CSV files, so this file should instead be saved as an  Excelworkbook (this will also prevent annoying messages about loss of features whenever you save it in the original format).
   
1. Select the menu File, and then click on the list item Save As, and then click on the name of the current folder.
2. In the dialog Save As, in the menu Save as type:, select Excel Workbook (*.xlsx).
3. Click the button Save.
5. Quit  Excel, which will allow you to add this table to  ArcGIS Pro.

Procedure : Creating a Matching Join Field in ArcGIS

When you want to join a source table to a target geography layer’s attribute table, the first step is to determine which fields, if any, match. Which columns have the same data or almost the same data? They may have different names, e.g. GEO_ID and GEOID10 in the previous procedure, but that’s actually not important as fields will be referenced individually when setting up the join. They may have differences in representation, e.g. abbreviations in one and full names in the other. There may be small differences or there may be large ones, but in either case you must make sure there are identical matches for every feature that you want to join.

Often you’ll need to create a new field and calculate a set of matching values. This can be done in either table, depending on their usage. If the attribute table is the best location for this new field, the Field Calculator in ArcGIS can be used to make small changes or automate this process for all fields when you can identify a simple pattern to the differences.

For example, in the previous procedure Making a Census File Compatible with ArcGIS Using Excel, compare the  Excel table with the attribute table of  CENSUS2020TRACTS_POLY. The FIPS numbers in the latter show up in the former, but with an extra unvarying string prepended to it, 1400000US, which describes the type of data represented:

• The first three digits identify the summary level of the data; 140 means “State-County-Census Tract”.
• The next four digits are the “geographic variant” and “geographic component”, which in most cases are just set to 00 and 00.
• The last two digits provides an internationalization by using the International Standards Organization’s two-character country code, here US for the United States of America.

Since this geographic identifier will appear in all data downloaded from the Census Bureau, while the geography layer could be re-used many times, it’s better in this case to add a field to the latter and calculate matching values for the identifier:

1. In the attribute table, click on the menu  Table Options, and select the menu item Add Field….
2. In the dialog Add Field, in the text field Name, provide a name, e.g. GEO_ID.
3. In the menu Type, select Text.
4. In the section Field Properties, in the text field Length, provide a number — e.g. 20.
5. Click the button OK.
6. Before calculating values for a field, always check to make sure that no records are selected, or those will be the only ones to which the calculation will apply: in the table’s menu bar, if the button  Clear Selection is not grayed out, click on it.
7. Locate the new field (usually the very last column), right-click on its header to bring up its contextual menu, and click on the item  Calculate Field.
8. In the dialog Field Calculator, in the text field field_name =, type an expression to update field values, e.g. to prepend the census identifier’s common text string to the FIPS value for that record:
   
         "1400000US" & [GEOID10]
   
   The VBScript language is used here by default, but Python could also be used (and often provides better tools for complex calculations). The text string must be surrounded by double quotes, and the field name used to provide this record’s value must be surrounded by square brackets; the & character is used to concatenate the two together. As an alternative to typing in the field name, you can also insert it by double-clicking on it in the list Fields:.
9. Click the button OK, and the field should be populated with the correct values.

If you just need to change one field (e.g. state names but “District of Columbia” appears in one and “Columbia” in the other), you can select that one record, skip step 6 above, and run the Field Calculator with the matching value, and it will only change that one record.

Application: Working with a Census Table in ArcGIS

1. Follow this procedure and join the census table ACSST5Y2019.S1701_data_with_overlays.xlsx to the layer  CENSUS2020TRACTS_POLY using the field GEO_ID in both cases.
2. Symbolize the layer with one of the quantitative fields from the second layer, e.g. S1701_C01_002E (Population for whom poverty status is determined: Below poverty level), normalized by S1701_C01_001E (Population for whom poverty status is determined: Total).



The areas in red above are in eastern Holyoke (the area known as “The Flats”) and in west-central Springfield, surrounding the downtown area.

Exercise: Mapping More Census Data

Revisit the Census Bureau’s data site as described in the procedure Downloading Census Data from the U.S. Census Bureau, find another topic of interest to you, e.g. educational attainment, download its data, and map it.

Do you have any ideas about how you might display both it and the previous poverty data on the same map?