topic Re: groupByFieldsForStatistics in REST API URL? in ArcGIS Online Developers Questions

groupByFieldsForStatistics in REST API URL?

JayJohnsonWashoeCounty — Tue, 26 May 2020 20:12:40 GMT

I have an AGOL hosted view of Covid-19 case data that includes a zipcode polygon layer with a one to many join to a patient data table. This hosted view was created using the AGOL Analysis, Summarize Data, Join Features tool. The patient data table has one row for each patient and a field for their ZIPCode.

https://services.arcgis.com/iCGWaR7ZHc5saRIl/arcgis/rest/services/Join_PatientZip_to_ZipCodes_Centroid/FeatureServer

I'd like to tally how many cases are in each ZIPCode polygon and add this to a webmap. The data changes daily, so I'd like to do this as a view, not as a static layer.

I can successfully return summary numbers in the REST API Query as shown below. The question is, how do I translate this into a URL string I can add as a data source to a webmap, where each zip code polygon has a count of total cases?

It seems like I should be able to append a URL string to the REST endpoint given above (yes?). Something along the lines of:

&groupByFieldsForStatistics="zipcode_text"&outStatistics="somethingelse" Apparently I don't know how to construct the "somethingelse" portion of the string properly.

Is this the right approach?

Thanks,

Jay

Re: groupByFieldsForStatistics in REST API URL?

DaleCasale — Thu, 28 May 2020 14:05:44 GMT

If you execute a query in a service, the parameters you supplied will be created in the URL. Although it will have extra query parameters that are unused and you can weed these out. Btw, didn't you want to group by ZipCode?

https://services.arcgis.com/iCGWaR7ZHc5saRIl/ArcGIS/rest/services/Join_PatientZip_to_ZipCodes_Centroid/FeatureServer/0/query?where=OBJECTID%3E0&objectIds=&time=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&resultType=none&distance=0.0&units=esriSRUnit_Meter&returnGeodetic=false&outFields=&returnGeometry=true&featureEncoding=esriDefault&multipatchOption=xyFootprint&maxAllowableOffset=&geometryPrecision=&outSR=&datumTransformation=&applyVCSProjection=false&returnIdsOnly=false&returnUniqueIdsOnly=false&returnCountOnly=false&returnExtentOnly=false&returnQueryGeometry=false&returnDistinctValues=false&cacheHint=false&orderByFields=zipcode&groupByFieldsForStatistics=+ZIPCODE&outStatistics=+%5B%7B%0D%0A++++%22statisticType%22%3A+%22count%22%2C%0D%0A++++%22onStatisticField%22%3A+%22zipcode%22%2C%0D%0A++++%22outStatisticFieldName%22%3A+%22ZipCount%22%0D%0A++%7D%5D&having=&resultOffset=&resultRecordCount=&returnZ=false&returnM=false&returnExceededLimitFeatures=true&quantizationParameters=&sqlFormat=standard&f=html&token=

Re: groupByFieldsForStatistics in REST API URL?

SarahAmbrose — Thu, 28 May 2020 14:09:33 GMT

Hi Jay Johnson ,

If you perform the query in REST (just like you did) - you just need to look at the URL that's generated. For example, I did the same thing you did and this was the resulting URL:

https://services.arcgis.com/iCGWaR7ZHc5saRIl/ArcGIS/rest/services/Join_PatientZip_to_ZipCodes_Centroid/FeatureServer/0/query?where=&objectIds=&time=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&resultType=none&distance=0.0&units=esriSRUnit_Meter&returnGeodetic=false&outFields=&returnGeometry=true&featureEncoding=esriDefault&multipatchOption=xyFootprint&maxAllowableOffset=&geometryPrecision=&outSR=&datumTransformation=&applyVCSProjection=false&returnIdsOnly=false&returnUniqueIdsOnly=false&returnCountOnly=false&returnExtentOnly=false&returnQueryGeometry=false&returnDistinctValues=false&cacheHint=false&orderByFields=&groupByFieldsForStatistics=zipcode_text&outStatistics=%5B%7B%22statisticType%22%3A%22count%22%2C%0D%0A%22onStatisticField%22%3A%22Case__%22%2C%0D%0A%22outStatisticsFieldName%22%3A%22Total%22%7D%5D&having=&resultOffset=&resultRecordCount=&returnZ=false&returnM=false&returnExceededLimitFeatures=true&quantizationParameters=&sqlFormat=none&f=html&token=

So it's already done the work for you - and completed the query.

I will also sometimes use a URL decode/encode. If you copy the URL I pasted above above in this first box here URL Decode and Encode - Online and decode it, it will look like you expect:

https://services.arcgis.com/iCGWaR7ZHc5saRIl/ArcGIS/rest/services/Join_PatientZip_to_ZipCodes_Centroid/FeatureServer/0/query?where=&objectIds=&time=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&resultType=none&distance=0.0&units=esriSRUnit_Meter&returnGeodetic=false&outFields=&returnGeometry=true&featureEncoding=esriDefault&multipatchOption=xyFootprint&maxAllowableOffset=&geometryPrecision=&outSR=&datumTransformation=&applyVCSProjection=false&returnIdsOnly=false&returnUniqueIdsOnly=false&returnCountOnly=false&returnExtentOnly=false&returnQueryGeometry=false&returnDistinctValues=false&cacheHint=false&orderByFields=&groupByFieldsForStatistics=zipcode_text&outStatistics=[{"statisticType":"count",
"onStatisticField":"Case__",
"outStatisticsFieldName":"Total"}]&having=&resultOffset=&resultRecordCount=&returnZ=false&returnM=false&returnExceededLimitFeatures=true&quantizationParameters=&sqlFormat=none&f=html&token=

So you can do the opposite to figure out how to encode something!

I'm not 100% sure on the official "rules" if something needs to be encoded or not - but the above two strategies have worked for me

- Sarah Ambrose

Product Engineer, Esri

Re: groupByFieldsForStatistics in REST API URL?

JayJohnsonWashoeCounty — Thu, 28 May 2020 14:18:04 GMT

So the answer was RIGHT THERE all along. Thanks!

Re: groupByFieldsForStatistics in REST API URL?

JayJohnsonWashoeCounty — Thu, 28 May 2020 14:20:03 GMT

Sarah, Thanks for expanding on Dale's answer. And that URL decode/encode looks useful - thanks for the pointer!

Re: groupByFieldsForStatistics in REST API URL?

JayJohnsonWashoeCounty — Thu, 28 May 2020 16:45:28 GMT

Sarah, Should I be able to take that resultant URL and add it to an AGOL map (Add from web)? I'm getting the result I want in the Query interface, but when I try to add the URL that includes the query string into a web map, I'm not successful.

Do I need to be creating a layer view and making changes to the view definition instead of pursuing this URL approach?

Jay

Re: groupByFieldsForStatistics in REST API URL?

SarahAmbrose — Thu, 28 May 2020 17:25:01 GMT

Hey Jay Johnson ,

Sorry about that - focused on the technical part of your question (the query itself) and not the actual over-arching question. I do not expect that you would be able to add that to your map. When you summarize the data you now have a table - so there isn't really a geometry associated with it. You might be able to try joining the data to itself to calculate the count value in the tool (instead of the REST endpoint).

- Sarah