Latest Contributions by 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). MikeMillerGIS_0-1646912055319.png 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.  config.png This script produces as CSV for each stat defined and will look like this: MikeMillerGIS_0-1646911957324.png   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.   MikeMillerGIS_1-1646911983433.png 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.  MikeMillerGIS_2-1646911983435.png This generates a file, like below for your data, that the FeatureServiceStats.py uses. MikeMillerGIS_3-1646911999396.png 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. MikeMillerGIS_1-1646911983433.png 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. ... View more