Updating ArcGIS Online Feature Services using UPSERT

Introduction

The ArcGIS Online Feature Service writer provides full support for the upsert database action. Users can readily update existing features of, or insert new features into, a target ArcGIS Online feature service layer in one swift action using this writer.

In this two-part exercise, users will build a basic workflow for upserting detected feature changes to an ArcGIS Online feature service layer. In Part 1 of the exercise, users will create a new ArcGIS Online feature service and layer to act as a source dataset for the upsert action. Then, in Part 2, users will update this feature service layer with new features and values using the upsert action of the ArcGIS Online Feature Service writer.

Requirements

• FME Form 2023.2 at minimum
• Read and write permissions to Esri ArcGIS Online

Step-by-Step Instructions

Part 1: Create a New Feature Service

In Part 1 of this exercise, users will create the feature service and layer that will be used for the upsert action in Part 2.

1. Download Original.csv and Updates.csv

Download Original.csv and Updates.csv from the Files section near the bottom right of this article, and save both files locally.

2. Open FME Workbench  

Open FME Workbench, and start with a blank canvas. Once a blank canvas appears, add a CSV reader to the workspace.

In the Add Reader dialog that pops up, select the ellipsis next to the Dataset parameter and browse to Original.csv, downloaded in Step 1. Now select the Parameters button and set the Feature Type Name(s) to From File Name(s). 

Click OK on the CSV Parameters dialog and OK again on the Add Reader dialog to add the reader feature type to the canvas.

3. Add Global ID Values to the Source CSV Features

Add global ID values to the source CSV features. Since the upsert action of the ArcGIS Online Feature Service writer requires the use of global IDs, users must first ensure that the target feature service layer has a non-null global ID field value before writing with the upsert action.

Add a UniqueIdentifierGenerator to the canvas and connect its input port to the output port of the CSV reader feature type. Double-click on the UniqueIdentifierGenerator. In the Parameters dialog that opens, set the Unique Identifier Type to Globally Unique Identifier ({GUID}). Type in GlobalID for the Generated ID value. 

Once the UniqueIdentifierGenerator is run during a translation, a new attribute named GlobalID will appear on output features. This GlobalID attribute will contain the generated GUID values.

Select OK on the UniqueIdentifierGenerator Parameters dialog to return to the main canvas.

4. Add an ArcGIS Online Feature Service Writer

Add an ArcGIS Online Feature Service writer to the canvas. In the Add Writer dialog that opens, select the Parameters button.

In the Parameters dialog that opens, select an ArcGIS Online web connection for the ArcGIS Online Connection parameter. If a web connection has not yet been configured for ArcGIS Online, please follow the instructions in the FME Support Center article How to Create an ArcGIS Online Web Connection (OAuth 2.0) before continuing on with this exercise.

Alternatively, users can create a new web connection by choosing the drop-down to the right of the ArcGIS Online Connection parameter and selecting Add Web Connection. Choosing this option will prompt users to authenticate using valid ArcGIS Online credentials.

The underlying web service for this on-the-fly web connection is configured to use an ArcGIS Online registered app tied to Safe Software credentials, and so is subject to change or deletion without notice. Users are, therefore, encouraged to make use of their own ArcGIS Online registered app and web service in production environments.

Once the ArcGIS Online web connection has been configured and applied, choose the ellipsis next to the Folder parameter, and navigate to the target ArcGIS Online Content location for this feature service. Set the Feature Service Handling parameter to Create If Needed, and set the value of the Feature Service parameter to the desired feature service name. Leave all other parameters set to their default values. 

Select OK to close the Parameters dialog, and click OK again to close the Add Writer dialog. The Feature Type parameters dialog will pop open. Configure the parameters therein as follows:

• Layer Name: Original 
• Geometry: arcgisonline_point
• Feature Operation: Insert
• Feature Type Handling: Use Existing

Click OK on the Feature Type dialog to add the writer feature type to the canvas. Connect the output port of the UniqueIdentifierGenerator to the input port of the new writer feature type.

Right now, the new GlobalID attribute created by the UniqueIdentifierGenerator is not of the correct data type to be recognized as a global ID in ArcGIS Online. To adjust the GlobalID attribute’s data type, double-click on the newly added writer feature type. Select the User Attributes tab in the Feature Type dialog that opens. Choose Manual for Attribute Definition, and then find the GlobalID attribute in displayed attribute table. Select the Type cell for the GlobalID attribute, and then choose the dropdown menu that appears to change the attribute's type to GlobalID.

Now click OK on the Feature Type dialog to commit this data type change and return to the main canvas.

5. Write a New Feature Service to ArcGIS Online

Write a new feature service to ArcGIS Online by selecting the Run icon along the main Workbench toolbar. Once the translation is successful, open a web browser and sign into ArcGIS Online with the same credentials used to authenticate the ArcGIS Online web connection in Part 1 - Step 4, above. 

