Retrieving Updated/ Inserted/ Deleted Features in ArcGIS Utility Network Data

Introduction 

Managing and updating utility network data in Esri ArcGIS Portal Feature Services involves handling complex data structures and maintaining data integrity across different versions. When working with feature services, particularly in a utility network context, understanding the methods for detecting differences between data versions is crucial for efficient data management and operations. This tutorial will focus on retrieving updated, inserted, or deleted features in the utility network data, which is a critical aspect of maintaining an up-to-date and synchronized dataset across various systems and applications.

Each part of this tutorial will showcase a different approach to retrieving updated, inserted, and deleted features in the Esri Utility Network Data. 

There are two primary methods to retrieve the differences in data between versions for data:

1. Using the ChangeDetector Transformer:

• • It involves comparing the original version of the data with a revised version to determine what has changed.
  • The ChangeDetector transformer can help identify inserted, updated, or deleted features and has ports for each of the operations.

2. Setting Differences for Version Data to Read and Exposing fme_db_operation:

• • By setting the differences for version data to read, the reader only pulls the data that is different from your version compared to the DEFAULT version.
  • Exposing the fme_db_operation attribute allows users to know if a certain feature has been inserted, deleted, or updated.
  • Important Note: Currently, with this configuration, domains will not be resolved whatsoever. Additionally, all the date values are converted into UNIX Epoch Time values, specifically in milliseconds, you must use AttributeManager to convert values to seconds then use the DateTimeConverter transformer to convert values to   %Y%m%d%H%M%S Format. 

An alternative method to retrieve updated, inserted, and deleted features is to use a WHERE clause for selection on the reader, provided the date is known.

ChangeDetector Transformer Overview

The ChangeDetector transformer requires an exact match in schema definition to compare and detect updated, inserted, and deleted features. Both incoming datasets (original and revised data) must have identical schema definitions, including Attribute Names, Attribute Types, and Attribute Width, for the ChangeDetector transformer to properly and correctly detect changes between the two datasets. Even the slightest mismatch in schema definition can compromise the integrity of the ChangeDetector transformer, potentially cascading into catastrophic schema definition issues for the overall dataset integrity!

Due to this strict requirement for exact schema matching, the incoming data must be standardized before it reaches the ChangeDetector. While ArcSDE and ArcGIS Portal Feature Services enforce strict schema definitions, File Geodatabases do not. This discrepancy can lead to schema definition-related problems when detecting changes between File Geodatabases and ArcSDE or Portal Feature Services. It is crucial to note any changes made to the schema definition during the standardization process so that these changes can be rectified to match the destination schema definition!

This tutorial investigates workspace configuration for File Geodatabase and Portal Feature Service to effectively use the ChangeDetector transformer to retrieve updated, inserted, and deleted features. When comparing File Geodatabase with Portal Feature Service, the BulkAttributeRenamer transformer and StringPadder transformer will be used to ensure that the schema definitions match.

This approach is necessary because the naming conventions for attribute names may differ between File Geodatabase or ArcSDE and Portal Feature Services. By using BulkAttributeRenamer, you can standardize the attribute names to ensure a successful comparison and change detection process when working with different data sources.

For a deeper understanding of the ChangeDetector transformer, please consult this documentation. 

Requirements

• FME Form Workbench installed and licensed
• Esri ArcGIS Pro or ArcGIS Enterprise installed and licensed
• Basic understanding of Utility Network data structure and dataset, FME transformers, and FME and geodatabase
• Utility Network-configured ArcGIS Portal Feature Service item
• Complete the following FME & Esri ArcGIS Utility Network Tutorials:
  1. Reading Utility Network Data from ArcGIS Portal Feature Service and Writing to ArcGIS Portal Feature Service
  2. Reading Utility Network Data from ArcGIS Portal Feature Service and Writing to File Geodatabase

Sources

