topic Re: How to set up where clause for date when extracting data from a map service using python in Python Questions

How to set up where clause for date when extracting data from a map service using python

LoriEmerson_McCormack — Thu, 23 Jun 2022 16:30:11 GMT

I successfully used Python 3 to download data from a REST URL using the information from this link How To: Extract data from a map service using Python (esri.com).

I need help setting up my where clause when using a Date field.

Here is what worked when I used a non date field in my where clause:

params = {'where': 'RoadNumber < "00200"',
'geometryType': 'esriGeometryEnvelope',
'spatialRel': 'esriSpatialRelIntersects',
'relationParam': '',
'outFields': '*',
'returnGeometry': 'true',
'geometryPrecision':'',
'outSR': '',
'returnIdsOnly': 'false',
'returnCountOnly': 'false',
'orderByFields': '',
'groupByFieldsForStatistics': '',
'returnZ': 'false',
'returnM': 'false',
'returnDistinctValues': 'false',
'f': 'pjson',
'token': token
}

These don't work in my where clause for a date:

# Find today's date and calculate last 5 years
today = date.today()
thisyear = today.year
last5years = thisyear - 5
last5yeardate_string = str(last5years) + "-01-01 + 00:00:00"

params = {'where': 'CollisionDate >= last5yeardate_string',

params = {'where': 'CollisionDate >= (datetime.datetime.now() - dateutil.relativedelta(years=5))',

Re: How to set up where clause for date when extracting data from a map service using python

jcarlson — Thu, 23 Jun 2022 17:03:51 GMT

Depending on the service / DB being queried, the timestamp needs to be formatted a particular way. Try something like timestampe '<your timestamp here>' and see if that works.

Also, you may want to check out the ArcGIS Python API. It's purpose-built for interacting with GIS portals and servers, and I personally find it easier for querying services than using requests.

Re: How to set up where clause for date when extracting data from a map service using python

Kara_Shindle — Thu, 23 Jun 2022 17:39:24 GMT

I have a script that takes the current date and subtracts however many days I need off of it, but I'm doing it using a SQL expression.

"SystemEndDate IS NULL AND Type = 7 AND (GETDATE() - last_edited_date) <= " + str(days) + "

Re: How to set up where clause for date when extracting data from a map service using python

RViruet — Thu, 23 Jun 2022 20:46:41 GMT

You could try something like:

params = {'where': "CollisionDate >= '{}'".format(datetime.datetime.now() - relativedelta(years=5))}

Re: How to set up where clause for date when extracting data from a map service using python

LoriEmerson_McCormack — Thu, 23 Jun 2022 22:16:03 GMT

Thank you, Josh, Kara, and RViruet,

I was able to get this to work, using SQL date function:

params = {'where': 'EXTRACT(YEAR from CollisionDate) >= "2017"',