GIS Cookbook: Recipe - Joining a table  
Keywords: Data, joining, relates, sorting, data analysis
Category: Data Analysis
Software: ArcView 3.2

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

Description: Often times, the information you want to display or analyse 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 your 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 scenerio, we are going to look at the state of New Mexico and join the the cancer statistics for each county to the counties within the state.

Methodology:
1) Open ArcView

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

Your starting theme


3) Click on the shapefile in the table of contents, making it active, and then select Theme ->Table This will display the attribute information currently within the shapefile.

To get your shapefiles attribute table


Your shapefiles attribute table


There is usually only basic information. You will need to identify one of these attribute columns to match up your external data too. In this scenerio, we will be joining to counties.

3) Navigate to and open your external data source. Open to the data you would like to add or join to the shapefile open in ArcView. Your external data will need to be in the correct form to bring it into ArcView, either (txt or .dbf) If you are working in excel see the recipe Importing an Excel table to your GIS project (3.x) This recipe may work for other platforms as well.

4) In your external data, Make sure that there is a one to one match so that each data entry matches with one shapefile attrubute. This will allow you to join the two 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 datasource so that there was only one county entry. You would add up all the cities or cesus tracts to make one county entry.)

5) When you are finished editing your data there should be the same number of data entries in your external data as there are in row attributes (polygons, or ID numbers) in your shapefile attribute table. Make sure you SAVE and CLOSE the external Data file you have been working on.

6) Now we will bring your external data into ArcView.

7) Go to the project manager window. Then click on the table icon, and in the upper right corner, click Add

The Project Manager window


8) The Add Table window will appear.

Load your external Data


Navigate to the directory with your external data in it. Make sure that on the lower left of the Add Table window, that the List of File Type matches the type of your external data, either .dbf, .txt, or INFO. When you have selected your external data on the right side of the window, click Ok

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




9) Your external data will appear in a table window in ArcView. Click on the column attribute that you plan to join with your shapefile attribute.

Your external data


Select the attribute you will use to join the two


10) Now open the attribute table for the shapefile you are joining too and click on the matching column attribute (the one field in two have in common)

Select the matching attribute to join


11) With both column headings selected select Table -> Join

Join the two


12) Your external data table will disappear. It is now joined to your shapefile. Now in the attribute table of your shapefile you can scroll right and see your external table embedded within it. If you don't see it See Pitfall 2or See Pitfall 3.

Your final joined data, ready for analysis and so much more


13) It is now spatially referenced and can be used for spatial analysis. For example, See Recipe Making a choropleth map (3.x)
Pitfalls:
Authored by: Benjamin N. Sprague Modified: 9/11/03


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