ArcGIS Solutions for Utilities: Mapping the Path with Data Loading Tools

Explore a groundbreaking method for managing utility data with ArcGIS Solutions. Let's work together to unlock the potential of the stormwater data management solution. In Part 1, we will focus on the essential step of effortlessly loading data into the solution's feature layers using the Data Loading Tools, a hidden gem within ArcGIS Solutions. 

Navigating the intricacies of utility data management can be a formidable challenge. Within the vast utility management spectrum, ArcGIS Solutions emerges as a game-changer. It transforms ArcGIS into a dynamic system that empowers organizations to map, edit and collaborate on assets effortlessly. ArcGIS Solutions offers a seamless experience from field staff to office personnel, providing interactive system maps and dashboards. Spanning stormwater, communications, electric, gas and beyond, each utility has unique needs and the ArcGIS Solutions team has worked with industry experts to design data models and applications to help you manage and interact with your data. This series, employing stormwater in Victoria, B.C., as a use case, serves as a comprehensive guide, demonstrating how ArcGIS Solutions can be harnessed to overcome the challenges of managing your utility data. Though stormwater takes the spotlight, the principles explored are universally applicable, making this series a valuable resource for any utility seeking enhanced data management. Join us in part one of this journey as we explore the power of ArcGIS Solutions  starting with the the data-loading process. 

Getting Started with the Stormwater Data Management Solution:  

To kick things off, we focus on the critical first step of any ArcGIS Solution workflow: data loading. While ArcGIS Solutions simplifies configuring applications, loading your data into these solutions can feel daunting. Let's deploy the Stormwater Data Management solution and see what we are working with.  

*Note that this solution differs from the Stormwater Utility Network Foundation solution, which walks through implementing a Utility Network data model.  

Once a solution is deployed, we can see many components, from groups to an ArcGIS Pro package and several web applications. Don’t be overwhelmed by the content; once we finish our first step, many of these layers, maps and apps will be populated by a single hosted feature layer called StormwaterSystem.  

The StormwaterSystem feature layer contains several layers and tables. The ArcGIS solutions team gave us a data dictionary on the Configure Stormwater Data Management page to give us a better understanding of everything included. This dictionary shows that the solution contains 13 layers, 4 tables, 8 relationships and 52 domains; a lot is happening in this data. When the data dictionary is compared to the features downloaded from the City of Victoria Open Data Portal, there is an immediate apparent discrepancy. The City of Victoria has 15 layers for stormwater data compared to the StormwaterSystem’s 13. To see even more differences, we can compare the manhole feature in the solution to the City of Victoria Storm Drain manhole layer. Again, the difference is clear, with 52 fields in the solution layer and 36 in the Victoria data. The Victoria data also has 4 subtypes where the solution has none and many domains do not align with those in the StormwaterSystem layer. Examining these features tells us the schemas do not match, so we cannot directly append the data from the Victoria manhole layer into the StormwaterSystem manhole layer. Looking at these differences and imagining how to bring our data into the solution layer can start to feel overwhelming and might stop you in your tracks but have no fear! The ArcGIS Solutions team has created a toolbox that we can use in tandem with our work to solve this problem quickly. 

Data Loading Tools: 

Now, enter the hero of our story – the Data Loading Toolbox. These tools are the unsung champions that bridge the gap between your unique dataset and the streamlined structure of the Stormwater Data Management Solution. Rather than succumbing to the discrepancies, we can leverage these tools to harmonize the data seamlessly. The Data Loading Tools help you load data from a source to a target dataset with the ability to perform in-flight data transformations. The tools allow you to map your data in detail, from the class level through subtypes and fields, to mapping your coded value domains and transforming individual values.  

Let's delve into the intricacies of the Data Loading Tools, understanding how they help us transform the complex data-loading process into a transparent and manageable task. As I work in ArcGIS Pro 3.2, the data loading tools are included in the Data Management toolbox. If you are following along in a previous version of ArcGIS Pro, you must install the Data Loading toolbox for this workflow. 

Creating a Data Loading Workspace: 

Open the StormwaterDataManager Project Package in ArcGIS Pro, adding all your stormwater data and the StormwaterSystem feature layer.  Once all necessary data is added we can get to loading our data. The first step is to Create a Data Loading Workspace. Here, we set our source data to each feature in the City of Victoria and match it with the corresponding layer of the StormwaterSystems hosted feature layer from the solution as the source. In some cases, we can see that we do not have data that corresponds with a field from the solution layer or we have multiple features that correspond to only one in the solution, as we see with pressurized and gravity mains, both being pipes. In these cases, we can leave out targets we don't have data for and add the same target multiple times for each matching source layer. Once the source and target layers are all matched, run the tool.  

