Generating Reports from the Utility Network
In this article, I wanted to discuss a different approach of extracting data to power reports for branched versioned datasets, like the Utility Network. We could leverage geoprocessing tools to query the data for the reports, but many of these tools pull down the records to the client and then preform the statistic. It is more efficient and faster to ask the database to do this work, so the statistics are preformed. To do this, we can use the OutStatistics parameter on the Query Rest Endpoint.
If you are not familiar with this parameter, scroll down to outStatistics on the Rest API Query doc. You can test using this capability, but browsing to the rest page one of your features services, selecting a layer and opening the query page.
Since you will want to run these queries regularly to feed reports, we developed the FeatureServiceStat python script that leverages the ArcGIS Python API to make the query and store the results as csv files. To set up this script, fill out the credentials to access the service, the item ID from the portal to the feature service, the output folder and a lookup json file to convert subtype and domain codes to descriptions(more on this later).
Next, review the sum_stats dictionary. The keys are the class names to query. In the example below, the Feature Service has a layer named ElectricDevice. Next, fill out the queries you want to preform. You can specify 1:N queries.
Details of each query definition
- name: The name of the resulting csv file
- where: This value is mapped to the where parameter
- grouped_by: These values are mapped to the groupByFieldsForStatistics parameter
- stats_info: These values are mapped to the outStatistics parameter
- bits(Optional): For electric, phase information is stored as a bitwise domain. If you want break the results by bit, such as the KVA on phase A, Phase B and Phase C, fill this dictionary out with the key representing the bit value and the description of bit description
- bit_field(Optional): The field with the bit values. Only required when bit is specified. This field must be in the group_by parameter.
This script produces as CSV for each stat defined and will look like this:
Looking at the XfrCount csv, you can see the count of transformers and the Total KVA per subnetwork and phase, as well as those values split out by the individual phase since we specified the bit information.
Now back to that code_lookup parameter. Since we use subtypes and coded value domains, the code is returned in the query. To make the results easier to interrupt, the script uses a code lookup json file to convert the codes to descriptions. To generate this file, use the build_lookup,py file and specify the path to the utility network and output file.
This generates a file, like below for your data, that the FeatureServiceStats.py uses.
Looking back at our XfrCount csv, you can see columns with the original phase and asset group values, but since we used the lookup file, new columns with their description have been added on the right.
This script is just an example to highlight these capabilities. We hope it gives you some ideas on how to utilize the outStatistics parameter in your organization or use this parameter in the workflows you are building.
Thanks
Mike
** Edit 1-12-2023
I updated the FeatureServiceStats.py module to load the hidden system layers.