query minimum date using rest service

baohuachu3 — Thu, 16 Mar 2017 08:58:24 GMT

In the service there is field name "sysDate" with Date type.

Is there any way to select the record with minimum sysDate?

Re: query minimum date using rest service

RandyBurton — Fri, 10 Dec 2021 22:03:09 GMT

Here's my python code to query the REST API. In my case, I was querying for a survey date, so substitute it for "sysDate". In the query dictionary, the where clause should insure that the date field is not null, but it could be adjusted to search for a date after/before a specific date. Limit the output to 1 row with "resultRecordCount".

For additional help, see: Query (Feature Service/Layer)

import arcpy, urllib, urllib2, json, sys, time, datetime, collections
from datetime import datetime, timedelta

# URL, referrer and tokenurl may vary based on ArcGIS Online or your server setup
# Credentials and feature service information

username = "" # user name here
password = "" # password here

# Adjust URL as required
URL = "https://services.arcgis.com/<xxxx>/arcgis/rest/services/<layername>/FeatureServer/0/query"

referer = "http://www.arcgis.com/"
tokenurl = "https://www.arcgis.com/sharing/rest/generateToken"

# obtain a token
query_dict = { 'username': username, 'password': password, 'referer': referer }
query_string = urllib.urlencode(query_dict)
token = json.loads(urllib.urlopen(tokenurl + "?f=json", query_string).read())
if "token" not in token:
    print(token['error'])
    sys.exit(1)

# build query dictionary
query_dict = {
    "where" : "SurveyDate IS NOT NULL",
    "outFields" : "OBJECTID, Business, SurveyDate",
    "orderByFields" : "SurveyDate ASC",
    "returnGeometry" : "true",
    "resultRecordCount" : "1",
    "f": "json", "token": token['token'] }

    # to select all fields use:    "outFields" : "*",
    # to select individual fields use comma delimited list: "outFields" : "OBJECTID, SurveyDate",
    # a date certain: "where" : "SurveyDate >= DATE '2016-05-29 18:30:00'",
    # if you do not want geometry:  "returnGeometry" : "false",
    # resultRecordCount only applies if supportsPagination is true 
   
# results in json format
jsonResponse = urllib.urlopen(URL, urllib.urlencode(query_dict))
features = json.loads(jsonResponse.read(),
                      object_pairs_hook=collections.OrderedDict)[u'features']

# print json.dumps(features, indent=4, sort_keys=False) # formatted json

print 'ObjectID\tBusiness\tSurvey Date\tX-lon\tY-lat'

for feature in features:
    # AGO uses GMT/UTC, you may wish to convert to local time
    surveyTime = time.strftime('%c', time.localtime(feature['attributes']['SurveyDate']/1000))
    print str(feature['attributes']['OBJECTID']) + '\t' + feature['attributes']['Business'] + \
          '\t' + surveyTime,

    # if you want geometry; AGO returns web mercator, reproject if necessary
    # print "x: " + str(feature['geometry']['x'])+ "\ty: " + str(feature['geometry']['y'])
    # WGS 1984 : (4326) Lat/Lon  
    # WGS 1984 Web Mercator (auxiliary sphere) : (102100) or (3857)  
    ptGeometry = arcpy.PointGeometry(arcpy.Point(feature['geometry']['x'],feature['geometry']['y']),
                                     arcpy.SpatialReference(3857)).projectAs(arcpy.SpatialReference(4326))  
    print "\t" + str(ptGeometry.firstPoint.X) +"\t" + str(ptGeometry.firstPoint.Y)

print "\nCompleted"
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: query minimum date using rest service

baohuachu3 — Mon, 20 Mar 2017 15:21:14 GMT

Hello Randy,

I check the query object in ArcGIS Js API and get the idea:

query.orderByFields=["SystemDate "];

query.num=1; //Number of features to retrieve.

Then the queryTask will return the feature with minimum SystemDate.

Thank you so much for your help.

Have a nice day.

topic Re: query minimum date using rest service in ArcGIS REST APIs and Services Questions

query minimum date using rest service

Re: query minimum date using rest service

Re: query minimum date using rest service