When the Create Data Loading Workspace tool has completed running, we will see a new folder called DataLoadingWorkspace that contains information on which features are matched and folders for domains, scripts and data mapping. There will be an excel file for every set of matched features and each subtype of those features, meaning our output has 18 excel files despite only adding 11 inputs to the tool. The Data Reference workbook is a good place to start as it contains links to all other excel files used in the data loading process.  

Mapping the Data: 

Now comes the bulk of the work to be done throughout the whole process: filling out the mapping sheets. Do not underestimate this task! It will take time but doing it correctly will save considerable time in the long run and make the data loading process as smooth as possible. Let’s look at the Storm Drain Gravity mains feature as an example. The image below shows a diagram of the gravity mains feature from the City of Victoria. Once the data is loaded, it will become a subtype of the pipe class in the solution feature. We can also see how one of the domains from the material field of that feature compares to the domain in the material field of the StormwaterSystem layer. Many changes need to be applied to the data and this is only one of several domains.    

As we are only focusing on the Gravity Mains layer, we can temporarily disable the other layers using the Enabled field in the data reference workbook. This can help save time as you can test only parts of your mapping without performing a full data load every time you make changes. The TargetDeleteQuery field can also help with the iterative process of loading data; by setting it to 1=1, you can delete all existing data from that feature before loading data again to avoid duplication.  

We can open the excel file for the Gravity Mains layer from the data reference workbook. In this file, the mapping sheet shows us the fields in the target layer that need to be matched and the information that has been auto-populated from the source layer. This sheet will be where most of the work is done in every workbook. The other sheets include target and source schema information (coloured yellow) and one sheet for each field with an assigned domain (coloured blue).  

To map our fields, we need to understand what each column is describing. I have summarized this for us below: 

• TargetField: All of the fields are in the target dataset. 
• FieldType: Field type for each field in the target dataset. 
• Expression: This is an expression parameter that will be used in the Calculate Field geoprocessing tool in ArcGIS Pro. You will need to use python functions written in the scripts file for multiline expressions. This field is automatically set if the target and source have the identical names. 
  • Lookup Columns: Used for mapping fields with domains. 
  • LookupSheet: The source domain sheet name. 
  • LookupKeys: The column of codes for the  source domain. 
  • LookupValue: The column name with the matching target coded values from the LookupSheet.  

We can start by matching the fields without domains. In the gravity mains feature we can match 7 fields directly and hard code additional 3 fields.

The hard coded values for the lined and liner method fields indicate that these features are unlined. In our source data we have a separate feature for lined gravity mains so these can automatically be assigned values for unlined pipes. We can also hard code the asset type as 3 for “gravity” as each feature is a gravity main.  

Now for each field that does contain a domain we have some more work on our hands. Continuing with our material field from above we will set the LookupSheet value to the sheet named Material, the LookupKeys to the field that holds the original code values also named Material and the LookupValue to the field with the corresponding values from the target domain: 

Using the values from the target domain the remaining blank fields must be matched with the appropriate new value. 

This same process is repeated for every domain in the feature until all data is matched.  

Loading the Data:

Once we are finished mapping our feature, we return to ArcGIS Pro and open the Load Data To Preview tool. This tool uses the data loading workspace we have just completed populating to load the data from our source feature to a preview geodatabase. This allows us to review our results before loading them into our final feature. Those working with a version of ArcGIS Pro previous to 3.2 will not have this tool and will need to use the Execute Data Load tool. If there are any issues after the preview stage, we can return to the data-loading workspace to adjust our field parameters.  

After running the tool, the City of Victoria features have been successfully loaded into the preview StormwaterSystem features. Everything is looking good so we can now run the Load Data Using Workspace tool to get our data into our hosted feature. With that, we have done it! The data has been loaded into the StormwaterSystem layer and is ready to be used in all the other components of the Stormwater Data Management solution.  

We have successfully managed the complex and often challenging process of loading utilities data into an ArcGIS Solution template. Despite the vast differences in data schemas, the Data Loading tools have proven to be a powerful ally. As we complete this first phase, we realize that the data-loading process is much more manageable than we initially thought. We can now confidently proceed to the next phase, knowing that our data is ready for use. 

In Part 2, we will focus on configuring maps and apps online. We are on the path to streamlined and efficient utility data management and we invite you to join us in this transformative journey with ArcGIS Solutions. Stay tuned for more insights and practical guidance in our upcoming installment!