Amherst College I.T. I.T. home. Amherst College.
I.T. home.
IT > Software > Geographic Information Systems > Mapping Place-Name Data

Geographic Information Systems

Mapping Place-Name Data

Previous: Constructing and Sharing Maps
 
Following: Mapping Coordinate Data


Not all geographic data is in a GIS-ready format. Commonly it is in the form of a table of values assigned to place names representing geographic features such as states or street addresses.

Topics

Procedures

  1. Joining a Table to a Layer

  2. Saving a Copy of a Data Set

  3. Downloading Census Data from the US Census Bureau

  4. Downloading Census Data from the National Historical Geographic Information System

  5. Making a Census File Compatible with ArcGIS Using Excel

  6. Creating a Matching Join Field in ArcGIS

  7. Determining Map Layer Attribute Types

  8. Formatting Excel Data for Compatibility with ArcGIS

  9. Formatting Excel Cells for Compatibility with ArcGIS

  10. Making an Excel File Compatible with ArcGIS

  11. Geocoding a Table of Street Addresses


Getting Started

Since this tutorial will be using specific maps and data, the first step is to make your own copy of the tutorial data.

Set Up: Getting the Tutorial Data

  1. Make sure you have a copy of the folder  MappingPlaceNamesin your local drive or Google Drive, either within the folder Introduction to GISor copied separately, as described previously in Constructing and Sharing Maps.

The folder  MappingPlaceNames contains the following files:

 states.shp  stateinfo.xls  MASSCENSUS2010TRACTS.shp  HolyokeSchools.xls

Since some uses of ArcGIS require names without spaces or special symbols, do not rename the folders or files.

Set Up: Initializing ArcMap and Adding the States Layer

  1. Start up the  ArcMap software (see Constructing and Sharing Maps for details).
  2. In the toolbar Standard, click on the button Add Data Icon Add Data.
  3. In the dialog Add Data, navigate into the folder  MappingPlaceNames; if necessary, make a new connection to it first (see Constructing and Sharing Maps for details).
  4. In the folder  MappingPlaceNames, click on the file  states.shp.
  5. Click on the button Add.

ArcMap will now display the map of the United States that we saw before:

As before, it’s a good idea to save your map early and often.


Joining Tabular Place-Name Data

When data is associated with place names, we can join it to an existing layer providing the geography.


Place-Name Data

Geographic data ideally comes in the form of a layer, which is a set of geographic features with attributes assigned to them. We’ve already seen examples of state polygons and city points, whose attribute tables associate these features with information such as area, population, etc.

Layers also include information about the geography of the features, so they can be immediately displayed as a map, as above. Recall that the details of the geography are hidden inside the Shape field:

Very often, however, you may obtain or create a table of data whose only geographic connections are a set of place names that abstractly represent the geographic feature, e.g. “Massachusetts”, “Alabama”, etc. In other words, it doesn’t have a Shape field:

Such place-name data is also known as toponymic data.

Fortunately, if you have access to a map layer that defines the same geographic regions with the same names, you can join your table to that layer. Joining essentially extends its attribute table with new fields (columns), which you can then use to symbolize it, etc.

Application: The Structure of an ArcGIS-Compatible Table

  1. In the Windows Explorer, navigate into the folder  MappingPlaceNames.
  2. Locate the file  stateinfo.xls, 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.

Besides Microsoft Excel XLS or XLSX files, ArcGIS can also read tables in several other formats:

CSV
Comma-separated values files are simple text files with each field in the table separated by commas (if the data includes commas, the field value must be enclosed in double quotes).
TXT or TAB
Tab-separated values files are also simple text files, with each field in the table separated by tabs.
DBF
dBASE files are an old database format that are still used for Shapefile attribute tables.
MDB
Microsoft Access files are a newer database format.

All of these formats can also be read by Microsoft Excel (but it will only open TAB files directly if the extension is changed to TXT).

If you have data in these formats and are going to be making any changes with Excel, it’s generally better to first save it as XLSX; otherwise Excel will always complain about potential data loss when you save.