• Retrieving_UID_Part1.fmw contains the completed workspace for this tutorial. Dataset (path location) and Tables for Geodatabase Reader will need to be re-configured to make the workspace usable.
• Retrieving_UID_Part2.fmw contains the completed workspace for this tutorial. Portal URL, Credentials, Feature Service, Layers/Feature Types, and Branch Versioning for Portal Reader and Dataset (path location) and Tables for Geodatabase Reader will need to be re-configured to make the workspace usable.
• Retrieving_UID_Part3.fmw contains the completed workspace for this tutorial. Portal URL, Credentials, Feature Service, Layers/Feature Types, and Branch Versioning for Reader will need to be re-configured to make the workspace usable.

Step-by-Step Instructions

Part 1: File Geodb and File Geodb ChangeDetector

1. Add an Esri Geodatabase File Geodb Reader
In a blank workspace, add a Reader.

In the reader parameters, set the following: 
A. Set the Format as: Esri Geodatabase (File Geodb)
B. Specify file path location of the original (DEFAULT) version for the Dataset. 
C. Click on "Parameters...".

In the Esri Geodatabase (File Geodb) Parameters dialog: 
A. Click on the ellipsis next to Tables. 

2. Edit Geodatabase Parameter

In the Select Table dialog: 
A. Select the desired layers. 

• • For this tutorial, WaterJunction will be selected

B. Click on "OK".

Back in the parameters, disable the Resolve Subtypes. 
A. Uncheck Resolve Subtypes.

• Your final Geodatabase Parameter should look similar to this:

B. Click on “OK” two times to finish adding the reader.

3. Add an Esri Geodatabase File Geodb Reader
In workspace with the previously configured reader, add another Reader.

In the reader parameters, set the following: 
A. Set the Format as: Esri Geodatabase (File Geodb)
B. Specify file path location of the revised version for the Dataset. 
C. Click on "Parameters...".

In the Esri Geodatabase (File Geodb) Parameters dialog: 
A. Click on the ellipsis next to Tables. 

4. Edit Geodatabase Parameter

In the Select Table dialog: 
A. Select the same layers that were selected in step 2 for the original geodatabase. These should be the layers you want to compare between the original and revised versions.

• • For this tutorial, WaterJunction will be selected.

B. Click on "OK".

Back in the parameters, disable the Resolve Subtypes.
A. Uncheck Resolve Subtypes.

• Your final Geodatabase Parameter should look similar to this:

B. Click on “OK” two times to finish adding the reader.

5. Add a ChangeDetector for Each Layer

For each layer selected in steps 2 and 4, for this example, only one ChangeDetector is needed:
A. Connect the "Original" input to the corresponding layer from the original geodatabase (the first reader).
B. Connect the "Revised" input to the corresponding layer from the revised geodatabase (the second reader).

6. Edit ChangeDetector Parameters 

In the ChangeDetector Parameters dialog:
A. Set the Update Detection Key Attribute as: GLOBALID or globalid 

• • For this tutorial, globalid will be selected. 

B. Click on the ellipsis next to Selected Attributes.

In the Select ‘Selected Attributes’ Attributes dialog:
A. Click on "Select all". 
B. Click on "OK". 

Back in the parameters, expand and configure Check Geometry and Changed Output.
A. Set the Match Geometry as: 3D
B. Specify 0.001 for Vector Tolerance.
C. Expand Changed Output and specify ‘_changes’ for Detailed Change List Name.
D. Click on “OK”.

7. Save and Run the Workspace

Now, save and run the workspace. Use Feature Cache and Visual Preview on the output port of ChangeDetector to see if updated, inserted, and deleted features were properly detected. 

Part 2: Esri ArcGIS Portal Feature Service and File Geodb ChangeDetector 

For this tutorial, we will use an Esri ArcGIS Portal Feature Service as the original data source and a File Geodatabase will be used as the revised data source. However, you can reverse the configuration and use a File Geodatabase or an ArcSDE as the original data source, and an Esri ArcGIS Portal Feature Service as the revised data source.

This tutorial includes modifying mismatched attributes and discrepancies to improve the accuracy of the change detector. However, this tutorial will not include rectifying the attribute after ChangeDetector. 

However, it is important to note that all of the modified attributes must be rectified to match the schema of the desired writer, case sensitive with updating features. This involves using transformers like the BulkAttributeRenamer and AttributeManager to reconcile the attribute names.