Verify that the feature service has been written correctly to the Content location specified while configuring the ArcGIS Online Feature Service writer. Open the written feature service layer in ArcGIS Map Viewer, and verify that 2,086 features are shown in the layer's attribute table.

Return to your FME workspace, and click and drag across the canvas to select the reader feature type, UniqueIdentifierGenerator, and writer feature type. With all three of these objects highlighted on the canvas, right-click on one of them and choose Disable Objects. Then, still with all three objects highlighted, right-click on one of them again and choose Disable Connections. Disabling these objects and their connections will ensure they don't run again when Run is selected from the main Workbench toolbar.

The feature service and layer needed for Part 2 of this exercise are now available in the target ArcGIS Online environment. Save the workspace locally, and leave it open to complete Part 2 of this exercise.

Part 2: Apply Updates to the New ArcGIS Online Feature Service using the Upsert Action

Part 2 of this exercise continues from where Part 1 left off. Users will build a basic change detection workflow to identify and apply updates to the newly created ArcGIS Online feature service layer, using the upsert action of the ArcGIS Online Feature Service writer.

1. Add an ArcGIS Online Feature Service Reader

Beginning with the final workspace built in Part 1, add an ArcGIS Online Feature Service reader to the canvas. In the Add Reader dialog that pops up, select the Parameters button. In the Parameters dialog that subsequently opens, select the same ArcGIS Online web connection applied in Part 1 - Step 4.

Next, select the ellipsis to the right of the Feature Service parameter, and navigate to the feature service written in Part 1 - Step 5. Now select the ellipsis next to the Layers parameter, and choose the feature service layer written in Part 1 - Step 5. Leave all other parameters set to their default values.

Select OK to close the Parameters dialog and OK again to close the Add Reader dialog.

The new ArcGIS Online Feature Service reader feature type will appear on the canvas. Place this reader feature type just below the now disabled workflow completed in Part 1 of this tutorial.

2. Add a CSV Reader to the Canvas

Add a CSV reader to the canvas. In the Add Reader dialog that pops up, point the Dataset parameter to the Updates.csv dataset that was downloaded in Part 1 - Step 1. Select the Parameters button, and in the dialog that opens set the Feature Type Names(s) to From File Name(s).

Select OK to close the Parameters dialog and OK again to close the Add Reader dialog. A new CSV reader feature type will be added to the canvas. Position this reader feature type just below the ArcGIS Online reader feature type added in Part 2 - Step 1 above.

3. Add a ChangeDetector

Add a ChangeDetector transformer to the canvas. Once the ChangeDetector has been added, position it to the right of the ArcGIS Online and CSV reader feature types.

The ChangeDetector is able to compare and identify the differences between the attribute values and/or geometry of two sets of input features. One set of features is fed to the ChangeDetector as the Original dataset, while the other set is input as the Revised dataset. The ChangeDetector will first attempt to match each feature in the Original dataset to a corresponding feature in the Revised dataset based on the value of the attribute specified for the transformer’s Update Detection Key Attribute parameter. If a match is found, the two matching features’ attributes and / or geometry are compared for differences. Features are output from the ChangeDetector via a series of ports which identify the database operation needed to capture the changes detected between the matched Original and Revised features. 

Connect the ChangeDetector’s Original input port to the ArcGIS Online reader feature type added in Part 2 - Step 1. Connect the ChangeDetector’s Revised input port to the ‘Updated’ CSV reader feature type added in Part 2 - Step 2.

Now, double-click on the ChangeDetector to open its Parameters dialog. In the Parameters dialog, set the Update Detection Key Attributes to featureid. Set the Attribute Matching Strategy to Match Selected Attributes, and then click the ellipsis to the right of the Selected Attributes parameter. In the dialog that pops open, select the following attributes:

• Code
• num_measures
• Power
• Quality
• recorded _tstamp
• StationID

Click OK on the Select 'Selected Attributes' Attributes dialog to return to the Parameters dialog.

Disable the Check Geometry option, and then expand the Changed Output parameter group. Set the Mode parameter to Upsert, and select the ellipsis to the right of Original Attributes to Preserve. In the dialog that opens, choose the GlobalID and OBJECTID attributes, and then select OK.

Leave all other parameter values set to their defaults. The ChangeDetector's Parameters dialog should be configured as shown in the following image:

Select OK to close the ChangeDetector Parameters dialog and return to the main canvas.

4. Run the Workspace to the ChangeDetector

Run the workspace to the ChangeDetector, with feature caching enabled. This partial run first requires that Enable Feature Caching is checked (enabled) in the Run menu. Users should also enable the Run menu's Enable Feature Counting option. The Run menu is accessible via the drop-down to the right of the Run icon in the main Workbench toolbar. 

