Updating ArcGIS Online Feature Services using UPSERT

Liz Sanderson
Liz Sanderson
  • Updated

FME Version

  • FME 2022.1

Introduction

In FME 2022.1, we have introduced the ability to perform UPSERT operations when writing out to ArcGIS Online Feature Services. With the ability to perform UPSERT operations when writing to a feature service, you are now able to update an existing row if a specific value already exists in a table, and insert a new row if the specified value already exists in a table.

In this step-by-step tutorial, you will create a new feature service on ArcGIS Online regarding Cell Signal data in and update the existing feature service using updated information using UPSERT. 
 

Requirements

  • FME 2022.1+
  • Read and write permissions to ArcGIS Online

 

Step-by-Step Instructions

Part 1: Create a New Feature Service  

1. Add CSV Writer
Open a blank workspace in FME Workbench and add a CSV reader to the canvas. Browse to the Original.csv dataset available for download from the Files section of this article. In the parameters, set the Feature Type Name(s) to be From File Name(s). Click OK twice to add the reader. 

1.png
 

2. Write a New Feature Service to ArcGIS Online
Add an ArcGIS Online Feature Service Writer to the canvas. Configure the parameters by clicking on “Parameters…” in the bottom left corner. First, select an ArcGIS Online connection. If you do need to create a new ArcGIS Online connection, you can create a new connection by selecting Add Web Connection and authenticating using your ArcGIS Online username and password.  
 

3.png

Next, navigate to the folder location where you would like to create this feature service. Give your Feature Service a name and set Yes for Create if Needed (in FME 2023.0+, set Feature Service Handling to "Create if Needed"). Select OK to close the dialogue. Click OK again to close the writer. 
 
2.png

Connect the reader and writer together. 
4.png

3. Write to ArcGIS Online
Select Run to write the data to ArcGIS Online. Log into ArcGIS Online to confirm that the data has been written out. Return to your workspace and drag to select both Reader and Writer then Left Click and select disable before moving on to the next part.  

5.png

 

Part 2: UPSERT Updates to the Existing ArcGIS Online Feature Service

1. Add an ArcGIS Online Feature Service Reader
Continuing in the same workspace from Part 1, add in an ArcGIS Online Feature Service Reader and navigate to the Feature Service created in Part 1 of this tutorial. Select the correct feature service layer then select OK to close the dialogue. Click OK again to close the Reader. 

2. Read in the CSV with Updates
Next, read in the CSV containing updates to the feature service.  Add another CSV reader to the canvas and browse to the Updates.CSV dataset. Configure the parameters by clicking on “Parameters…” in the bottom left corner. In the parameters set the Feature Type Names(s) to From File Name(s). select OK to close the dialogue. Click OK again to close the Reader. 
 
3. Detect Changes Using the ChangeDetector 
Place the ChangeDetector transformer on the canvas. The Change Detector detects any changes to the original dataset and assigns a database operation to control how the data gets written in. 
 
Connect the Original input port to the ArcGIS Online Reader and connect the CSV reader with updated data to the Revised port. Click the red cog wheel to open the parameters. In the parameters set the Update Detection Key Attributes to featureid. Set Attribute Matching Strategy to Match Selected Attributes then for Selected Attributes select:

7.png

Disable check geometry and open the Changed Output dialogue to set the Mode to be Upsert. select OK to close the dialogue. 

image.png

Run the change detector. All original features are output via the Deleted Port when no match can be found for it in the Revised set. The Upserted port holds all attributes that have been assigned the database operation UPSERT. 

8.png9.png

In your visual preview window, navigate to the display control panel. By enabling/disabling the ChangeDetector_Deleted attributes you can visually check for the Upserted attributes. 
 

4. Write to ArcGIS Online
Add another ArcGIS Online writer to the canvas and navigate to the feature service created in Part 1 of the tutorial. Set the Table Definition to Copy from Reader and then click OK. In the Select Feature Type dialogue, select the ArcGIS Online Feature Service Reader.  
 

11.png


Connect the ArcGIS Online Feature Service Writer to the Upserted and Deleted port of the Change detector.
13.png

Open the ArcGIS Online Feature Service writer parameters, set the Feature Operation to fme_db_operation, then set the Table Handling to Use Existing and Yes for Update Spatial Columns. 

2-1.png


5. Run the Workspace and Inspect Output
Run the workspace, a total of 2214 features should be written to the existing ArcGIS Feature Service; 1623 features Upserted and 591 features Deleted. Inspect your Feature Service on ArcGIS Online to confirm that the updates have been applied successfully. 

12.png


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. 

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.