Community

Table out_statistics query doesn't work using ArcGIS API for Python

685
3
03-05-2018 09:34 AM
MichaelJimenez
by
New Contributor
‎03-05-2018 09:34 AM

Hi, I'm using ArcGIS API for Python for querying a table using the following parameters:

query = maintenance.query(where='1=1', out_fields='structureid,last_maintenace', return_geometry=False, group_by_fields_for_statistics='structureid', out_statistics='[{"statisticType": "max","onStatisticField": "maintanancedate", "outStatisticFieldName": "last_maintenace"}]')

I'm always getting a error when trying to use the out_statistics parameter and there is no documentation for the syntax for this parameters. I'm assuming that it's the same syntax as the REST API where it work.

Help please!

Here is the complete code:

from arcgis import *
gis = GIS("http://...", "user", "password")

item = gis.content.get('123...')
maintenance = item.tables[0]
deployment = item.layers[0]

query = maintenance.query(where='1=1', out_fields='structureid,last_maintenace', return_geometry=False, group_by_fields_for_statistics='structureid', out_statistics='[{"statisticType": "max","onStatisticField": "maintanancedate", "outStatisticFieldName": "last_maintenace"}]')

0 Kudos
3 Replies
JohnYaist1
by Esri Contributor
Esri Contributor
‎03-05-2018 01:01 PM

Hi mjimenez@prvectorcontrol.org_prvectorcontrol‌ -

The syntax for the `out_statistics` parameter is the same as the REST API. What data type is the `structureid` field? What's the error message you receive?

0 Kudos
MichaelJimenez
by
New Contributor
‎03-05-2018 01:40 PM

Hi John Yaist , thanks for replying.

The structured: 

  • structureid ( type: esriFieldTypeString , alias: Barcode , editable: true , nullable: false , length: 10 )

The error:

KeyError                                  Traceback (most recent call last)<ipython-input-213-f44a68d57969> in <module>()      2                           out_fields='structureid',      3                           group_by_fields_for_statistics='structureid',----> 4                           out_statistics=[{"statisticType": "max","onStatisticField": "maintanancedate","outStatisticFieldName": "last_maintenace"}      5                           ])      6 query.df ~/anaconda3/lib/python3.6/site-packages/arcgis/features/layer.py in query(self, where, out_fields, time_filter, geometry_filter, return_geometry, return_count_only, return_ids_only, return_distinct_values, return_extent_only, group_by_fields_for_statistics, statistic_filter, result_offset, result_record_count, object_ids, distance, units, max_allowable_offset, out_sr, geometry_precision, gdb_version, order_by_fields, out_statistics, return_z, return_m, multipatch_option, quanitization_parameters, return_centroid, return_all_records, **kwargs)    453     454         params['returnCountOnly'] = True--> 455         record_count = self._query(url, params)    456         if 'maxRecordCount' in self.properties:    457             max_records = self.properties['maxRecordCount']~/anaconda3/lib/python3.6/site-packages/arcgis/features/layer.py in _query(self, url, params)   1009    1010         if  params['returnCountOnly']:-> 1011             return result['count']   1012         elif params['returnIdsOnly']:   1013             return result KeyError: 'count'

Any thoughts? Thanks in advance!

0 Kudos
RussellBrennan
by Esri Contributor
Esri Contributor
‎10-13-2023 05:02 AM

@MichaelJimenez you need to escape the quotes. I came across this looking for answers when I ran into the same issue so I assume you have moved on. Since the outstatistics parameter is JSON not just a simple string you need to escape the quotes in the outstatistics JSON that is passed in.

This is the code I wrote for my case. 

# Set variables before execution
lName = "TestLayer" #layer name from contents pane
statField = 'TextField255' #field to get statistics from
whereClause = '1=1' #optional where clause

import arcpy
from arcgis.gis import GIS
from arcgis.features import FeatureLayer
gis = GIS("home")

url = arcpy.Describe(lName).catalogPath

layer = FeatureLayer(url)

hClause = "Count({}) > 1".format(statField)

queryResult = layer.query(where=whereClause, groupByFieldsForStatistics=statField, havingClause=hClause, outStatistics="[{\"statisticType\":\"count\",\"onStatisticField\":\"%s\",\"outStatisticFieldName\":\"NonUniqueCount\"}]" % (statField))

queryResult.sdf[['TextField255', 'NonUniqueCount']]

Note that I was using this inside of Pro so I have a mix of arcpy and arcgis api for python in my example.

Hope this helps you or someone else reading this later.

0 Kudos