Warning: To successfully make use of tables, their file names should start with letters and afterwards include only letters, numbers, and underscores.


Joining Tables with a Place Name Attribute

Because the map layer and table shown above have a matching place name attribute:

  • the map layer  states.shp and its field STATE_NAME, and
  • the table  stateinfo and its field State.

they can be joined together, row by matching row.

Procedure : Joining a Table to a Layer

Any map layer can have a table joined to it, as long as they have an exactly matching attribute that can be used to relate data for each feature.

  1. In  ArcMap, in the toolbar Standard, click on the button Add Data Icon Add Data.
  2. In the dialog Add Data, navigate into the folder with the table to be joined, e.g.  MappingPlaceNames.
  3. Double-click on the table to be joined, e.g the Excel workbook  stateinfo.xls.
  4. Adding an Excel WorksheetIn ArcGIS dialogs, Excel workbooks are workspaces, meaning you can open them like folders and see an overview of their contents.

    In particular, they display their worksheets and named regions (discussed below) as separate “files”.

    Worksheets all have a $ at the end of their names, as in the image at the right.

    If the join table is inside a workbook, double-click on the latter to see its contents, and then add the desired table to your map document by double-clicking on it, e.g.  Dates$.
  5. After adding a table, the Table of Contents will switch to the  List by Source view; the reason is that a table by itself is not displayable on the map, and therefore won’t show up in the  List by Drawing Order view. You wil probably want to switch back to the latter, though, since it’s simpler.
  6. Join Data DialogIn the Table of Contents, right-click on the layer to be joined, e.g.  states.shp.
  7. The layer’s contextual menu will now appear; select the menu item Joins and Relates, followed by the menu item Join....
  8. In the dialog Join Data, in the menu What do you want to join to this layer?, make sure that Join attributes from a table is selected.
  9. In the menu 1. Choose the field in this layer that the join will be based on:, select the matching field in the map layer, e.g. STATE_NAME.
  10. In the menu 2. Choose the table to join to this layer, or load the table from disk:, select the table to be joined, e.g.  Dates$.
  11. In the menu 3. Choose the field in the table to base the join on:, select the matching field in the table to be joined, e.g. State.
  12. Click on the button OK.

Alternative: Rather than use the “internal join” described above in Steps 1-5 and 10, you can directly join an external table in Step 10 by clicking on the button Document Open Browse; this is a slightly easier one-step process.

However, this “external join” has the disadvantage that you won’t get error messages describing incompatibilities in your table. It also obscures the location of the table from the ArcMap user, and if the map document loses track of it you can’t see its path to help you find it again to fix the join.

To see the results of the joining tables procedure, try out the new attributes:

Application: Working with a Joined Table

  1. In the Table of Contents, right-click on the layer  states.shp and select the menu item  Open Attribute Table.


     
  2. Examine the attribute table’s structure: the fields from the shapefile are now immediately followed by the fields from the joined table.
  3. Right-click on one of the field headers, e.g. State, to bring up its contextual menu, and select the menu item Properties… .
  4. In the dialog Field Properties, note that the visible field name is just an alias, and the actual field name begins with the table name, e.g. Date$, followed by a period.

    This field-name prefix ensures that there are no conflicts between fields from the joined tables that might have the same name.

    Note that Alias: is editable, so you can use it to change a field name if you think it could be more descriptive; it has fewer restrictions on length and characters than the actual name.
  5. Close the dialog Field Properties and then the attribute table.
  6. Symbolize the layer with one of the quantitative fields from the joined table.
  7. Save the document by clicking on the button  Save.

Important Note: The additional fields from the joined table have not been permanently added to the map layer, just temporarily linked to it.

This means that if you move the table file or the map document to some other location, the map may no longer work because ArcMap could be looking in the wrong place for the table file.

When ArcMap can’t find a file, it places a red exclamation point ! next to its name in the Table of Contents, and you can click on ! to start a dialog to relink it.

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?)

Once you’ve joined a layer and a table, sometimes you may want to save the result as a new shapefile with a merged attribute table:

