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.