ArcMap > Working with tables
About joining attribute tables |
|
|
Release 9.1
Last modified December 7, 2006 |
Print all topics in : "Working with tables" |
Most database design guidelines promote organizing your database into multiple tables—each focused on a specific topic—instead of one large table containing all the necessary fields. Having multiple tables prevents duplicating information in the database because you store the information only once in one table. When you need information that isn't in the current table, you can link the two tables together. In ArcMap, you can establish this kind of link by either joining or relating two tables together.
Typically, you'll join a table of data to a layer based on the value of a field that can be found in both tables. The name of the field does not have to be the same, but the data type has to be the same; you join numbers to numbers, strings to strings, and so on.
When editing joined data, you cannot edit the joined columns directly. However, you can directly edit the columns of the origin table. To edit the joined data, you must first add the joined tables or layers to ArcMap. You can then perform edits on this data separately. These changes will be reflected in the joined columns.
Suppose you obtain daily weather forecasts by county and generate weather maps based on this information. As long as the weather data is stored in a table in your database and shares a common field with your layer, you can join it to your geographic features and use any of the additional fields to symbolize, label, query, or analyze the layer's features.
One-to-one and many-to-one relationships
When you join tables in ArcMap, you establish a one-to-one or many-to-one relationship between the layer's attribute table and the table containing the information you wish to join. The example below illustrates a one-to-one relationship between each county and the weather data. In other words, there's one record of weather data for each county.
Here's an example of a many-to-one relationship. Suppose you have a layer where each polygon is classified according to its land use type. The layer's attribute table only stores a land use code; a separate table stores the full description of each land use type. Joining these two tables together establishes a many-to-one relationship because many records in the layer's attribute table join to the same record in the table of land use descriptions. You might then use the more descriptive text when generating the legend for your map.
Summarizing your data before joining it
Depending on how your data is organized, you may have to start by summarizing the data in your table before you join it to a layer. When you summarize a table, ArcMap creates a new table containing summary statistics derived from your table. You can create various summary statistics including count, average, sum, minimum, and maximum.
For example, suppose you want to create weather maps by state instead of county, but the weather information you have is organized by county. You could summarize the county data by state—for instance, finding the average rainfall for all counties within a state—then join the newly created output table to a state layer to create a weather map of rainfall by state.
You've seen how joining tables establishes a one-to-one or many-to-one relationship between a layer and a table. However, in some situations, you may want to establish a one-to-many or a many-to-many relationship between a layer and a table.
One-to-many and many-to-many relationships
An example of a one-to-many relationship is building occupancy. One building, such as a shopping center, may be occupied by many tenants. You may want to join a table of tenants to the attribute table of a layer representing buildings. However, if you perform a join, ArcMap will only find the first tenant belonging to each building, ignoring additional tenants. In this case, you should relate the tables instead of joining them.
Relating tables
Unlike joining tables, relating tables simply defines a relationship between two tables. The associated data isn't appended to the layer's attribute table like it is with a join. Instead, you can access the related data when you work with the layer's attributes. For example, if you select a building, you can find all the tenants that occupy that building. Similarly, if you select a tenant, you can find what building it resides in (or several buildings, in the case of a chain of stores in multiple shopping centers—a many-to-many relationship).
Relates defined in ArcMap are essentially the same as simple relationship classes defined in a geodatabase, except that they are saved with the map instead of in a geodatabase.
If your data is stored in a geodatabase and has relationship classes defined, you can use these directly without having to establish a relate in ArcMap. The relationship classes will automatically be available when you add a layer that participates in a relationship class to the map. Note that the many-to-many relationship is defined differently when your data is stored in a geodatabase. In general, if you have relationship classes defined in your geodatabase, you should use these instead of creating new ones in ArcMap.
Learn how to create a relationship class
Joining data spatially
When the layers on your map don't share a common attribute field, you can join them using a spatial join, which joins the attributes of two layers based on the location of the features in the layers. With a spatial join, you can:
- Find the closest feature to another feature.
- Find what's inside a feature.
- Find what intersects a feature.
Learn how to perform a spatial join
How are joins and relates saved in your map?
When you save a map containing joins and relates, ArcMap saves the definition of how the two attribute tables are linked rather than saving the linked data itself. The next time you open your map, ArcMap reestablishes the relationship (whether a join or relate) between the tables by reading the tables from the database. In this way, any changes to the source tables that have taken place since they were last viewed on the map, are automatically included and reflected on the map.
You can make a permanent disk copy of a layer with joined data simply by exporting the layer. To export the layer, right-click it in the table of contents, point to Data, and click Export Data. This creates a new feature class with all of the attributes, including the joined fields, written out.