Procedure : Saving a Copy of a Data Set

  1. In ArcMap Icon ArcMap, in the Table of Contents, right-click on the name of the layer, e.g.  states.
  2. In the layer’s contextual menu, point at the menu item Data, then in the submenu that appears click on the menu item Export Data….
  3. In the dialog Export Data, in the menu Export, make sure that All features is selected.
  4. Near the text field Output shapefile or feature class:, click on the button Document Open Browse to navigate to an appropriate location for the new data set, e.g. the folder MappingPlaceNames, and give it a descriptive name, e.g. states_and_dates.shp. Remember that file names should start with letters and afterwards include only letters, numbers, and underscores
  5. Click on the button OK.
  6. The dialog ArcMap will now appear, asking if you want to add the exported layer to the map; click Yes or No.

One advantage of using joins is that you can more easily change the contents of the joined table (there will be more about editing from within ArcMap later).

However, many applications such as Excel won’t allow you to edit the table if ArcGIS also has it open.

If you aren’t changing the join attribute name, the simplest thing to do is quit ArcMap, edit the table, and reopen the map; the table will be rejoined as previously defined, with the new data.

Otherwise, you’ll need to unjoin the table, remove it from ArcMap (if an internal join), edit it, re-add it to ArcMap, and rejoin it.


Mapping Census Data

An important example of data that is commonly joined to map layers comes from the U.S. Census Bureau.


Downloading Census Data

As another example of commonly available place name data, we’ll work with some more census data. As you probably already know, every ten years there is a census that tries to obtain basic information from 100% of the U.S. population; this is the primary source of the data we looked at previously in the states layer. In addition, the U.S. Census Bureau provides many variations of this data, as well as the results of the annual American Community Survey that tracks detailed information from a small subset of the population collected every year. This data is in tables that you must join to existing layers.

We’ll also use a type of geographic region that you might not be familiar with, census tracts. According to the Bureau, “Census tracts are small, relatively permanent statistical subdivisions of a county....Designed to be relatively homogeneous units with respect to population characteristics, economic status, and living conditions, census tracts average about 4,000 inhabitants.”

Two smaller subdivisions of census tracts are also available, though we won’t use them here. Census blocks are “the smallest geographic unit for which the Census Bureau tabulates 100-percent data....Many blocks correspond to individual city blocks bounded by streets, but blocks — especially in rural areas — may include many square miles.” Census block groups are just that, and are the smallest region available for some sensitive attributes such as income.

Set Up: Adding a Census Tract Layer

  1. In  ArcMap, click on the button  New Map File.
  2. Click on the button Add Data Icon Add Data.
  3. In the dialog Add Data, navigate into the folder  MappingPlaceNames.
  4. In the folder  MappingPlaceNames, click on the file  MASSCENSUS2010TRACTS.shp.
  5. Click on the button Add.
  6. Save your map document in the folder  MappingPlaceNames, with a name such as Census.mxd.

ArcMap will now display a map of the Year 2010 census tracts in Massachusetts:

If you right-click on the layer  MASSCENSUS2010TRACTS and select the menu item  Open Attribute Table, you should see the following:

The census tracts are uniquely named by their FIPS (Federal Information Processing Standards) code, which begins with the two-digit state code in the field STATEFP10 (e.g. 25 for Massachusetts), followed by the three-digit county code in the field COUNTYFP10 (e.g. 001 for Barnstable County), and finally the tract code in the field TRACTCE10 (e.g. 015300). By combining these three together into a text string in the field GEOID10 (e.g. 25001015300), we have a unique identifier that can be used to join other census data to this layer.

Be aware that census tracts change from decade to decade. The Census Bureau provides cartographic boundary shapefiles for its online data (including other regions such as congressional districts and metropolitan statistical areas) at http://www.census.gov/geo/www/cob/.

This particular data set actually comes from MassGIS, and includes 100% data on population in each census tract, in the field POP100_RE.