1. Add an Esri ArcGIS Portal Feature Service Reader
In a blank workspace, add a Reader.

In the reader parameters, set the following: 
A. Set the Format as: Esri ArcGIS Portal Feature Service
B. Specify your organization's ArcGIS Portal URL for the Dataset.
C. Click on "Parameters...".

2. Edit Feature Service Parameter

In the Esri ArcGIS Portal Feature Service Parameters dialog:
A. Configure Feature Service and specify Layers.

• • For this tutorial, Water Device will be selected.

B. Enable Branch Versioning and select sde.DEFAULT.
C. Leave everything else as default.

• Your Feature Service Parameters should look similar to this:
  

D. Click on “OK” two times to finish adding the reader.

3. Add an Esri Geodatabase File Geodb Reader
In the same workspace, add another reader. 

In the reader parameters, set the following: 
A. Set the Format as: Esri Geodatabase (File Geodb)
B. Specify file path location of the revised version for the Dataset.
C. Click on "Parameters...".

In the Esri Geodatabase (File Geodb) Parameters dialog: 
A. Click on the ellipsis next to Tables. 

4. Edit Geodatabase Parameter

In the Select Table dialog: 
A. Select on the desired layers, but these layers should correspond to the layers you selected for step 2. 

• • For this tutorial, WaterDevices will be selected. 

B. Click on "OK".

Back in the parameters, disable the Resolve Subtypes. 
A. Uncheck Resolve Subtypes.

• Your final Geodatabase Parameter should look similar to this:

B. Click on “OK” two times to finish adding the reader.

5. Add BulkAttributeRenamer for Original and Revised Version

For each layer,
A. Connect the "Original" input to the corresponding layer from the original version from Portal Feature Service (the first reader).
B. Connect the "Revised" input to the corresponding layer from the revised version from geodatabase (the second reader).

6. Edit BulkAttributeRenamer Parameters

In the BulkAttributeRenamer Parameters dialog:
A. Set the Action to: Change Case
B. Set the Case Change Type to: lowercase

• Your final BulkAttributeRenamer Parameter should look similar to this:
  

C. Click on “OK”.

Repeat Step 6 for all the feature types you want to detect change for. 

7. Add an StringPadder 

• • File Geodatabase reader strips '%H:%M:%S' values if these values are not specified on ArcGIS. ArcSDE Geodatabase Datetime values are dependent on how the database is configured. To increase the accuracy of the ChangeDetector transformer, you must ensure that the datetime attributes in both the original and revised versions have the same format. 

A. Connect the following output from BulkAttributeRenamer corresponding to the File/ ArcSDE Geodatabase reader to StringPadder. 

8. Edit StringPadder Parameters 

In the StringPadder Parameters dialog:
A. Select the all the Datetime Attributes, this includes: 

• • creationdate
  • inservicedate 
  • installdate 
  • lastupdate
  • retireddate

B. Specify the Desired String Length to: 14
C. Specify the Pad Character to: 0
D. Set the Pad Empty String to: No
E. Click on “OK”.

9. Add a ChangeDetector for Each Feature Type

For each layer selected in step 6:
A. Connect the "Original" input to the corresponding layer from the original version of BulkAttributeRenamer (the first reader).
B. Connect the "Revised" input to the corresponding layer from the revised version of StringPadder (the second reader).

10. Edit ChangeDetector Parameters 

In the ChangeDetector Parameters dialog:
A. Set the Update Detection Key Attribute as: globalid
B. Click on the ellipsis next to Selected Attributes.

In the Select ‘Selected Attributes’ Attributes dialog:
A. Click on "Select all". 
B. Click on "OK". 

Back in the parameters, expand and configure Check Geometry and Changed Output
A. Set the Match Geometry as: 3D
B. Specify 0.001 for Vector Tolerance.
C. Expand Changed Output and specify ‘_changes’ for Detailed Change List Name 
D. Click on “OK”.

11. Save and Run the Workspace

Now, save and run the workspace. Use Feature Cache and Visual Preview on the output port of ChangeDetector to see if updated, inserted, and deleted features were properly detected. 

