Skip navigation
All Places > Electric and Gas > Blog > 2019 > July
2019

By Tom Coolidge and Tom DeWitte

 

Today’s Collector and ArcGIS Enterprise provide new enhancements and capabilities. These enhancements include; improved user interface, better GPS antenna support, direct capture of barcode via the mobile device camera, and allow for a more streamlined workflow for field users.  In addition to those important enhancements, the enterprise geodatabase capability of attribute rules allows for the automatic decoding of the barcode and the derived barcode data to be automatically written to the appropriate attributes.  This automatic decoding and attribute population provides significant productivity gains for field users and allows for a simpler deployment pattern for administrators. In this blog we will take a deeper dive into how to configure and deploy the ArcGIS platform and collector to address the industry need of Tracking and Traceability.

 

For an introductory explanation of how the ArcGIS platform addresses Tracking and Traceabiliy, please read the first blog of this 2 blog series: Tracking & Traceability – Part 1

 

Like any good recipe for success, we need to know the required ingredients.  The Tracking and Traceability solution requires the following software:

  • Collector for ArcGIS
  • ArcGIS Enterprise 10.6.1 or higher

 

Additionally, we will need arcade scripts which provide the logic of how to decode the ASTM F2897 barcode 16-character string and use the derived data to automatically populate the appropriate attributes.

 

Though not required, most deployments also include a GPS Antenna to improve spatial accuracy.  

 

The Basic deployment steps

Deploying the ArcGIS Platform to meet the needs of Tracking and Traceability can be broken down into 5 steps.  These steps are:

  1. Preparing the enterprise geodatabase
  2. Creation of staging geodatabase layers
  3. Application of attribute rules
  4. Publication of staging geodatabase layers as a feature service
  5. Creation of web map for Collector

 

The overall data flow process for Tracking and Traceability is to have Collector post the field collected features directly to the staging geodatabase.  There is NO translation or conversion of the field collected data.  Once the field collected data is submitted to the staging geodatabase a GIS mapping technician can review the new features and append them into the enterprise geodatabase.

Preparing the Enterprise Geodatabase

The first step to setting up this workflow is ensuring your Enterprise Geodatabase has the required feature classes, feature class attributes and coded value domains to store the information collected in the field.

 

If you are starting a new enterprise geodatabase, it is recommended that you use the Esri provided pipe system data model called Utility and Pipeline Data Model (UPDM). The 2019 edition of UPDM includes everything needed to store the information collected in the field. You can download this data model with this link:

 UPDM 2019 Edition download

 

 If you have an existing enterprise geodatabase, then you need to make sure the asset feature classes have the correct attributes to store the field collected data. Examples of assets captured by field staff include, fittings, valves, and pipe segments. Here is a specific listing of the minimally required attributes:

 

Point Asset Featureclasses

Field Name

Field Definition

Coded Value Domain

barcode

Text(16)

 

manufacturer

Text(2)

Pipeline_ASTM_Manufacturer

manufacturerlotno

Long Integer

 

manufacturedate

Date

 

manufacturecomponent

Text(2)

Pipeline_ASTM_Manufacture Component

material

Text(2)

Pipeline_ASTM_Material

diameter

Double

Pipeline_Fitting_Diameter

diameter2

Double

Pipeline_Fitting_Diameter

wallthickness

Double

 

wallthickness2

Double

 

 

Line Asset Featureclasses

Field Name

Field Definition

Coded Value Domain

barcode

Text(16)

 

manufacturer

Text(2)

Pipeline_ASTM_Manufacturer

manufacturerlotno

Long Integer

 

manufacturedate

Date

 

manufacturecomponent

Text(2)

Pipeline_ASTM_Pipe_Manufacture Component

Material

Text(2)

Pipeline_ASTM_Material

nominaldiameter

Double

Pipeline_Pipe_Diameter

wallthickness

Double

Pipeline_Pipe_Wall Thickness

 

After your Enterprise Geodatabase is ready to accept the decoded barcode values and the appropriate ASTM F2897 coded value domains have been assigned, you are ready to create the staging geodatabase.

 