The Census Bureau also lets you download much of their data from http://www.census.gov/. As an example, we’ll map some poverty data from the latest American Community Survey. This survey provides much more detailed information than the decadal census, and is drawn annually from a small sample of the population.

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

  1. If necessary, start up a web browser:
    1. Click on the menu Start Menu Icon 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 Explore Data to let it drop down its menu, then click on the link Explore Data Main.
  4. On the web page Try out our new way to explore data, click on the button Go to data.census.gov (you can also just start on this page once you’re used to it).
  5. On the Web page Explore Census Data, click on the link Advanced Search.
  6. 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.:
  7. Census data advanced search dialog

    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.

  8. Hampden County with Census TractsGeography 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:

    Census data advanced search geography filter dialog

  9. In the column GEOGRAPHY, select, for example, Tract.
  10. In the column WITHIN (STATE), select, for example, Massachusetts.
  11. In the column WITHIN (COUNTY), select, for example, Hampden County, Massachusetts (this is the county south of Amherst, containing Springfield, Chicopee, and Holyoke).
  12. In the column HAMPDEN COUNTY, MASSACHUSETTS click on, for example, the checkbox  All Census Tracts within Hampden County, Massachusetts. At the bottom of the window, a banner appears to indicate your final selection.
  13. In the column BROWSE FILTERS, click on the item Topics:

    Census data advanced search topics filter dialog
  14. In the column TOPICS, select, for example, Income and Poverty.
  15. In the column INCOME AND POVERTY, select, for example, Poverty.
  16. In the column POVERTY, click on, for example, the checkbox  Poverty. At the bottom of the window, a banner appears to indicate your final selection.
  17. Finally, click on the button Search.
  18. A new dialog will appear, displaying a list of the various tables of information available for this topic and at this level of geography:

    Census data advanced search tables list dialog
  19. In the list Tables, click on, for example, POVERTY STATUS IN THE PAST 12 MONTHS, which expands to provide data on the poverty status of individuals in different categories:

    Census data advanced search results for poverty
  20. At the top of the page, the menu Product shows the year and survey providing this particular set of data, e.g. 2019: ACS 5-Year Estimates Subject Tables.

    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%.

    At the top of the page, clicking on the button FILTER would open the dialog that allows you to change the geography type to a much larger one, e.g. County, which 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%. (In general you could also select a smaller geography type, but it won’t actually let you see that data in this case because the margin of error would be significantly larger.) Click again on the button FILTER to hide this dialog.

    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.
  21. At the top of the page, click on the button DOWNLOAD.
  22. 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.
  23. Click the button DOWNLOAD SELECTED (#).
  24. In the dialog Download / Print / Share, note that one survey is selected, but you could add others if you wanted; and the default file type is CSV, which is correct for GIS. Click on the button DOWNLOAD.

    Census data download dialog
  25. In the next dialog We’re preparing your files, wait for the appearance of the button DOWNLOAD NOW, and then click it.
  26. The data will be downloaded as a single compressed file named something like  ACSST5Y2019.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. ACSST5Y2019.S1701 or simply Poverty;
      4. Click on the button Select Folder.
    3. Now back in the previous dialog, click on the button Extract.
  27. The decompressed folder contains three files, with names like:
    • ACSST5Y2019.S1701_data_with_overlays.csv — the actual census data, structured differently than above for use with GIS, with each tract in its own row.
    • ACSST5Y2019.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.
    • ACSST5Y2019.S1701_table_title.txt — additional metadata providing background information such as source, methdology, etc.

You may have noticed in this last procedure that the Census Bureau only has data online starting in 2000. If you want data and boundary files from earlier years, visit the National Historical Geographic Information System, which provides a similar system.

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 Menu Icon 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. American FactFinderTo 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. Hampden County with Census TractsGeography 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:

    American Factfinder Geography List

    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:

    NHGIS Data Finder Geography-Based Tables List

    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:

    NHGIS Data Finder Geography and Year-Based Tables List


    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).

    Census Data Finder Topics

  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:

    American Factfinder Selections 2
  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.

Joining Census Data

Once you’ve downloaded census data, you’ll need to clean it up to make it compatible with ArcGIS.

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:
  2. Census Data in Raw Form

    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.
  3. 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.
  4. 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.

  5. 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.
  6. Quit  Excel, which will allow you to add this table to  ArcMap.