Once Enable Feature Caching and Enable Feature Counting are both active, single-click the ChangeDetector on the main canvas and choose the Run To This icon that pops up. Choosing Run To This will run the active workflow up to and including the ChangeDetector.

Once the translation is complete, have a look at the three output ports displayed for the ChangeDetector. The Upserted and Deleted output ports should both have feature caches, showing a count of 1623 and 591 features, respectively. There should not be any features in the Unchanged output port. 

The features output via the Upserted port are those Original features that:

• matched a feature in the Revised dataset based on the featureid value; and
• had values which differed from the matched Revised feature for at least one of the attributes chosen for the ChangeDetector’s Selected Attributes parameter

Select the green magnifying glass on the Upserted port to view its feature cache in the dockable Visual Preview window. Notice that there is now a new attribute, fme_db_operation, whose value indicates the database operation that should be performed on the output feature at writing to capture the attribute differences identified between the Original and the Revised datasets. 

The features output via the Deleted port are those Original features that did not match a feature in the Revised dataset. These Original features will be deleted from the target ArcGIS Online Feature Service created in Part 1 of this exercise.

Any features output via the Unchanged port are those Original features that matched a feature in the Revised dataset, but did not show any value differences from the matched Revised feature, for any of Selected Attributes specified.

5. Configure an ArcGIS Online Feature Service Writer to Perform the Upsert

Configure an ArcGIS Online Feature Service writer to perform the upsert. First, add an ArcGIS Online Feature Service writer to the canvas. In the Add Writer dialog that opens, adjust the Layer Definition parameter to Copy From Reader, and then select the Parameters button.

In the Parameters dialog that opens, point the Connection parameter to the same web connection used in Part 1 - Step 4 of this exercise, and then select the ellipsis to the right of the Folder parameter. Navigate to the ArcGIS Online Content folder holding the feature service created in Part 1 of this exercise, and set the Feature Service Handling to Use Existing.

Select the ellipsis to the right of the Selected Feature Service parameter, and navigate to the feature service created in Part 1. Now expand the Advanced parameter group, and set Use Global IDs to Yes, leaving Preserve Global ID set to No.

The Parameters dialog should look like the following image:

Select OK at the bottom right of the Parameters dialog, and OK again in the Add Writer dialog. The Select Feature Type dialog will pop open. Here, users can select the reader that will supply the layer definition to the writer as part of the Copy From Reader operation selected above.

Choose the Original [<not set> [ARCGISONLINEFEATURES] ] option presented, and then select OK on the Select Feature Type dialog.

A new ArcGIS Online Feature Service writer feature type will appear on the canvas. Connect this feature type to the Upserted port of the Change detector.

Now, double-click on the new writer feature type. In the Feature Type dialog that opens, on the Parameters tab, configure the Layer parameters as follows:

• Feature Operation: Upsert
• Feature Type Handling: Use Existing
• Update Spatial Column(s): leave as default
• Upsert Match Column: GlobalID  (only present in FME Workbench 2024.0+)

Next, select the User Attributes tab, to the right of the Parameters tab. Set the Attribute Definition to Manual, and then select the OBJECTID attribute in the attributes table. With the OBJECTID attribute highlighted, select the - button just below the table to ensure this attribute is not written.

Select OK on the Feature Types dialog to return to the main canvas.

6. Configure a Second ArcGIS Online Feature Service Writer to Perform the Deletes

Configure a second ArcGIS Online Feature Service writer to perform the deletes. Upsert and delete actions cannot be performed by the same ArcGIS Online Feature Service writer; the upsert action uses a different ArcGIS API endpoint than the endpoint used for deletes, updates, and inserts.

Add another ArcGIS Online Feature Service writer to the canvas. Repeat Part 2 - Step 5 to configure this second writer, up to the point where the new writer feature type appears on the main canvas.

Connect the newly added writer feature type to the Deleted port of the ChangeDetector.

Now, double-click on the new writer feature type. In the Feature Type dialog that opens, on the Parameters tab, configure the Layer parameters as follows:

• Feature Operation: Delete
• Feature Type Handling: Use Existing

Select OK on the Feature Type dialog to return to the main canvas. Save the workspace locally.

7. Run the Workspace

Run the workspace by selecting the Run icon from the main Workbench toolbar. The translation will begin from the ChangeDetector, and carry on to the two ArcGIS Online Feature Service writers.

If users would instead like to run the entire Part 2 workflow from its beginning, first access Tools --> Purge Temporary Files to clear all feature caches from the workspace. Then, choose the Run icon from the main Workbench toolbar.

Once the translation completes successfully, access the newly updated feature service in ArcGIS Online to verify the upsert action was correctly performed. The target feature service should now have a total of 1623 features showing in its attribute table when the feature service layer is viewed in ArcGIS Map Viewer.

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.