Creating the Staging GDB

This step involves setting up your staging geodatabase layers.  These layers should be a schema duplicate of the enterprise geodatabase asset layers. Being a schema duplicate will simplify the appending of data from the staging geodatabase to the enterprise geodatabase.

 

The simplest approach to setting up the staging geodatabase is to create schema duplicate feature classes in the enterprise geodatabase.  I recommend creating a new feature dataset to store these duplicate layers.  If using the UPDM 2019 edition data model the feature classes to duplicate are:

  • PipelineDevice
  • PipelineJunction
  • PipelineLine

To help keep the staging layers uniquely separate from the production layers I like to rename the layers as follows:

  • StagingDevice
  • StagingJunction
  • StagingLine

These duplicate layers should not have any features/records.

 

To properly support disconnected field capabilities, you should use the “Add GlobalID” tool to add a GlobalID field to every staging feature class.

 

Additionally, though not required, it is recommended that you enable “Editor Tracking” to allow all edits to have a date/time stamp and the ArcGIS platform user ID of who created and last updated the feature/record.

 

A final step not to be overlooked is to decide whether you want to include photos as part of the new construction data collection process.  It the answer is “yes” then remember to “Enable Attachments” for each of the layers you want to have field staff capturing photos.

 

With the staging geodatabase layers now created it is time for attribute rules.

 

Application of attribute rules