Sometimes there’s no exact match between the fields in two tables, and a new one must be created:

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  MASSCENSUS2010TRACTS. 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.

The dialog used to add a new field to a table.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. The dialog used to add data to a field.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  Field Calculator.
  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.

To see the results of the previous two procedures, try out the new attributes:

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  MASSCENSUS2010TRACTS 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).
  3. 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?


Formatting Tables for Joins

While joining tables may appear straightforward, they need to be in a certain format to ensure success.


Making Attribute Fields Compatible

Before joining two tables using a particular attribute, it’s generally a good idea to ascertain the data type of that attribute in the layer’s table. The reason is that not only the join attribute’s values but also its type must be compatible in the two tables, and appearances can be deceiving.

For example, the POP2010 number in the attribute table for  MASSCENSUS2010TRACTS may appear to be an integer but it could actually be text or a real number.

The following table describes the most common data types.

Some Common ArcGIS Attribute Data Types

Data Type Value Represented Minimum Value Maximum Value Maximum Significant Digits/Characters
Short Integer number
-32,768
32,767
5
Long Integer number
-2,147,483,648
2,147,483,647
10
Float Real number
-3.4 x 1038
1.2 x 1038
6
Double Real number
-2.2 x 10308
1.8 x 10308
15
Text String of characters
254

Procedure : Determining Map Layer Attribute Types

  1. In ArcMap Icon ArcMap, in the Table of Contents, double-click on the layer of interest, e.g.  MASSCENSUS2010TRACTS.
  2. In the dialog Layer Properties, click on the tab Fields:
  3. In the list of fields, scroll down until you find the attribute name you are interested in joining, and note its type.

We can see in the image above that the data type of the FIPS attribute in  MASSCENSUS2010TRACTS is Text.

Question: Could it be another type?

To use this attribute to join an Excel table, the corresponding attribute data type must also be Text. If you look at this Excel table and the join field we used, GEO_ID2, you’ll note the little green flag in the upper left corner of each cell; it indicates that the numbers are actually formatted as text. (Hint: this can also be ascertained from their alignment on the left; Excel aligns numbers on the right.)

Procedure : Formatting Excel Data for Compatibility with ArcGIS

  1. In the application  Excel, click on the column header of the data whose format you want to change, e.g. A (you can also click and drag over specific cells in that column to select them).
  2. Select the menu Format, and then click on the menu item Cells....
  3. In the dialog Format Cells, click on the tab Number.
  4. In the list Category, you can choose a numbe of different formats:
  5. If you want the data to be treated as text, select Text.
  6. If you want the data to be treated as integers, select Number, and then change the number of Decimal Places: to 0.
  7. If you want the data to be treated as real numbers, select Number, and then change the number of Decimal Places: to a value that matches the data in the map layer’s join attribute. Note: because of rounding errors, joining real data should be avoided.
  8. Click on the button OK.
  9. In the menu File, select Save. If necessary, change the name of the file so that it starts with a letter and afterwards include only letters, numbers, and underscores.

Another formatting incompatibility to be aware of is that the content of a cell in the Excel join column cannot wrap, i.e. it must all be on one line, and cells in the table shouldn’t be merged cells, either.

Procedure : Formatting Excel Cells for Compatibility with ArcGIS

  1. In the application  Excel, click on the column header of the data whose format you want to change, e.g. A (you can also click and drag over specific cells in that column to select them).
  2. Select the menu Format, and then click on the menu item Cells....
  3. In the dialog Format Cells, click on the tab Alignment.
  4. In the section Text Control, if necessary turn off the checkbox Wrap Text.
  5. Also, if necessary, turn off the checkbox Merge Cells. Note: this may produce empty cells that misalign your data, so some clean-up may be necessary.
  6. Click on the button OK.
  7. In the menu File, select Save. If necessary, change the name of the file so that it starts with a letter and afterwards include only letters, numbers, and underscores.

More generally, the data in the two join columns must match exactly. In particular, watch out for extra spaces between words and at the beginning and end of data.


Summary: Making an Excel File Compatible with ArcGIS