If you notice that the number of features for update is close to the total number of input features, it may indicate an issue with attribute matching. In this case, please refer to the troubleshooting section to rectify the attributes. This could involve using additional transformers like the BulkAttributeRenamer or StringPadder to ensure that the attribute names and values match exactly between the original and revised versions. By carefully reviewing and rectifying the attributes, you can improve the accuracy of the ChangeDetector and ensure that only the truly updated features are identified.

However, it is important to note that all of the modified attributes must be rectified to match the schema of the desired writer, case sensitive with updating features. This involves using transformers like the BulkAttributeRenamer and AttributeManager to reconcile the attribute names.

Part 3: Portal Feature Service Features to Read Difference 

When using the Portal Feature Service Reader configured to read differences for Version Data to Read, it's important to note that domains will not be resolved. This means that any domain-based attribute values will not be automatically translated to their corresponding domain descriptions.

All of the date and datetime datatype attributes columns are converted to UNIX Epoch in milliseconds when working with Portal Feature Service Reader configured to read differences for Version Data to Read. Portal Feature Service converts dates into unix, the difference is carried over into FME and not converted

This tutorial includes rectifying these values to the proper date for Esri ArcGIS.

1. Add an Esri ArcGIS Portal Feature Service Reader
In a blank workspace, add a Reader. 

In the reader parameters, set the following: 
A. Set the Format as: Esri ArcGIS Portal Feature Service
B. Specify your organization's ArcGIS Portal URL for the Dataset.  
C. Click on "Parameters...". 

2. Edit Feature Service Parameter

In the Esri ArcGIS Portal Feature Service Parameters dialog 
A. Configure Feature Service and specify Layers.  

• • If you do not know to to configure this, please consult the tutorial article 

B. Expand Enable Branch Versioning and select Your Revised version of Data for Version Name  
C. Set the Version Data to Read to Differences 
D. Expand Schema Attributes and select fme_db_operation for Additional Attributes to Expose
E. Click on "OK" and “OK” again to finish adding the reader. 

Your Feature Service Parameters should look similar to this:

3. Add an AttributeManager for the Layer

For each of the layers derived from the reader,
A. Connect the input of AttributeManager to the reader feature type.

4. Edit the AttributeManager Parameters

A. Filter Attributes for ‘Date’.  
B. For the following attributes, use the Arithmetic Editor 

Your AttributeManager Parameters should look similar to this:

C. For the following attributes, use conditional value definition

In the Conditional Value Definition dialog:

a. For the If Statement, for the Test, set the Operator as Attribute has a Value, then choose Arithmetic Editor and input the following values

b. Else <No Action> 

Your Conditional Value Definition should look similar to this: 

Your final AttributeManager Parameter should look similar to this:

5. Add a DateTimeConverter for the Layer

For each of the layers, add a DateTimeConverter transformer,
A. Connect the Output of AttributeManager to the DateTimeConverter.

6. Edit the DateTimeConverter Parameters

In the DateTimeConverter Parameter dialog:
A. Set Input Format as: %s$ (Epoch Time)
B. Set Output Format as: %Y%m%d%H%M%S (FME datetime)
C. Set Passthrough Nulls, Empties, or Missing as: Yes
D. Click on the ellipsis next to Datetime Attributes. 

7. Select Datetime Attributes 

In the Select ‘Datetime Attributes’ Items dialog: 
A. Select the following attributes for Datetime Attributes:

• • creationdate
  • lastupdate
  • installdate
  • inservicedate
  • Retireddate

B. Click on “OK”.

Back in the DateTimeConverter Parameter dialog:
A. Click on “OK”.

Your final workspace should look similar to this:

8. Save and Run the Workspace

Now, save and run the workspace. Use Feature Cache and Visual Preview on the output port of DateTimeConverter to see if updated, inserted, and deleted features were properly detected.

Part 4: WHERE Clause Method for Esri ArcGIS Portal Feature Service and File/ ArcSDE Geodb

For this tutorial, we will use the WHERE Clause on the reader to retrieve features. If you have knowledge of specific dates when features within the utility network data were updated, inserted, or deleted, this is an alternative method available to retrieve these changes. This method involves using a WHERE Clause to filter the data based on the known dates, allowing you to efficiently identify and extract the modified features. 

