Table Joins

Joining and relating tables allow you to combine spatial and non-spatial data into ArcGIS based upon a single field in common, or the ‘key’.

Joins and relates are essentially the same thing, with one pretty significant difference. Join actually joins the data within the attribute table of the joined layer, while relate keeps each table in their respective form but allows for the use to switch back a forth to examine the tables.

• Both methods allow for spatial query
• Both methods are temporary (live only in the MXD) unless further steps are taken to make the process permanent (i.e. Exporting the joined data)

Preparing Data for Joins and Relates

ArcGIS and Excel see the world differently. The main reason for this is Excel is software concerned with calculations - spreadsheets - and ArcGIS is a Database Management System (similar to MSAccess). Because of this major difference, things that ‘make sense’ to Excel do not make sense to ArcGIS.

Open your non-spatial data in Excel Check for merged cells. If they exist, you need to un-merge them ArcGIS expects each column to have a heading If not, ArcGIS will simply number all the fields F1, F2, F3... for Field 1, Field 2, Field 3... Use the ‘1’ row (starting with cell A1) in Excel to give each column a proper heading Remember - Arc HATES spaces and ANY character that isn’t the underscore _ Keep ‘em short and sweet. Remove vowels to make heading shorter Check existing column headers for illegal moves (spaces, bad characters...) Assign each column to proper kind of data type Highlight the column and right-click Choose ’ Format Cells’ from the resulting menu Assign the proper cell type Number, text, date, etc. When assigning numbers, assign the proper amount of decimal places Look at the key in your spatial data. ArcGIS cares deeply about the key being EXACTLY the same in both the source and the target. Use Excel’s data changing formulas to match cases BE SURE to copy the results and Paste Special -> As Text in another column and DELETE the column with the formulas! OpenOffice calc uses the same formulas/procedures Look for spaces in the start of the key’s word (use ‘Find and Replace’ as needed) and remove them Remember ” California” is not the same as “California”, nor is “California” the same as “california” or “Kalifornia” Hightlight the entire contents of your Excel sheet, right-click, and ‘Set Print Area’ The option to set print area may not be available in your version of Excel Save your Excel worksheet

Joining the Spatial to the Non-Spatial

Joining Based on Attribute

Joins based on attributes connect the spatial to the non-spatial using text. Matching attributes such as state, country, FIPS number, or other unique identifiers allows us to expand the non-spatial knowledge of our data without fully recreating spatial data.

Attribute joins can be temporary, existing only in your map, or permanent, by exporting the desired fields to a new feature class.

Either way, using the ‘join’ dialog box follows the same basic structure: designate the key, or common field, validate the join, correct errors that prevent a total join, then apply the join to the data.

1. Right click on the layer you wish to add the attributes to, choose Joins and Relates -> Join

The Join Dialog box:

2. What do you want to do to join this layer? :

   Join attributes from a table

3. Choose the field in this layer the join will be based on:

   Pick the key from the drop down of field names

4. Choose the table to join to this layer, or load the table from disk:

   Pick the table (Excel or other) or other feature class to add to the existing feature class

   • It’s possible to join the spatial and the attribute table of another spatial layer. You would want to do this if you have two spatial layers with different information you’d like to make one -- ie. a parcel layer with parcel id’s and owner’s names with a parcel layer from a different source that includes parcel id and owner contact information.
   • You can toggle other feature classes with the Show the attribute tables of layers in this list check box
5. Choose the field in the table to base the join on:

   Find the other half of the key

   • Remember, the field names of the keys do not need to be identical, just the information contained in that key
6. Join Options:

   Pick either

   1. Keep all Records, in which the join will not eliminate the unmatched values from the table. They will appear at the bottom of the list and NULL will be in the records associated with the join layer; or
   2. Keep only matching records, which will eliminate the values in the joined table which have no match. No records with Null will appear at the bottom of the list.
7. Validate Join

   Check the join. Look at the output. Did the quantity of records that you expected to match actually match? What does the output tell you? Do you need to make changes in Excel and try to rejoin? Or did all go to plan?

8. OK When your join is as expected, click OK to complete the join

• About joining data This link will take you to the ArcGIS help menu

Joining Based on Spatial Location

Joins based on spatial location is similar to joins based on attribute for two spatial layers, except this method uses not text in the attribute table, but spatial location on a map, ie. all earthquakes inside California (or, more specifically, the polygon that is the California feature). 

Right click on the layer you wish to add the attributes to, choose Joins and Relates -> Join The Join Dialog box: What do you want to do to join this layer? Join data from another layer based on spatial location You are joining: Autofilled geometry types Based upon your geometry’s join relationship, these option will change. How do you want the attributes to be summarized? Choose the summary statistics you’d like to see post-join The result of the join will be saved to a new layer. Specify output shapefile or feature class for this new layer: Give your ouput a name and place Do not rely on the default name/place. BE SURE to rename and re-locate your feature class.