Explore joins and relates
In the first part of this tutorial, you will learn the differences between joins and relates by exploring parks and campsite data in Wyoming. You will review configuration details and best scenarios for each.
Review the park data
You will address data inconsistency at the park level by joining the associated organization details using a many-to-one join.
- Download the Wyoming.zip file.
- Unzip and extract the file contents to a folder of your choice.
- Expand the LearnlessonData folder and double-click Wyoming.aprx to open the project in ArcGIS Pro.
- Sign in to your ArcGIS organizational account.
If you don't have an organizational account, see options for software access.
- If necessary, activate the Explore Join and Relates map.
The project consists of three maps of the Wyoming study area, each built with the associated layers you will use to explore joins and relates in the study area.
The map includes the Wyoming study area of Yellowstone and Grand Teton National Parks.
You'll start exploring the data by reviewing the map's metadata.
A key aspect of working with ArcGIS is documenting the content and project items you create and use—your maps, projects, geoprocessing models, geodatabase datasets, and so on. Information that describes items is known as metadata. Metadata is saved with the item it describes—in the geodatabase for geodatabase items, in the project for project items, on the file system for file-based items, and so on. Once created, metadata is copied, moved, and deleted with the item when it's managed by ArcGIS.
- In the Contents pane, right-click Explore Join and Relates and choose Properties.
The Map Properties window appears.
You can also open the Map Properties window by double-clicking Explore Join and Relates in the Contents pane.
- In the Map Properties window, click the Metadata tab and review the metadata.
- Click OK.
- In the Catalog pane, expand the Databases folder.
If the Catalog pane is not visible, on the ribbon, on the View tab, in the Windows group, click Catalog Pane.
Data for this tutorial is organized in two geodatabases, the Wyoming Natural Assets.geodatabase for analysis and Wyoming Natural Assets_Publish.geodatabase for sharing your curated data as a web map.
The mobile geodatabases end with the .geodatabase extension, which is an implementation of an open source public domain SQLite database. This is a portable and efficient data exchange format that is interoperable and commonly used in mobile app development.
Additional advantages of this format are that it is supported cross-platform, is stored in a single file on disk, and is a full-featured relational database (RDBMS) allowing for querying and reporting workflows that support views and indexes.
See Mobile geodatabases to learn more about this format.
- In the Catalog pane, expand the Wyoming Natural Assets.geodatabase.
Because this SQLite geodatabase is a full-featured RDBMS, feature class names must include the primary database name to make them unique within the instance.
The word main is appended to all feature class and table names, where main is identified with the primary database in the SQLite instance.
- In the Contents pane, click the National Parks layer to select it.
- On the ribbon, click the Data tab, and in the Table group, click the Attribute Table button.
The Unit Code field is an important field you will use in future steps while exploring Yellowstone and Grand Teton National Parks.
- In the Contents pane, if necessary, expand Standalone Tables, right-click OrganizationsTable, and choose Open.
The OrganizationsTable table appears.
- Review the OrganizationsTable fields to familiarize yourself with the data and identify fields used to build table associations such as joins and relates.
- On the ribbon, click the Standalone Table tab, and in the Design group, click the Fields button.
The Fields view appears.
- In the Fields view, review the structure of the table.
This stand-alone table contains various organizational attributes, including specific attributes such as Organization type, URL address, and Jurisdiction. These attributes are important for managers at the parks and thus need to be appended at the park level. Another important field is OrgParentID, which you will use to perform a table join.
Each natural asset (park) is managed by a specific organization. To maintain data consistency, you will associate records from the National Parks layer with its specific organization to create a more complete picture of park assets and who owns them. To achieve this, you will perform a join between National Parks and the Organizations Table.
A join is database functionality that appends records in one layer or table with records in another layer or table based on a common field. The resulting joined information is stored in the layer’s properties and persists temporarily inside your map. Joins are based on a key field that must exist in both participating tables. The name of the field may vary, but the data type must be consistent, for example, numbers to numbers or strings to strings.
Add a join
Next, you will add a join.
- On the ribbon, on the Standalone Table tab, and in the Design group, click the Joins drop-down arrow and choose Add Join.
The Add Join window appears.
For the Input Table parameter, you will choose the layer on which the join will be performed. For Input Join Field, you will specify the input table field on which the join will be based. For Join Table, you will enter the table to be joined to the input layer. For Join Table Field, you will specify the field in the join table containing values join is based on.
- In the Add Join window, enter the following parameters:
- For Input Table, choose National Parks.
- For Input Join Field, choose ParentOrgID.
- For Join Table, choose OrganizationsTable.
- For Join Table Field, choose OrgParentID.
- Click Validate Join.
- Review the message, confirm that no errors are returned, and click Close.
- Click OK.
A join physically combines fields and records from the associated tables into a stable single table that can be used for display, query, and analysis.
- In the National Park attribute table, right-click the row number for the first record and click Pinned Pop-up.
A pop-up appears for the record.
- Review the pinned pop-up pane and identify additional OrganizationsTable fields that have been appended to the National Parks table.
You can pin a pop-up for an individual feature to the screen so it does not disappear when you select a different feature. It can remain pinned until you close the pop-up. This is helpful if you want to compare pop-ups side by side.
- When you have finished, close the pop-up pane.
- Close the National Parks table, OrganizationsTable table, and the Fields view.
- On the Quick Access Toolbar, click Save to save the project.
Create a relate
Park staff and visitors are concerned with knowing static details such as the name and the exact location of campsites within a park boundary. In addition, they often need to know more dynamic details such as check-in and check-out times, site access and condition and pet regulations, which are managed in separate stand-alone tables. Dynamic site-specific data such as this calls for a more complex data management approach because the data needs to reflect the seasonal changes and evolving campsites conditions.
One way to address this is to implement dynamic data associations such as one-to-many relates. In this way, it is possible to ensure that a campsite feature can be queried and that all static and dynamic associated attributes are available in your map. Related data is never joined (statically) to the associated campsite, but rather queried from the source table when requested.
- If necessary, activate the Explore Join and Relates map.
- On the ribbon, on the Map tab, in the Navigate group, click the Bookmarks drop-down arrow, and choose Slough Creek Campground.
Your map updates to display campsites located in Slough Creek Campground.
- If necessary, in the Contents pane, expand the Facilities and Campsites layers.
For Facilities, the brown pinpoint symbol represents a campground, which is a subtype of the Facilities layer, while the white and green circles represent the campsites associated and located within that campground.
When displaying feature layers in a map, you can identify attribute information about a particular feature by opening a pop-up. Use the Explore tool to click a feature and display a pop-up containing a list of field attributes.
- On the ribbon, on the Map tab, in the Navigate group, verify that the Explore tool is selected.
- On the map, click a campsite.
A pop-up pane displays more information about the campsite attributes.
- In the Pop-up pane, right-click the campsite description and choose Move to Pinned Pop-up.
- Review the pop-up.
- In the Contents pane, in the Standalone Tables group, right-click CampsiteAttributes and choose Open.
The CampsiteAttributes table appears.
- Review the CampsiteAttributes table. Consider sorting the table by the EntityID field.
Each unique EntityID is representative of multiple attributes (details) for that one campsite. For example, where EntityID is a value of 1, it represents associated attributes specific to only Campsite 1. The details for Campsite 2 and all others differ; each campsite appears to have multiple and varying attributes associated with it.
If you were to join campsite attributes to campsite features (points), only the first row of attributes would be joined to each campsite feature and additional rows of attributes would be lost. A solution is to use a relate to enable access to multiple attributes associated with a campsite feature.
A relate is a common RDBMS mechanism that allows for workflows to associate one or more tables to each other through a key field without performing an actual join between the participating tables. Tables remain separate and do not appear physically joined, but when a feature or record in one table is selected, records associated through the key field in the related table are selected and may be accessed and updated. It can be accessed through the selected features or records in your layer or table.
Relates support various types of cardinality, allowing them to be used in circumstances in which associated records may have a one-to-one, one-to-many, many-to-one, and many-to-many association between records.
In the section, you will use a one-to-many relationship between campsite features and campsite attributes, thus making it possible to select a single campsite and access its associated multiple attributes describing details for that campsite.
- Close the CampsiteAttributes table.
- In the Contents pane, click the Campsites layer.
- On the ribbon, on the Data tab, in the Relationship group, click the Relates drop-down arrow, and choose Add Relate.
The Add Relate window appears.
- In the Add Relate window, set the following parameters:
- For Layer Name or Table View, choose Campsites.
- For Input Relate Field, choose Campsite Id.
- For Relate Table, choose CampsiteAttributes.
- For Output Relate Field, choose EntityID.
- For Relate Name, type Campsite Attributes Relate.
- For Cardinality, verify One to many.
- Click OK.
Next, you will explore the relationship and associated records.
- On the ribbon, on the Map tab, in the Selection group, click the Select button.
- On the map, select any campsite in Slough Creek Campground.
The selected campsite highlights in cyan, the default selection color, which indicates the feature is selected.
- On the ribbon, click the Data tab, and in the Tables group, click Attribute Table.
The Campsites attribute table appears.
- In the Campsites attribute table, click the Show selected records button.
The table filters to display only the selected campsite feature attributes.
- At the top of the table, click the menu button, point to Related Data, and choose CampsitesAttributes.
The CampsitesAttributes table highlights all related records associated with the currently selected campsite feature.
The number of associated attributes related to your selected campsite appear at the bottom of the attribute table.
The selected campsite, Slough Creek site #3, has 21 related records describing details for the site.
A pop-up provides an additional method to view related records. A pop-up pane displays the attributes of the selected feature as well as all related records in a convenient manner.
- Click the Campsites table, right-click the highlighted record row number, and choose Pop-up.
- In the Pop-up window, expand Slough Creek site #3 and review the attributes.
In this example, Slough Creek site #3 has 21 related attributes.
- On your own, click and explore various related records in the Pop-up pane. Use the Explore tool to learn about additional campsites. When you have finished, close the Pop-up pane and any open attribute tables.
Relates exist and persist in the application only for as long as the map referencing the relevant layers is open. Once the map is closed, the relate is deleted. However, permanent relates can be created and persisted in a geodatabase and can be reused and shared among users of the geodatabase. You will explore relationship classes next.
- Close the Explore Join and Relates map and save the project.
In this first module, you identified and addressed incomplete data at the park level by performing a one-to-one join to match parks with their respective managing organizations. You also built a one-to-many relationship and associated campsites features to additional attributes describing details about each campsite.
Next, you will use relationships to understand the inconsistencies between facilities categorized using subtypes and campgrounds, and campsites where they are located.
Create a relationship class
In this section, you will focus on the relationship between campgrounds and campsites in Yellowstone and Grand Teton National Parks. You will identify primary issues such as empty campgrounds, and campsites without a campground reference point. Next, you will create a relationship class to better define the relationship between campsites located in campgrounds and apply validation using relationship rules to ensure data integrity during updates and edits.
Review an existing relationship class
You will investigate a common issue in Grand Teton National Park near Jenny Lake using attachments.
- If necessary, activate the Explore Relationship Classes map.
- On the ribbon, on the Map tab, in the Navigate group, click Bookmarks, and choose the Jenny Lake Campground bookmark.
The map displays campsites located within the Jenny Lake Campground area.
There are no campsite facilities in the Jenny Lake Campground area. This campground is lacking a reference point and you cannot determine which agency manages the campsites.
- In the Contents pane, investigate the Facilities layer, then examine the map.
You can try to gather more information from the campsite’s attributes.
- On the ribbon, on the Map tab, in the Selection group, click Select By Attributes.
The Select By Attributes window appears.
- In the Select By Attributes window, enter the following:
- For Input Rows, choose Campsites.
- For Selection type, verify New selection is selected.
- For Expression, build the expression Where Campsite Name is equal to 1HB.
- Click the Verify SQL expression is valid button and click Apply.
- In the Select By Attributes window, click OK.
The campsite 1HB is selected on the map.
- Use the Explore tool to click the selected feature and display the pop-up.
The pop-up does not contain information to indicate in which campground the campsite is located.
- In the Pop-up, scroll down and review the campground pictures added as attribute attachments.
The pictures displayed in the Pop-up pane are attachments stored internally in the geodatabase in a separate attachment table that maintains a relationship class to the target dataset.
- Click the right arrow next to the first attached picture to display the second associated picture.
The sign in one of the images reads Jenny Lake Campground. This indicates that the campsites in your map are part of this campground.
Attachments provide a way to associate nongeographic data, such as picture files, HTML documents, reports, and so on, with your geographic information. To learn more, read An overview of the Attachments toolset.
Since the campsites at Jenny Lake do not have an associated campground facility point, this is the third data validation issue that merits attention and a solution.
- Close the pop-up.
- On the Map tab, in the Selection group, click Clear.
Address data inconsistencies
To maintain data validity, you will build a relationship class between campsites and facility campgrounds and apply relationship rules that will be enforced during editing, to ensure that campsites are always associated with a facility.
A relationship class is similar to a relate but differs in that a relationship class is maintained and persisted in a geodatabase. Each time the relationship is used, it dynamically reflects the newest information from the participating tables. In addition, a relationship class supports the establishment of relationship rules that refine the relationship and help maintain consistent and valid data.
A relationship class is built around the concept of an origin and destination table and how objects in the origin relate to the objects in the destination table.
The following are some key relationship class terms:
Origin and destination classes—Any action on an object in the origin table will affect the related objects in the destination table.
- Primary key and foreign key—Fields used to match objects from the origin table to the destination table.
- Relationship cardinality—How many objects in the origin are related to how many objects in the destination. Supports one-to-one, one-to-many, many-to-one, and many-to-many cardinality.
- Simple and composite relationships—In a simple relationship, related objects can exist independently of each other. In a composite relationship, destination objects cannot exist independent of origin objects. When the origin is deleted, all related destination records are also deleted.
Next, you will create a relationship class using the Create Relationship Class geoprocessing tool.
- At the top of ArcGIS Pro, in the Command Search, type create relationship class. In the search results, click Create Relationship Class (Data Management).
The Create Relationship Class tool pane appears.
- In the Create Relationship Class tool pane, enter the following parameters:
- For Origin Table, choose Facilities.
- For Destination Table, choose Campsites.
- For Output Relationship Class, click the Browse button.
- In the Output Relationship Class window, click Databases and Wyoming Natural Assets.geodatabase. For Name, type main.CampgroundsHaveCampsites and click Save.
- In the Create Relationship Class tool pane, continue entering the following parameters:
- For Relationship Type, choose Composite.
- For Forward Path Label, type Campsites.
- For Backward Path label, type Facilities.
- For Message Direction, choose Both directions.
- For Cardinality, choose One to many (1:M).
- For Origin Primary Key, choose FacilityID.
- For Origin Foreign Key, choose ParentFacilityID.
Read Relationship class properties for more information.
- Click Run.
Next, you will explore the relationship class.
- In the Catalog pane, expand Wyoming Natural Assets.geodatabase and locate the main.CampgroundsHaveCampsites relationship class.
- In the Wyoming Natural Assets.geodatabase, right-click main.CampgroundsHaveCampsites and choose Properties.
The Relationship Class Properties window appears.
- In the Relationship Class Properties window, on the General tab, review the relationship properties.
Next, you will refine the relationship by updating and adding relationship rules.
Relationship rules allow you to enforce restrictive behavior to help maintain data integrity during editing and data entry.
- Click the Rules tab. Expand the window and explore the rules that you can set for your relationship class.
You will focus on the Campground subtype because you have identified that some campsites are not associated with a specific campground.
- For the row where Subtype is Campground, check the Enabled column.
This will signal the creation of a relationship rule.
Next, you will set the rule Min and Max cardinalities for origin and destination features.
- For the Campground rule, enter the following:
- For the origin Min, type 1.
- For the origin first Max, type 1.
- For the destination Min, type 1.
- For the destination Max, type 100.
Setting this cardinality specifies that a single campground representing the origin (1 and only 1) may have between 1 and up to a maximum of 100 campsites (destinations) associated with it.
- Click OK.
The Relationship Class Properties window closes and the rule is added to the relationship class.
This rule enforces that each campsite must be associated with one campground and that each campground must have at least one and no more than 100 campsites associated with it. If the rule is broken during editing, the edit will be flagged as invalid by a warning message and saving the edit will not be allowed.
- Save the project.
In this section, you used a relationship class and a relationship rule to address potential data inconsistencies between campsites and the campground subtype in the facilities feature class. By defining the rule, you can maintain integrity between these two feature classes and enforce data quality.
Next, you will explore using rules during a data update and editing workflow.
Apply relationship class rules in action
Now that you have identified the campground–campsite problem with your data and built a relationship class rule to identify and prevent this from occurring, you will test the rule and add the missing campground feature.
Locate campsites with no campgrounds
You will add a campground point feature near Jenny Lake and thus ensure all campsites are correctly associated with a campground.
- Verify that the 1HB campsite is selected.
If the feature is not selected, use the Select By Attributes tool with the following expression: Where Campsite Name is equal to 1HB.
- On the ribbon, on the Map tab, in the Selection group, click the Attributes button.
The Attributes pane appears. On the Selection tab, a notification message warns that this campsite is not associated with a campground.
Notification messages are helpful to users not familiar with a dataset, making them aware of data validation and integrity checks.
Next, you will add a campground point for Jenny Lake Campground and associate campsites with the new campground. To meet the requirements of the relationship class that you created, you need to ensure that the origin primary key, (Facility Id), of the campground you add is identical to the destination foreign key (Parent Facility Id) of the associated campsites.
- The Parent Facility Id has a value of 247664.
You will use this value when creating the new Jenny Lake Campground feature.
- On the ribbon, on the Edit tab, in the Features group, click the Create button.
The Create Features pane appears.
- In the Create Features pane, under Facilities, click Campground, and confirm the Point tool is activated.
Using the Point tool, you can create a campground feature on the map.
- With the Point tool active, click a location within the Jenny Lake campsites to add a new campground.
- At the bottom of the Create Features pane, click the Attributes tab to view the Attributes pane.
The pane has updated to display attributes for the new campground feature.
The warning message identifies that a campground must have at least one associated campsite to be valid. Next, you will update the campground attributes and add the Parent Facility Id value of 247664 to associate the 1HB campsite to the campground.
- In the Attributes pane, set the following attribute values:
- For Facility Name, type Jenny lake Campground.
- For Facility Description, type New campground created.
- For Facility Id, type 247664.
- Verify that Facility subtype is set to Campground.
- At the bottom of the Attributes pane, ensure that Auto Apply is checked.
- On the ribbon, on the Edit tab, in the Manage Edits group, click Save. In the Save Edits window that appears, click Yes.
Now that you have set the Parent Facility Id, you will review the campsites in the Jenny Lake Campground area and confirm that the Parent Facility Id value is associated with them.
- Using the Select tool, click any campsites in the Jenny Lake Campground area.
You can see the Parent Facility Id value that associates this campground through the Facility id, to the Jenny Lake Campground.
- On the ribbon, in the Selection group, click Clear and save the project.
Adjust a campground location
Due to recent upgrades and planning changes Bridge Bay Campground will be permanently closed and a newly updated campsite will be established at a different location. Some Bridge Bay campsites are inactive and need to be permanently removed from the inventory, so you will address this situation.
- On the Map tab, in the Navigate group, click Bookmarks, and select the Bridge Bay Campground bookmark.
The map zooms in to the Bridge Bay Campground, and there is a single campsite in the area.
- Use the Select tool and click the campsite.
In the Attributes pane, the Type of use attribute is set to Permanently out of use, which means that it is no longer needed in the campsites table.
- Use the Select tool and select Bridge Bay Campground.
- On the ribbon, click the Edit tab, and in the Features group, click the Delete button.
- In the Delete window that appears, click Yes.
Both the Bridge Bay Campground and the Bridge Bay #1 campsite are deleted from the map.
Deleting Bridge Bay Campground has removed the Bridge Bay #1 campsite because of a cascade delete triggered by the composite relationship. This mechanism is useful because it ensures that data remains valid and unnecessary records are removed from data tables.
- On the ribbon, on the Edit tab, in the Manage Edits group, click Save. In the Save Edits window that appears, click Yes.
In this section, you tested the use of a relationship class to ensure the validity of features you added and updated during editing. Using the relationship, you were able to maintain data integrity and kept data tables free of unnecessary records.
In the final section, you will share your valid data for use with web maps used by park staff and with web apps available to the public. Your data is stored in a mobile geodatabase that can be shared and easily redistributed for these uses.
Share related data in a web map
To share your valid and curated data with stakeholders, you will publish a web map that can be used for verification during the quality assurance and quality control process and later in the creation of information products that can be shared with the public.
A workflow that involves multiple organizations and stakeholders maintaining various parts of the database highlights the importance of metadata to ensure that standards are met, and users are aware of naming conventions and ownership. Metadata represents a system of record for a data layer where lineage, conventions, codes, and physical characteristics of the data are retained, and as a dataset is copied or updated, the metadata follows suit.
- Activate the Share Final Map.
Next, you will create your item metadata.
The Share Final Map references data maintained in the Wyoming Natural Assets_ Publish.geodatabase. These datasets have been updated and adjusted to support publishing. Joined and related data maintained on layers by ArcGIS Pro is temporary and not persisted when publishing. For this reason, data joined between National Parks and the Organizations Table, as well as related data between Campsites and the CampsiteAttributes tables, have been exported as new feature classes to the Wyoming Natural Assets_ Publish.geodatabase.
- In the Catalog pane, expand Wyoming Natural Assets_ Publish.geodatabase, right-click main.CampsiteswithAttributes and choose Edit Metadata.
The main.CampsiteswithAttributes metadata pane appears.
- In the main.CampsiteswithAttributes metadata pane, update the following:
- For Title, type Campsites with Attributes.
- For Tags, type National Park Service, Relationship Class, Additional Attributes.
For Summary, type This feature class has been created from a one-to-many relate to maintain campsite information in Yellowstone and Grand Teton National Parks.
- For Description, type Use this feature class to access information about campsite features and associated attributes in Yellowstone and Grand Teton National Parks.
- For Credits, type Your Name.
- On the ribbon, on the Metadata tab, in the Manage Metadata group, click Save.
- Close the main.CampsiteswithAttributes metadata pane.
- Save the project.
Share as a web map
Finally, you will publish your curated data with the related records as a web map that can be shared with stakeholders and the public. The Share Final Map option has updated metadata associated with the map; you will use this metadata when publishing
- If necessary, activate the Share Final Map tab.
- In the Contents pane, right-click Share Final Map, and choose Properties.
- In the Properties pane, click the Metadata tab.
Review the map metadata and note the Tags, Summary, and Description.
- When you have finished reviewing the metadata, click OK.
- On the ribbon, on the Share tab in the Share As group, click Web Map.
The Share As Web Map pane appears.
- In the Share As Web Map pane, set the following parameters:
- For Name, type Wyoming Campsites Data Quality Web Map followed by your initials.
- For Summary, type Wyoming campsites cleaned data with relationship class.
- For Tags, accept the source map tags.
- For Select a Configuration, confirm that Copy all data: Exploratory is selected.
- For Location and Folder, type Wyoming Campsites to create a folder named Wyoming Campsites.
- Under Share with, check Everyone.
You cannot create two layers in an ArcGIS organization with the same name. Adding your initials to a layer name ensures that other people in your organization can also complete this tutorial. Once a layer has been created, you can rename it in the map to remove your initials, which will not affect the name of the underlying data layer.
- Click Analyze.
One error appears on the Messages tab related to unique numeric IDs for web sharing.
- Double-click the error.
The Map Properties window appears on the General tab.
- Check Allow assignment of unique numeric IDs for sharing web layers and click OK.
The error is resolved, indicated by a green check mark on the Messages tab.
- Click Share.
When sharing is complete, your web map will be available for stakeholders to verify campground and campsite details in ArcGIS Online. In addition, the web map and associated web layers can be included in web mapping apps that can be made available to national parks and campgrounds visitors.
- Close the Share as Web Map pane and save the project.
In this tutorial, in the role of a regional GIS specialist working for the National Park Service, you investigated data inconsistencies as reported by users for two popular parks in Wyoming. You identified three main issues with the data and used a join, a relate, and a relationship class with a relationship rule to verify data validity and implement a workflow that will prevent such inconsistencies from occurring again.
To build off of this tutorial and explore how to use a many-to-many relationships class, read the blog article Explore a many-to-many relationship class.
You performed a one-to-one join to incorporate organization information for each national park. You explored a one-to-many relate to promote quick access to additional attributes about campsites and to address decentralized data. In addition, you created a relationship class between campgrounds and campsites to prevent any loss of data and enforce the integrity of the relationship between these two features. You also built on the relationship class, and you used rules to help with the data cleaning and editing process for the Jenny Lake and Bridge Bay campgrounds. Finally, you shared your edits as a web map to give access to others to view your edits.
You can find more tutorials in the tutorial gallery.