While Microsoft Excel can make it easy to manipulate data tables, it also has its own way of doing things with which you’ll need to be familiar to make extensive use of it. Such use is outside of the scope of this class, but if you follow the recommendations below, your data tables should be compatible with ArcGIS.

Procedure : Making an Excel File Compatible with ArcGIS

Generally speaking there are five things you need to do to make Excel data compatible with ArcGIS: name it properly, create a row of field names, below that arrange your data in a plain rectangular array, name the table, and make sure the join fields match. Below are general descriptions of how to do this.

  1. Name the file: it should start with a letter and afterwards include only letters, numbers, and underscores.
  2. Create a row of field names:
    1. The very first row in a table must contain unique names for each column. Usually they will in some way describe the attribute that will be in that field, e.g. Population, ZipCode, etc.
    2. Field names must begin with letters, and after that can contain letters, numbers, or the underscore _ . They cannot contain other special characters such as the period . or hyphen -, or spaces (and be careful that you don’t have any spaces at the beginning or end of the names, too!). Note that field names are also case sensitive (upper and lower case letters are distinguished). If your joined data appears as <null>, then check the field names for an illegal character.
    3. Field names cannot be one of a long list of reserved words, e.g. All. If your joined data appears as <null>, then check the field names against this list.
    4. For some types of joined tables, field names must be ten characters or less, though in other types you can use up to 64 characters. It’s a good idea to use short names in any case.
  3. Arrange your data in a plain rectangular array:
    1. Every map feature, such as a state or city, must have its data in a single row.
    2. Every column/field should contain the same kind of information for each feature, e.g. all population values should be in a single column. Blanks are allowed if particular data is missing. Also make sure the values have a consistent data type, e.g. all text, all integers, or all real numbers.
    3. All record and feature data must be contiguous, i.e. there must be no other data or blank rows or columns separating the data, and it must begin immediately below the field name row.
  4. Name the table:
    1. If you have non-table data in the cells around your table, e.g. explanatory notes, you’ll want to select just the range of cells covered by the table and give it a unique name.
    2. If you don’t have any other data in the worksheet besides your table, and it begins in cell A1, then the table can be referenced by its worksheet name. It’s highly recommended that you change the worksheet name to something more illuminating than Sheet0. Whatever name it has, ArcGIS will see it with a $ at the end, e.g. Sheet0$, indicating that it’s using the entire contents of the worksheet.
    3. Unlike field names, worksheet and cell range names have few restrictions like those described in (1)(b) above. However, names with spaces and special characters in them will appear with single quotes around them, e.g. 'My Sheet$'.
  5. Make sure the join fields match:
    1. Make sure the data types in the join fields are compatible: both text, both integers, or both real numbers.
    2. Make sure the Excel join field doesn’t wrap its text and doesn’t have any merged cells.
    3. Make sure the values in the two join fields match exactly, e.g. there are no extra spaces, variations in case, etc.
    4. The two tables don’t need to have the same number of records, e.g. some features could be missing in one table or the other. If the map layer is missing a record that appears in the join table, the latter will be ignored, and if the join table is missing a record that appears in the map layer, its values will appear as <Null>.

Geocoding Street Addresses

Street addresses can be geographically located when you have a special street layer and a geocoder.


Street Addresses

Geographic data often comes in the form of a table of street addresses, for example of schools or businesses, along with attributes describing them such as their name, purpose, etc.:

Geocoding Linear InterpolationA full street address includes a street number, street name, city, state, and zip code. Like other place name data, street names can be associated with a street layer to get a very rough location. To get a more accurate position, we need to know where the street numbers fall along the street.

If a street layer contains details about which addresses lie within which blocks, it can be used with a program called a geocoder to place the addresses on the map as points along the streets. Address locations will be approximate, because only a block’s beginning and ending addresses are usually provided, and others are linearly interpolated along the street (see the block at the right, which places the address 317). In addition, street layers have varying degrees of accuracy.

Street layers are commonly available from cities and towns as well as commercial entities. The ArcGIS software suite comes with the collection ESRI Data and Maps, which includes the commercial package called Street Atlas North America, and a geocoder that works with it.