With ArcGIS Enterprise 10.6.1 the attribute rule capability has evolved to provide a robust automation capability for managing attributes. For Tracking and Traceability, attribute rules provide the ability to automatically read the barcode value, decode the barcode and automatically populate the derived attribute fields (manufacturer, manufacture lot #, manufacture component type, manufacture date, material, diameter, and wall thickness). When this capability is applied to the staging geodatabase layers, the auto-population occurs when Collector submits the new feature.  This means a connected mobile device running Collector to capture new construction will be able to see the decoded information while they are documenting the new assets in the field.

 

The following link provides the arcade attribute rule scripts and detailed documentation on how to apply them.

ASTM F2897 barcode decode attribute rules 

 

The way attribute rules work is to assign them to a single attribute field. This means the decoding of the barcode is broken out into 9 separate arcade scripts.  Here is a breakdown of how the arcade scripts are applied to the staging geodatabase layers. 

 

StagingDevice Featureclass

Attribute Fields

Arcade attribute rule script

manufacturer

Device_Manufacturer.txt

manufacturerlotno

Device_Manufacturelotno.txt

manufacturedate

Device_ManufactureDate.txt

manufacturecomponent

Device_ManufactureModel.txt

material

Device_Material.txt

diameter

Device_Diameter.txt

diameter2

Device_Diameter2.txt

wallthickness

Device_Wallthickness.txt

wallthickness2

Device_Wallthickness2.txt

  

StagingJunction Featureclass

Attribute Fields

Arcade attribute rule script

manufacturer

Junction_Manufacturer.txt

manufacturerlotno

Junction_Manufacturelotno.txt

manufacturedate

Junction_ManufactureDate.txt

manufacturecomponent

Junction_ManufactureModel.txt

material

Junction_Material.txt

diameter

Junction_Diameter.txt

diameter2

Junction_Diameter2.txt

wallthickness

Junction_Wallthickness.txt

wallthickness2

Junction_Wallthickness2.txt

 

StagingLine Featureclass

Attribute Fields

Arcade attribute rule script

manufacturer

Line_Manufacturer.txt

manufacturerlotno

Line_Manufacturelotno.txt

manufacturedate

Line_ManufactureDate.txt

manufacturecomponent

Line_ManufactureComponent.txt

material

Line_Material.txt

nominaldiameter

Line_NominalDiameter.txt

wallthickness

Line_Wallthickness.txt

 

Once the attribute rules are successfully applied to your enterprise geodatabase staging layers you are ready to publish the staging layers as a feature service.

 

Publication of staging geodatabase layers as a feature service

Publishing the staging layers from ArcGIS Pro is a very straight forward process. The steps are as follows:

  1. Create a new Map
  2. Add staging gdb layers to map
  3. Symbolize layers as desired
  4. Publish map as a feature service

After the map is created and the staging geodatabase layers are added to you map you will have a ArcGIS Pro map which looks like the following:

I find using ArcPro for defining the symbology to be easier and quicker than using the ArcGIS Enterprise Portal map viewer tools.  Additionally, I can use more advanced symbology such as the UPDM2019_Symbols style set that is included in the UPDM 2019 Edition download.  When the layers are symbolized as desired, remove the basemaps and prepare to publish.

To publish the staging layers as a feature service, use the sharing ribbons’ web layer – Publish Web Layer tool to create the feature service.

With the feature service now published your staging geodatabase layers are ready for the final step which is to create the web map for Collector.

 

Creation of web map for Collector

Creating a web map for Collector is the opportunity to fine tune the interface your field staff will use for documenting the new construction.  Items to think about when creating the web map are:

  • Scale Constraints of layers
  • Which data fields will be exposed to the field staff
    • Which fields will be exposed during editing
    • Which field will be exposed during viewing

Both the ArcGIS Enterprise portal map viewer or the ArcGIS Pro desktop tool can be used to accomplish this task.

 

When the web map is defined and saved you are now ready to take Collector to the field to being collecting your new gas pipe construction.

 

Summary

With the latest enhancements to Collector and the new attribute rule capability for enterprise geodatabases. Deploying the ArcGIS platform to address the needs of tracking and traceability is easier than ever. Five basic steps  are all that it takes to enable your field staff to efficiently capture new construction digitally and retire the time consuming and inefficient historical paper based process.

  1. Preparing the enterprise geodatabase
  2. Creation of staging geodatabase layers
  3. Application of attribute rules
  4. Publication of staging geodatabase layers as a feature service
  5. Creation of web map for Collector

 

PLEASE NOTE: The postings on this site are my own and don’t necessarily represent Esri’s position, strategies, or opinions

By Tom Coolidge and Tom DeWitte

Tracking and Traceability is now a well-established practice in the natural gas distribution industry supported by ArcGIS®.

 

ArcGIS mobile app advances over the last three years have helped adoption of Tracking and Traceability activity grow. Collector for ArcGIS has evolved to now include the ability to use a mobile device’s camera to read the ASTM F2897 barcode. Collector also now includes the capability to run arcade scripts in the pop-up window while the device is disconnected from the network.  Not to be overlooked, Esri also released a new enterprise geodatabase capability called attribute rules.

 

Those three new capabilities have enabled many gas utilities, and increasingly gas pipe installation contractors; to use Collector to capture the location, barcode, and other information about the newly-installed pipe and its related components. These new capabilities and lessons learned from the many organizations actively using Collector for the digital as-builting portion of the Tracking and Traceability workflow have resulted in a more efficient and streamlined process for performing these tasks.

 

The purpose of this blog is to give an overview of how the current version of Collector, when combined with an ArcGIS 10.7 or higher enterprise geodatabase, can result in a simpler and more efficient Tracking and Traceability workflow. A second blog article will follow with a detailed explanation of the new attribute rule arcade scripts which completely automate the decoding of the ASTM F2897 barcode and the automatic population of the derived attributes.

 

A quick review of Tracking and Traceability

PHMSA proposed rules in May of 2015 to 49 CFR part 192 to address the need for operators to better ‘track’ the details and location of assets after their delivery from the manufacturer or supplier.  The rule also speaks to the need for better ‘traceability’ of assets; meaning the ability to locate assets by material, size, manufacturer, model, or other attribute.

 

The ASTM F2897 standard, developed collaboratively by the natural gas industry and its leading suppliers, specifies a 16-digit alphanumeric barcode format that embodies identification of a pipeline component’s manufacturer, lot number, production date, model, material, diameter, and wall thickness.  This barcode standard is now a common piece of the manufacturer provided information for plastic pipe and its plastic components.  Additional efforts spearheaded by the Gas Technology Institute are currently underway to define a more advanced barcode standard which can be applied to both steel and plastic pipe and their components.  This barcode “thing” is not going away.  Just the opposite, it is going to expand significantly in the years to come.

 

Pattern Overview

The ArcGIS deployment pattern for Tracking and Traceability is comprised of four steps, as illustrated here:

 

 

Step 1: Digital as-builting

The recent improvements to Collector have made this process easier than it was just a few years ago.  The first enhancement was the revamping of the interface to simplify data entry. The second enhancement was to increase the certification of GPS vendors and their devices. Here is a link to the list of GPS receivers which can be used with Collector: https://doc.arcgis.com/en/collector/ipad/help/high-accuracy-prep.htm

The third enhancement is the native ability of Collector to use the mobile device’s camera to capture the ASTM F2897 barcode.

 

With these enhancements, field staff can go into the field and capture the as-built information of the new construction using a smart device running Collector. The smart device is Bluetooth-connected to a high precision GPS antenna.  The field staff use the high accuracy GPS antenna to capture the location of the newly installed assets. The collected location data is directly streamed into Collector as native ArcGIS features.  No translation or conversation is required.  The field staff then manually input into Collector a minimal amount of information, such as Installation Date, and installation method.  The field staff then uses the device’s camera to capture the barcode and automatically populate the BARCODE attribute of the GIS feature.  The BARCODE value contains information about the asset, such as size, material, manufacturer and manufacture date.  Once the BARCODE value is captured, the field staff no longer need to manually enter this information.

 

The recent enhancement to Collector supporting the ability to run arcade scripts in the pop-up window, provides the ability to immediately display the decoded data to the field staff even when the device is disconnected.

 

An additional capability of an Esri mobile app on a smart device or tablet is the ability to capture photos of the newly installed assets.  These photos are automatically associated to the GIS feature.

 

When the field staff have completed the collection of the newly installed assets, the GIS features are submitted to the staging geodatabase.

 

Step 2: Contractor/crew assessable storage

A fundamental challenge of Tracking and Traceability is how to correctly integrate high precision GPS geospatial data, with less accurate legacy geospatial data.  A key component to overcoming this challenge is the staging geodatabase.  A staging geodatabase can be either hosted in ArcGIS Online as hosted feature layers or stored on premise with a local ArcGIS Enterprise implementation. The key purpose of the staging geodatabase is to provide an easily accessible data repository for the field crews to submit their collected construction information too.  The staging geodatabase only holds the newly collected construction information.  The construction data sits in the staging geodatabase until a mapping professional using ArcGIS Desktop accesses and downloads it to the enterprise geodatabase.

 

With the new enterprise geodatabase capability of attribute rules, it is possible to have the captured barcode value automatically read and used to auto-populate the derived attributes manufacturer, lot number, production date, model, material, diameter, and wall thickness.  If the digital as-builting described in step 1 happens while the device is connected to the enterprise geodatabase, then Collector will automatically decode the barcode, auto-populate the derived attributes and display the decoded information immediately after the new/updated GIS feature is submitted by Collector. In the second blog, we will provide links to these arcade scripts and describe how to apply them to an enterprise geodatabase.

 

Step 3: Append to enterprise geodatabase

One of the time saving capabilities of ArcGIS Desktop is the ability to interact with data from both the staging geodatabase and the enterprise geodatabase at the same time.  This allows the mapping professional to easily select the staging geodatabase features and append them into the final enterprise geodatabase feature classes. 

 

If the staging geodatabase layers are stored in ArcGIS Online, the previously described attribute rule arcade scripts can be applied to enterprise geodatabase layers. 

 

NOTE: Attribute rules only work with ArcGIS Enterprise 10.7 or higher. Additionally, ArcGIS Pro is the only desktop tool to understand attribute rules.  If using ArcMap and a geometric network, it is important that the staging geodatabase layers be stored in an enterprise geodatabase and the attribute rules are applied to the staging geodatabase layers.

 

The standard arctoolbox geoprocessing append tool can be used to copy the newly collected GIS features from the staging geodatabase layers to the final enterprise geodatabase feature classes.

 

Step 4: Mappers connect digital as-built with gas system

With the new construction data now appended from the staging geodatabase into the enterprise geodatabase and the barcode value decoded, the mapping professional now needs to determine how to connect the high precision geospatial features with the less accurate geospatial features. The outcome of this process needs to honor two data requirements:

  • Connecting the new features with the legacy features to create a single topologically connected gas pipe system.
  • Preserving the high precision GPS collected geospatial coordinate data.

 

The recommended best practice for accomplishing this seemingly disparate set of requirements is for the enterprise geodatabase point features such as Meters, Excess Flow Valves, and Non-Controllable Fittings to have the following attributes added: SPATIALACCURACY, GPSX, GPSY, GPSZ.  Here is another example where attribute rules can streamline the population of these GPS fields.  If using ArcMap and the geometric network, then a configuration of Esri’s Attribute Assistant tool or ArcFM’s AutoUpdater capability can be used to automatically populate these fields.  This will preserve the original GPS location values, which can be used later to rubbersheet all features (legacy and GPS) to the more accurate GPS location preserved in the GPSX, GPSY, and GPSZ attributes.  With the GPS location preserved, the mapper can adjust the new construction features as required to connect to the legacy gas pipe system.

 

Business value of using ArcGIS platform

This approach to Tracking and Traceability provides an opportunity for the GIS department to once again show the greater gas organization that not only can the GIS Department provide a solution which addresses this new common industry practice, but it can do so in a manner that improves the operational efficiency of the gas organization.  This pattern improves the operational efficiency of the gas organization and their contractors as follows:

  • Using Collector to collect construction data improves location accuracy and attribute quality by eliminating translation to paper and interpretation of paper based information.
  • Bluetooth integration with high precision GPS antennas improves the speed at which data is collected.
  • Capturing the barcode value reduces the amount of information the field staff manually collects. Material, diameter, manufacturer, manufacture model, manufacture data, manufacture lot number are all automatically populated by the decoding of the barcode.
  • Digitally collected data is immediately available for GIS department to process into enterprise geodatabase. This eliminates the historical latency problem of the GIS department waiting for the inter office mail transmittal of the construction packet.
  • The GIS department mapping professional task of updating the as-built representation of the gas pipe system is simplified. The mapper is no longer manually transposing paper based red-line drawings, but instead appending field collected geospatial features.  This improves the speed at which a mapper can complete the task of updating the as-built representation of the gas pipe system.
  • Safety of field operations staff is improved by providing the new construction data in a timelier manner.

 

This deployment pattern not only provides the ability to improve the efficiency of the field data collection, it improves the productivity of the mapping professional, and provides new construction updates to locators and field operations staff in a timely manner.

 

Next blog

In our next blog, we will dig into how to configure and deploy the arcade scripts for this solution to Tracking and Traceability.

 

PLEASE NOTE: The postings on this site are my own and don’t necessarily represent Esri’s position, strategies, or opinions.

***This workflow works with etlsolutions v0.5.2***

Click Here to learn more about benefits and how to get started with the Data Translation Tools

 

We have had requests for additional documentation and samples at the UC. A zip pro package has been attached to the thread.

 

We want to bring data from one file geodatabase to another file geodatabase. This is also known as Extract, Transfer, and Load (ETL). Let me set the stage.

 

We have a file geodatabase or fgdb for short. Let's call this fgdb, our source.  Inside this geodatabase, we have a feature class named Cars.  We have three subtypes in this feature class: Red, Blue and Green. There are domains assigned to each of these subtypes. I want all my data in this feature class to shuttle over to my target file geodatabase. This target fgdb has a feature class called Trucks with different schema. Schema is a term that includes attributes, data types, domains, and other data management concepts. The Trucks feature class has three subtypes as well, but they are different than those found in Cars. They are One, Two, and Three rather than Red, Blue and Green.

 

There are three ways to translate data with the workbook. The first method does a straight field mapping without any domains. The second method uses a sheet as a lookup table for domains. Finally, the last method uses a hard coded value to ‘burn in’ values. I will cover each method in this blog.

 

 

 

The first tool in our Data Translation toolbox is Create Mapping Workbooks. 

  • Open the GP Tool Create Mapping Workbooks tool within Data Translation toolbox.
  • Point each one of your source feature classes to its corresponding target feature class.
  • Specify the output folder for your mapping workbooks.
  • (Optional) Check the box to Calculate feature count statistics to generate information on what fields have populated data.

 

 

 

Once the tool has run, you will see a Points folder that contains a mapping workbook called Cars and a mapping.xlsx file which will be used later when using the Load Data from Workbook tool. Let's go into how to populate information into these excel workbooks.

 

Method One: Straight Field Mapping

 

Step 1:

Open the Cars workbook and navigate to the Mapping sheet.  We have columns for targetField, sourceField, and fieldType which are all system derived. The columns, expression, sheet, sheetKeys, and sheetValue are used for methods two and three. This default workbook is configured to transfer data to your new file geodatabase without any translations.

 

 

 

What this means is the domains will be mapped using their codes. For example, Red will not be mapped as anything since there isn't a 0 code in the domain "Type" of our target feature layer. Blue will be mapped as One since they share the domain code of 1. Green will be mapped as Two since they share the domain code of 2.

 

Step 2:

Before we use the Load Data from Workbook tool to Extract, Transfer and Load our data, it's time to inspect our mapping.xlsx workbook.

 

Each row in this workbook directs to the tool to: set the source database, set the target database, and set the lookup workbook. This first row was created when we pointed our Cars feature class to our Trucks feature class during the Create Mapping Workbooks section at the beginning of this workflow.

 

 

Step 3:

Now that I have inspected everything, it's time to run Load Data from Workbook tool.

  • Open the Load Data from Workbook tool in Data Translation toolbox.
  • Specify the location of the mapping workbook created from Create Mapping Workbooks tool.
  • (Optional) Truncate the target geodatabase before loading.

 

 

 

When we run this tool, we return values of 0, One, and Two.

 

 

Method Two: Field Mapping with Lookup

 

Step 1:

Open the Cars workbook and navigate to the Mapping sheet. In this sheet, we will define what sheets and columns to use as a lookup table. Since I am not using the sourceField for a straight mapping, I will remove Type from the sourceField column In this sheet, the values I have entered are highlighted. In the sheet column, "Type", highlighted in yellow, is used by the Load Data from Workbook tool to locate the correct excel sheet when mapping to Type.  In the sheetKeys column, "Type", highlighted in yellow, is used by the Load Data from Workbook tool to locate the correct column(s) on the Type tab when mapping to Type. In the sheetValue column, "NewType", highlighted in orange, is used by the Load Data from Workbook tool to locate which column to use as a lookup table on the Type tab.

 

 

Navigating to the Type tab, I have highlighted the Type column in yellow to show the relationship to the user-defined column in keys on the mapping tab previously mentioned.  I have also highlighted the user-defined column and values in orange. I entered the domain values 1, 2, and 3 in column C to map to Red, Blue, and Green, respectively.  I have also included their domain descriptions in column D. What I have accomplished in this workbook is creating a lookup table for old domain values to new domain values. Where Red Car was using a domain value of 0 to describe Red, Truck One uses a domain value of 1 to describe One.

 

 

Repeat steps 2 and 3 above from Method One

 

Here is our final output.

 

 

 

Method Three: Hard Code Burn-in

 

Step 1.

Open the Cars workbook and navigate to the Mapping sheet. In this sheet, remove all the values you entered in Method 2. We will simply enter in a hard-coded value of "3" into the expression column. This will burn-in the value of 3 for Red, Blue, and Green.

 

 

Repeat steps 2 and 3 above from Method One

 

Here is our final output.