By leveraging the temporal information associated with the utility network data, you can streamline the process of retrieving the updated, inserted, or deleted values without the need for a comprehensive comparison between the original and revised data sources. 

It is still reasonable to use previous parts to further assure the accuracy and completeness of the retrieved data, as the WHERE Clause method relies on the reliability of the temporal information. Combining both approaches can provide a more robust and thorough analysis of the changes within the utility network data.

When using the WHERE Clause method to filter utility network data based on specific dates, it's recommended to use the lastupdate attribute. The lastupdate attribute is automatically updated whenever the attributes of a feature are modified or updated, making it a reliable choice for temporal filtering.

There are no completed workspaces available for Part 4, as configurations will be specific to your dataset.

Datetime Syntax

When using the WHERE Clause method to filter utility network data based on specific dates, it's essential to understand the syntax for selecting dates. The WHERE Clause supports both singular date comparisons and date range comparisons using the BETWEEN operator. You can specify just the date, or include hours, minutes, and seconds if needed. Here's a breakdown of the syntax:

Portal Feature Service 

• Singular Date Selection:
  • Syntax: DatetimeAttributeName = TIMESTAMP '%Y:%m:%d'
    • Note: You can optionally include hours, minutes, and seconds using the format '%Y:%m:%d %H:%M:%S'
  • Example: lastupdate = TIMESTAMP '2024-01-01 00:00:01'

• Date Range Selection:
  • Syntax: DatetimeAttributeName BETWEEN TIMESTAMP '%Y:%m:%d %H:%M:%S' AND TIMESTAMP '%Y:%m:%d %H:%M:%S'
    • Where First '%Y:%m:%d %H:%M:%S' Value is Earlier Time and
      Second '%Y:%m:%d %H:%M:%S' Value is Later Time
  • Example: lastupdate BETWEEN TIMESTAMP '2024-01-01 00:00:01' AND TIMESTAMP '2024-01-01 23:59:59'

File/ ArcSDE Geodatabase

• Singular Date Selection:
  • Syntax: DatetimeAttributeName = DATE '%Y:%m:%d'
    • Note: You can optionally include hours, minutes, and seconds using the format '%Y:%m:%d %H:%M:%S'
  • Example: lastupdate = DATE '2024-01-01 00:00:01'

• Date Range Selection:
  • Syntax: DatetimeAttributeName BETWEEN TIMESTAMP '%Y:%m:%d %H:%M:%S' AND TIMESTAMP '%Y:%m:%d %H:%M:%S'
    • Where First '%Y:%m:%d %H:%M:%S' Value is Earlier Time and
      Second '%Y:%m:%d %H:%M:%S' Value is Later Time
  • Example: lastupdate BETWEEN TIMESTAMP '2024-01-01 00:00:01' AND TIMESTAMP '2024-01-01 23:59:59'

WHERE Clause for Esri ArcGIS Portal Feature Service

1. Add an Esri ArcGIS Portal Feature Service Reader
In a blank workspace, add a Reader. 

In the reader parameters, set the following: 
A. Set the Format as: Esri ArcGIS Portal Feature Service
B. Specify your organization's ArcGIS Portal URL for the Dataset.  
C. Click on "Parameters...". 

2. Edit Feature Service Parameter

In the Esri ArcGIS Portal Feature Service Parameters dialog: 
A. Configure Feature Service and specify Layers.  

• • For this tutorial, Water Device and Water Line will be selected. 

B. Enable Branch Versioning and select desired version. 
C. Leave everything else as default. 

3. Edit Feature Type Parameter 
Click on the Reader Layers that you just configured,

In the Feature Type dialog: 
A. Set the WHERE Clause as a singular date for one of the Feature Type.

• For this tutorial, 22:27:09 May 24th 2024 will be selected for Water Line, the WHERE Clause would be:
  lastupdate = TIMESTAMP '2024-05-24 21:13:30'

B. Click on “OK”. 

Example of a Query Result Using WHERE Clause (AttributeRemover used to Simplify Output for Better Visualization):