Set Up: Adding the Street Atlas Layer

  1. In  ArcMap, in the toolbar Standard, click on the button Add Data Icon Add Data.
  2. In the dialog Add Data, make a new connection to the folder  K:\Maps (see Constructing and Sharing Maps for details). This network folder is where Amherst College stores a large amount of data for use in maps.
  3. Navigate into the folder  ArcGIS Books-n-Data\ESRI Data & Maps 2013\streetmap_na.
  4. Click on the file  StreetMap North America.lyr. This is a layer file, which references data in one or more additional files, along with information about how to symbolize them.
  5. Click on the button Add.
  6. You will now see the dialog Geographic Coordinate System Warning; you don’t have to worry about this for now, so click on the button Close.
  7. Click on the button  Zoom In, and click-and-drag a rectangle around the City of Holyoke (north of Springfield and I-90, east of I-91).
  8. In the Table of Contents, turn off and back on the layer  StreetMap North America by clicking on the checkbox to the left of its name, and notice the relationship of the two data sets.

Let’s now geocode a set of addresses using Street Atlas’ street information:

Procedure : Geocoding a Table of Street Addresses

  1. In  ArcMap, click on the menu Tools, then select the submenu Geocoding, followed by the menu item  Geocode Addresses….
  2. The dialog  Choose an Address Locator to use… will appear with a list of address locator databases. If this is first time you’ve geocoded this list will be empty, and you’ll need to choose the correct encoder for the street address database format:
    1. Click on the button Add….
    2. Geocoding Setup Dialog In the dialog Add Address Locator, navigate into the folder  K:\Maps\ArcGIS Books-n-Data\ESRI Data & Maps 2013\streetmap_na\data.
    3. Double-click on the file  Street_Addresses_US.
  3. Continuing in the dialog  Choose an Address Locator to use…, click on the button OK.
  4. In the dialog Geocode Addresses: Street_Addresses_US, the menu Address table: might already list your data set if you’ve previously added it. Otherwise:
    1. Click on the button Document Open Browse and navigate into the folder with the table to be joined, e.g.  MappingPlaceNames.
    2. Double-click on the table to be joined, e.g the Excel workbook  Holyoke Schools.xls.
    3. In the workbook, double-click on a sheet or a named region, e.g.  HolyokeSchools.
  5. Continuing in the dialog Geocode Addresses: Street_Addresses_US, verify that the Address Input Fields match the correct ones in your data table.
  6. Make sure the Output shapefile or feature class: is an appropriately named and located file, e.g. HolyokeSchools.shp in the folder  MappingPlaceNames.
  7. Click on the button OK.
  8. Geocoding Addresses DialogThe dialog Geocoding Addresses… will now appear and provide a summary of the geocoding. It will tell you how many of the addresses in the table were matched, tied, or unmatched (e.g. if a zip code is incorrect it may end up tied with a slightly different street name).
  9. If you have not matched all of the records (very common), you may want to look at how the matches are made. Click on the button Rematch; the dialog Interactive Rematch will appear. It lists each record and gives it a score from 0 - 100 judging the quality of the match. You can use this dialog to interactively improve the match, e.g. by correcting bad data or choosing a more likely address (N.B. sometimes the geocode database can be wrong, too!).
  10. Geocoding Interactive Rematch Dialog

    This dialog can be recalled later by clicking on the menu Tools, then selecting the submenus Geocoding and Review/Rematch Addresses, and finally clicking on the menu item Geocoding Result: ….

  11. When you are finished matching, click on the button Close.
  12. The resulting data will be added to the map with a distinguishing name, e.g. Geocoding Result: HolyokeSchools.

Geocoded Holyoke Schools on the map

Note that these points on the map are created relative to Street Atlas’ description of the streets; this may differ from other descriptions, e.g. the local Planning Department may have more accurate data, while an old map may be less accurate.


Previous: Constructing and Sharing Maps

Mapping Place-Name Data

Top of Page  | Using IT DocumentationIT Home  |  IT Site Map  |  Search IT