Skip to main content

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.  

Screen recording of ArcGIS Solutions webpage. The search is used to locate the stormwater data management solution. Click on the solution for more information and select deploy now. The solution then loads and the details are reviewed in ArcGIS Online.

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.  

A series of 3 diagrams describing how Data Loading Tools can transform your data. From top to bottom (1) Map source to target classes, shows a series of stacked rectangles representing layers on the left that are yellow with arrows from these layers pointing to a stack of smaller rectangles representing layers in another feature. (2) Maps source fields to target fields, shows two tables with arrows connecting the column headers or field names from one table to the other. (3) Translate values and coded value domains, shows two tables with one column of data populated and arrows connecting these different values across the tables.

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.  

A screen capture from ArcGIS Pro showing the Create Data Loading Workspace tool with the source and target fields populated for every layer in the dataset.

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.  Screen recording of the windows file explorer showing the 3 folders output by the Create Data Loading workspace tool. Each folder is opened displaying their contents.

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.    

A diagram with two boxes, the left for the source data and the right for the target data. Each box contains a screen capture from ArcGIS Pro of the table of contents for the source and target data, respectively. There is an arrow connecting the StormDrainGravityMains feature from the source table to the contents of the Gravity subtype of the Pipe class in the target table of contents. Each box additionally contains a screen capture of the materials domains applied to the gravity main features. The matching values from each domain are connected with arrows for a total of 13 interconnected arrows.

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.