What is the maximum length for a Feature Layer Query?

1518
7
03-17-2022 10:49 AM
BenRomlein
Occasional Contributor

I'm trying to query a feature layer and my query contains 116535 characters. I'm getting the error:

Exception: Unable to complete operation.
(Error Code: 400)

I suspect my query is too long, but what is the limit and is there a way to temporarily bypass it to make my query work?

This is a FeatureLayer published on ArcGIS Server 10.9 and I'm submitting my query using the arcgis python api:

myFL = arcgis.features.FeatureLayer(
url="my.host/arcgis/rest/services/MyService/FeatureServer/0")
SQL = "myID in (<long list of ids>)"
loc_query = myFL.query(where=SQL)

 

0 Kudos
7 Replies
jcarlson
MVP Esteemed Contributor

I've done some absurdly long queries myself, though I've not checked the string length on some of the bigger ones. Can you test the same query on a subset of IDs?

You know, the query method can take a list of objectids using the object_ids parameter. Try that out, too, and see if you get any different results.

- Josh Carlson
Kendall County GIS
BenRomlein
Occasional Contributor

Thanks for the tips. Based on trying out your advice, I'm more stumped now than before.

This code runs in a loop, there are nine other queries (the next longest is 91821 characters, returning 7374 records) I'm running (all on the same feature service) and saving each resulting feature set as a feature class. This is the only one of those queries that results in error.

Trying a subset of IDs in the query, I can query up to 59 IDs successfully. Adding the 60th results in the error. Starting at the 60th, I can query the next 8821 IDs successfully. Adding the 8822nd results in the error. Starting at the 8822nd, I can query the remaining 1460 IDs successfully.

Is there a rhyme or reason to these apparent limits or is this just a cursed query? I suppose I can write those into my code to make it work, but it's confusing to me why it would need to be like this.

 

0 Kudos
jcarlson
MVP Esteemed Contributor

Very interesting! If this is the case, I would take a hard look at the value of those particular IDs. So, is querying the 60th ID successful on its own? It only causes the issue when queried in conjunction with the preceding 59?

Also, what are these IDs? Are they just integers? Are any of them null?

- Josh Carlson
Kendall County GIS
0 Kudos
BenRomlein
Occasional Contributor

Both 'breaking' IDs work on their own, or as a pair together--and when included with the next set of IDs.

The IDs are strings, no nulls in the query, or in the table.

The query is assembled based on the results of a prior query like this:

ids = [feat.attributes['originalID'] for feat in res.features]
SQL = "originalID in ("
for i in ids:
SQL += "'{id}', ".format(id=i)
SQL = SQL.strip(", ") + ")"

and works for nine other lists of ids coming from the same FeatureLayer.

0 Kudos
jcarlson
MVP Esteemed Contributor

What about the preceding string? Any odd characters in them? Can you do a query combining just the "breaking" ID and the one prior to it? I can't think how else something might be getting broken.

I do a similar thing in assembling a query string. You could condense those lines and use str.join('sep') to get your string without the ending ", ".

ids = ','.join([f"'{feat.attributes['originalID'}'" for feat in res.features])
SQL = f'originalId in ({ids})'

 Not that that will fix the weirdness going on, but thought I'd mention it.

- Josh Carlson
Kendall County GIS
0 Kudos
Scott_Tansley
MVP Regular Contributor

I'm not sure how you pass the loc_query, but it could be that your code is using a HTTP GET request.  Can you convert it to a POST request instead.  

In IE, the max limit of a URL in a get was 2048. 

Maximum URL length is 2,083 characters in Internet Explorer (microsoft.com)

Most other browsers are "around" that number:

http - What is the maximum length of a URL in different browsers? - Stack Overflow

I'm making an assumptionn that it's a GET because I can't see the rest of your code, but that's where I'd start.

Scott Tansley
https://www.linkedin.com/in/scotttansley/
0 Kudos
BenRomlein
Occasional Contributor

Still stumped on this one.

Querying the breaking ID with the preceding IDs returns the expected two records.

I'm submitting the query through the arcgis api for python's featureLayer.query method (https://developers.arcgis.com/python/api-reference/arcgis.features.toc.html#featurelayer). This submits a POST request I can verify in IIS logs:

 

2022-03-18 12:59:58 <My.Host> POST /arcgis/rest/services/<MyService>/FeatureServer/0/query - 443 - <My.IP> Geosaurus/1.9.1 http 200 0 0 104

 

and generates a SEVERE log in arcgis server:

 

<Msg time='2022-03-18T08:59:58,435' type='SEVERE' code='17000' target='<MyService>.MapServer' methodName='GraphicFeatureServer::GetCount_Internal' machine='<My.Host>' process='9904' thread='6604' user='<myUser>' requestId='83979d45-18d9-4d48-9461-09a94b10ff3a'>Error: Invalid query</Msg>

 

I guess I'll try copying the featureClass or exporting and seeing if the storage format has any impact, and then try to check into the values some more. Thanks for the replies, all I'll update the thread if I find a resolution.

0 Kudos