C. Set the WHERE Clause as Date Range for the other Feature Type.

• 22:27:09 May 24th 2024 will be selected for Water Device, the WHERE Clause would be:
  lastupdate BETWEEN TIMESTAMP '2024-06-18 16:24:30' AND TIMESTAMP '2024-06-18 16:28:23'

D. Click on “OK”. 

Example of a Query Result Using WHERE Clause (AttributeRemover used to Simplify Output for Better Visualization):

4. Save and Run the Workspace

Now, save and run the workspace. Use Feature Cache and Visual Preview on the reader to see if updated, inserted, and deleted features were properly identified.

WHERE Clause for File Geodatabase 

1. Add an Esri Geodatabase File Geodb Reader
In a blank workspace, add a Reader. 

In the reader parameters, set the following: 
A. Set the Format as: Esri Geodatabase (File Geodb)
B. Specify file path location of the desired version for the Dataset.  
C. Click on "Parameters...". 

In the Esri Geodatabase (File Geodb) Parameters dialog: 
A. Click on the ellipsis next to Tables. 

2. Edit Geodatabase Parameter

In the Select Table dialog: 
A. Select on the desired layers.  

• For this tutorial, Water Device and Water Line will be selected. 

B. Click on "OK". 

Back in the parameters, disable the Resolve Domains and Resolve Subtypes. 
A. Uncheck Resolve Domains. 
B. Uncheck Resolve Subtypes. 

3. Edit Feature Type Parameter 

Click on the Reader Layers that you just configured and open the Feature Type dialog: 
A. Set the WHERE Clause as a singular date for one of the Feature Type.

• For this tutorial, 22:27:09 May 24th 2024 will be selected for UtilityNetwork/Water_Line, the WHERE Clause would be
  lastupdate = DATE '2024-05-27 22:27:09'

B. Click on “OK”. 

Example of a Query Result Using WHERE Clause (AttributeRemover used to Simplify Output for Better Visualization):

C. Set the WHERE Clause as Date Range for the other Feature Type.

• 22:27:09 May 24th 2024 will be selected for UtilityNetwork/Water_Device, the WHERE Clause would be"
  lastupdate BETWEEN TIMESTAMP '2024-05-27 20:56:26' AND TIMESTAMP '2024-05-27 20:59:11'

D. Click on “OK”. 

Example of a Query Result Using WHERE Clause (AttributeRemover used to Simplify Output for Better Visualization):

Troubleshooting 

Inaccurate Change Detection due to Exact Attribute Mismatch 

Solution: Rectifying Attributes or Unselecting conflicting Attributes
When the number of features coming out of the output port of the updated port is similar to the number of features coming into the input of the Change Detector, it is inaccurately identifying changes due to differences in the File Geodatabase and ArcSDE Schema and the Esri ArcGIS Portal Feature Service Schema. This occurs because of how attributes are resolved by the ArcGIS API or FME Reader when the ChangeDetector matches exact values.

To rectify this problem, use the AttributeManager transformer to reconcile the attributes causing the discrepancies. Configure the AttributeManager to map the attributes from the source schema to the target schema, ensuring that the attribute names and types match between the two schemas. This will allow the ChangeDetector to accurately compare the attributes and identify only the genuine changes.

To rectify this problem, select the visual preview for the Updated port and identify what has changed in the '_changes' attribute in the Feature Information Panel. You can view these changes if you have configured the Detailed Change List Name in the ChangeDetector Parameters. Reconcile the attributes listed in the '_changes' list.

Example of number of input features similar to the number of output features for the Updated port

‘_changes’ list in the Feature Information

Example of adding SUPPORTINGSUBNETWORKNAME and setting the value as Unknown using AttributeManager

Example of unselecting SUPPORTINGSUBNETWORKNAME within Selected Attributes in the ChangeDetector Parameters  

Other FME and Esri ArcGIS Errors 

If you encounter FME and Esri ArcGIS, this documentation is a comprehensive resource containing various solutions for troubleshooting.

Data Attribution

Water Distribution Utility Network Foundation data is copyright 2024 Esri. Licensed under Apache-2.0. Source: ArcGIS Solutions.