CSISS - GIS Cookbook: Recipe

GIS Cookbook: Recipe - Joining a table

Keywords: Tables, joining, relates, sorting, data analysis
Category: Data Analysis
Software: ArcGIS 8/9.x

Problem: I have information in a table that I would like to join to a point, polygon or line layer.

Description: Often times, the information you want to display or analyze within GIS is in two different places or formats. Often you will have a map of a standard area, like the counties in a state or the states of a country, and you will want to add external data to them so you can look at that external data in a spatial context. In this recipe we will look at how to join data to a shapefile.

Scenario:
Ever since the work of John Snow and his mapping of Cholera cases, mapping cases and frequencies of diseases can help to find sources and cures. One of the major research topics of the 20th century is the search for an understanding of cancer. It helps researchers in the subject to know where the cases of cancer are the highest. In this scenario, we are going to look at the state of New Mexico and join the cancer statistics for each county to the counties within the state.

Methodology:
1) Open ArcMap.

2) Add the shapefile you wish to attach your data to.

The Shapefile you are going to work with

3) Right-click on the shapefile and select Open Attribute Table This will display the tabular information currently within the shapefile.

Opening the attribute table of the shapefile we are working with

The New Mexico layer's attribute table

You will need to identify one of these attribute columns to match up your external data too. In this scenario, we will be joining based on the NAME field.

4) Ensure that your external data is in a form that is compatible with ArcMap, either delimited text or .dbf. If you are working in Excel see the recipe Importing an Excel table to your GIS project (ArcGIS 8.x/9.x).

Also, make sure that there is a one-to-one match between the records in the external data source and the shapefile attribute table. This will allow you to join the two tables together. This may require you to do some editing of your external data source. You may have to combine or expand your data to fit the one-to-one relationship.

(For example, if you wanted to join your external data to counties, but your external data was organized in census tracts or cities with county information within them, you would need to combine the information within the data source so that there was only one county entry. You would add up all the cities or census tracts to make one county entry.)

When you are finished editing your data there should be the same number of data entries in your external data as there are in your shapefile attribute table. Make sure you SAVE and CLOSE the external data file you have been working on.

5) In ArcMap, go to the Add Data button. Navigate to your external dataset in either .txt or .dbf form. Select it and click Add

6) Your external data set will appear in the Table of Contents beneath your shapefile. Note: You can view external data tables like this one by clicking on the Source tab at the bottom of the Table of Contents. The Display tab shows only the map layers in the map document.

7) Right-click on your shapefile within the Table of Contents and select Joins and Relates -> Join . Note: This step is always performed on the layer you want to join data TO.

Joining

8) The Join Data window will appear. In Step 1, choose the name of the field in the shapefile you will use to join the data. In Step 2, select your external data source. In Step 3, select the name of the field in your external data source that you are going to join by. Then click OK .

The Join Data Window

If your error message says that the file is busy or already in use, See Pitfall 1

9) Right-click on the shapefile in the Table of Contents and select Open Attribute Table. Scroll to the right and confirm that the data from the external table have been appended onto the original data. If not, See Pitfall 2.

External Data is displayed in the attribute table of your original shapefile

10) Your external information is now spatially referenced and can be used for spatial analysis. For example, See Recipe Making a choropleth map (8.x)
Pitfalls:

This means that you have left the external data file you are trying to import open in another application. Make sure the file you are importing and preferably its host program is closed before you import the data.
If the tables joined successfully, but there are empty cells where you expect valid values, it means the value in the join field for that record did not have a match in the external data. You should double-check to make sure you've selected the correct fields to perform the join.

Authored by: Benjamin N. Sprague Modified: 2/4/05

Copyright © 2002-2015 by Regents of University of California, Santa Barbara Cookbook: Ben Sprague, Ethan Sundilson, Carlin Wong, Sam Ying

Copyright © 2002-2015 by Regents of University of California, Santa Barbara
Cookbook: Ben Sprague, Ethan Sundilson, Carlin Wong, Sam Ying