Generating Reports from the Utility Network

[MikeMillerGIS]

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.

FeatureServiceStats.zip

[AlessandroValra]

Thanks Mike,

this is very interesting.

I tried to extend same idea to Associations, but layer with ID: 500001 is not listed as valid Layer (because is a system Layer).

Do you think that is possible to do?
This can make possible to produce reports using non spatial data related through Associations.

Thanks...
Damiano

[JohnBendix]

I have a question is the build lookup meant specifically for a filegeodb that has the utility network in it?
Or can it be used to a enterprise utility network in sql?
Thanks John.

[MikeMillerGIS]

@AlessandroValra what error is reported when trying to use the association table.  

@JohnBendix this code requires services, it does not work on a local geodatabase.

[JohnBendix]

When I run the build_lookup.py I get this error

Traceback (most recent call last):
File "W:\UtiltyNetworkReport\build_lookup.py", line 78, in <module>
bsl = BuildSimpleLookup(
File "W:\UtiltyNetworkReport\build_lookup.py", line 13, in __init__
self.describe = arcpy.Describe(self.utility_network)
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\__init__.py", line 1275, in Describe
return gp.describe(value, data_type)
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py", line 384, in describe
self._gp.Describe(*gp_fixargs(args, True)))
OSError: "C:\temp\UtiltyNetworkReport\Electric_UtilityNetwork.geodatabase\main.UtilityNetwork\main.Network" does not exist

[AlessandroValra]

@JohnBendix try this type of string:

"C:\\DATA\\Utility Network\\Reports\\MyGDBTelco.gdb\\UtilityNetwork\\Network")

[MikeMillerGIS]

Let me test mobile gdb, I built this a while ago and might have supported mobile.  I am looking at fixing the hidden layers too.

[AlessandroValra]

@MikeMillerGIS  error is:

[2023-01-12 18:41:53] [ ERROR] --- An error occured on the query: If using SQL Server, Shape__Length does not work for stats, sorry, use measuredlength
[2023-01-12 18:41:53] [ ERROR] --- 'NoneType' object has no attribute 'query'

using:

sum_stats = {
    "500001": [
            dict(
                name="Associations_Type_Count",

or

sum_stats = {
    "associations": [
            dict(
                name="Associations_Type_Count",

and also Associations

[MikeMillerGIS]

I just updated the code to load the system layers.  You need to use the layer name, such as Associations.  I did not get a chance to test it, but the lookup should find the layers.

[DamianoMontrasio]

Hi Mike,

thanks for your fast response.

I will try your edits and then I will give you my feedback.

First, I introduced these rows after df = layer.query(...)

# All Columns to Lower Case
df.columns = df.columns.str.lower()

because I've found different responses for same fields Uppercase and Lowercase, for example "ASSETGROUP" and "assetgroup".

I don't know why.

Now I'm working for:

• Excel export as alternative of CSV
• Support also for simple query (listing of devices for example) and not only statistics.

Thanks a LOT!

Damiano

PS: sorry if my first response was with wrong user of my principal. Tis is my personal account.

[MikeMillerGIS]

I would place the code in a github repo if you want to work on it that way.  

[DamianoMontrasio]

I confirm that works on Associations!!

Thanks Mike!

You have inspired me!

Damiano