topic Query Feature Service and Rest API in ArcGIS REST APIs and Services Questions

Query Feature Service and Rest API

ViktoriaMagnis — Mon, 01 Jan 2018 16:50:09 GMT

I am trying to get the max(date) from a feature service using the rest api.

The where clause I am using is

SellDate =(select max(SellDate) from Buncombe_County_Property_Sales_Data)

But I keep getting 'where' parameter is invalid.

https://services6.arcgis.com/VLA0ImJ33zhtGEaP/ArcGIS/rest/services/Buncombe_County_Property_Sales_Data/FeatureServer/0/q…

Thanks

Vicki

Re: Query Feature Service and Rest API

JakeSkinner — Sat, 11 Dec 2021 13:05:14 GMT

Hi Vicki,

ArcGIS Online only allows certain standardized queries:

Standardized SQL functions in ArcGIS Online—ArcGIS Online Help | ArcGIS

Subqueries as a where clause, for example, POP_2010 = (SELECT min(POP_2010) FROM counties, are not supported

You can accomplish what you're looking for Python, though. Here is an example:

import urllib, urllib2, json, datetime, time

queryURL = 'https://services6.arcgis.com/VLA0ImJ33zhtGEaP/ArcGIS/rest/services/Buncombe_County_Property_Sales_Data/FeatureServer/0/query'

params = {'f': 'json', 'where': '1=1', 'outFields': '*', 'returnIdsOnly': 'true'}
req = urllib2.Request(queryURL, urllib.urlencode(params))
response = urllib2.urlopen(req)
ids = json.load(response)
minId = ids['objectIds'][0]
maxId = ids['objectIds'][-1]

x = minId

while x < maxId:
    y = x + 1000
    if y > maxId:
        y == maxId + 1
    where = 'FID >= ' + str(x) + ' AND FID < ' + str(y)
    params = {'f': 'json', 'where': where, 'outFields': '*'}
    req = urllib2.Request(queryURL, urllib.urlencode(params))
    response = urllib2.urlopen(req)
    data = json.load(response)
    
    dateList = []
    
    for data in data['features']:
        dateList.append(data['attributes']['SellDate'])
        
    x += 1000
    y += 1000
    
dateList.sort()
maxDate = str(dateList[-1])[0:10]

print(time.strftime(datetime.datetime.fromtimestamp(int(maxDate)).strftime('%c')))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Query Feature Service and Rest API

RandyBurton — Sat, 11 Dec 2021 13:05:16 GMT

Are you using AGOL or your own server/service? Are you using an HTML form, or are you using another method for your query? In the meantime, I would suggest the following:

"where" : "1=1",
"outFields" : "*",
"orderByFields" : "SellDate DESC",
"resultRecordCount" : "1"‍‍‍‍‍‍‍‍