topic Re: Feature Layer query can't return all the matching records in ArcGIS API for Python Questions

Feature Layer query can't return all the matching records

simoxu — Thu, 20 Feb 2020 07:00:25 GMT

I am using 1.7.0 version of the Python API.

I have a fairly large feature layer published in our ArcGIS Portal 10.7, roughly 1 million records in it.

When I do a query on this layer, and only return the count only, I'll get the count 1329 which is correct.

But when I try to return the actual records, it only returns 99 records despite that I've set return_all_records to True.

Anyone know why? and how can I fix it?

Many thanks.

Re: Feature Layer query can't return all the matching records

JoshuaBixby — Thu, 20 Feb 2020 15:00:48 GMT

At this point, it is unclear whether this is a REST API or ArcGIS API for Python issue. Have you tried directly querying the REST API to see what results you get?

Re: Feature Layer query can't return all the matching records

simoxu — Fri, 21 Feb 2020 06:07:43 GMT

Thanks for the response.

I tested the same spatial filter in REST API, it seems returning all the records.

So it is clear to me the issue is in the python API.

Re: Feature Layer query can't return all the matching records

JoshuaBixby — Fri, 21 Feb 2020 15:56:31 GMT

What if you check the records in the feature set before converting to data frame? That might tell you whether it is a feature set issue or a data frame issue.

Re: Feature Layer query can't return all the matching records

simoxu — Sun, 23 Feb 2020 23:04:46 GMT

Yes, I tried that. the query does not return all the features intersecting with the input geometry.

This only happens when the geometry filter is being used. there's no problem with the attribute filter using the where parameter.

Re: Feature Layer query can't return all the matching records

JoshuaBixby — Mon, 24 Feb 2020 14:16:47 GMT

I wonder if the results are correct in the ArcGIS API for Python but they are based on something happening in translation of your geometry filter from Python to REST. What is your geometry filter?

Re: Feature Layer query can't return all the matching records

simoxu — Tue, 25 Feb 2020 00:08:30 GMT

Hi Joshua

The geometry filter is a ploygon that returns all the land parcels that intersect with it.

Here is an update on what I've done:

I exported JSON of this geometry and tried it in the REST API, it works perfectly.

In Python API, the geometry works fine if only IDs, or counts are returned. so my current workaround is to return all the IDs then query the same layer again using the where parameter to return all the features, which works fine, but apparently this is not the ideal way to do it.

I also tried this in a standalone script, same issue. So it's not a Jupyter notebook bug either. so far I can pretty much isolate it to ArcGIS Python API. I just need ESRI to confirm my guessing.

I've send ESRI a support request, and I'll keep this post updated when I hear from them.

Cheers

Simo

Re: Feature Layer query can't return all the matching records

JoshuaBixby — Tue, 25 Feb 2020 18:44:03 GMT

Nice work, and sometimes a workaround is the best, most practical path forward. Can you reproduce the behavior using a public-facing service and some arbitrary polygon? If so, please share. I am guessing it has to do with the geometry information is getting translated from Python to REST.

I will be interested what you hear back.

Re: Feature Layer query can't return all the matching records

simoxu — Thu, 27 Feb 2020 03:05:33 GMT

No worries.

I also tried the same query on a large point layer (the centroids of the features in the original polygon layer), and it works fine. so it happens on polygon layers, I haven't tested polygon lines which I don't use very often.

Hope we can get the attention from a member in the Dev Team? John Yaist‌

Re: Feature Layer query can't return all the matching records

JohnYaist1 — Fri, 28 Feb 2020 17:30:39 GMT

I'll look through this today, simo xu. Do you see the same results with 1.7.1?

Re: Feature Layer query can't return all the matching records

JohnYaist1 — Fri, 28 Feb 2020 22:49:03 GMT

Hi simo xu

I'm not seeing what you're describing using that last couple releases of the Python API. I tried this on a couple different layers, one small one, one larger one (not a huge feature layer, but the features contain a large number of vertices).

Do you see this on any other polygon layer with an arbitrary geometry filter? Do you have a public-facing layer as Joshua Bixby suggests? Can you try explicitly adding the where clause to see if that works?

"where='1=1'"‍

Re: Feature Layer query can't return all the matching records

simoxu — Mon, 02 Mar 2020 07:27:24 GMT

Hi John Yaist‌

Thank you for looking into this.

I tried the same code in a 1.7.1 version, it gives me same result.

Here is the environment:

The geometry of the filter is quite complicated as shown below:

val_flayer is the cadastral feature layer.

The feature service I am using is for internal use and behind our organisational firewall, which is unfortunate, otherwise, it would be easy to reproduce the issue.

Thanks

Re: Feature Layer query can't return all the matching records

simoxu — Wed, 04 Mar 2020 00:22:58 GMT

John Yaist‌

Update:

This strange behavior only happens when Feature Service being queried is published to the Federated ArcGIS server directly -- this is my case.

I tried to upload the same FGDB zip file to our ArcGIS Portal and publish it there as a hosted service, it works fine.

Re: Feature Layer query can't return all the matching records

simoxu — Wed, 04 Mar 2020 07:25:52 GMT

It has something to do with query pagination.

when I monitor the the REST requests it sent to the server, it sends two requests:

1. get the the count of the records

2. get the actual features with resultRecordCount paramter sets to the result of the first request.

This is very logical and should work, but it doesn't for the feature service directly published to the federated server.

if I remove the resultRecordCount parameter in the REST request in the second step. it returns all the features...

Re: Feature Layer query can't return all the matching records

simoxu — Thu, 05 Mar 2020 04:11:11 GMT

It's proven that the actual differences are in the hosted service and non-hosted service. hosted service works regardless point layer or polygon layer. and I can reproduce the error using REST API. so this may not be a ArcGIS Python API bug. The issue is at a lower level I am afraid.

Re: Feature Layer query can't return all the matching records

simoxu — Wed, 11 Mar 2020 04:35:57 GMT

Hi John Yaist‌

This is very likely an ArcGIS Python API bug.

In the API source code for the query function (layer.py, Line 650), it does not test the exceededTransferLimit property in the response, the code assumes the query always return the specified number of records when possible. This is not the case for spatial filter....

exceededTransferLimit was introduced in ArcGIS Server 10.3.1, please see the details in the following link

In some cases when using the resultOffset and resultRecordCount parameters, the exceededTransferLimit property may be included in the query results even though the value specified in the resultRecordCount has not been exceeded. This is due to internal spatial index filtering of the query results. For this reason, you should always rely on the exceededTransferLimit property to determine if you should page through results rather than relying on the number of results returned from each page. In some extreme cases, zero results can be returned but the exceededTransferLimit property will be returned. In these cases, you should continue paging through your results until exceededTransferLimit is no longer returned.

Query (Feature Service/Layer)—ArcGIS REST API: Services Directory | ArcGIS for Developers

Hope this makes sense.

Regards

Simo

Re: Feature Layer query can't return all the matching records

JohnYaist1 — Wed, 11 Mar 2020 16:02:03 GMT

Thanks for all this information, simo xu‌. To get an official bug report logged I suggest contacting support who can document the specific workflow. I'll look into investigating on a service published directly to a federated server rather than a hosted one and see what I can reproduce.

Re: Feature Layer query can't return all the matching records

JohnYaist1 — Wed, 11 Mar 2020 17:06:14 GMT

Hi simo xu‌

What version of Server are you publishing this service to?

Can you outline exactly how the service is "published to the Federated ArcGIS server directly?" I want to follow the exact steps to test on a service published in the exact same manner.

Re: Feature Layer query can't return all the matching records

simoxu — Wed, 11 Mar 2020 23:05:16 GMT

Thanks John Yaist‌, I've been talking with ESRI local support and just urged them to escalate it to ESRI US.

Re: Feature Layer query can't return all the matching records

simoxu — Thu, 26 Mar 2020 00:53:23 GMT

BUG-000129661: Querying a feature layer from a referenced feature service with a geometry filter returns an incomplete results using the ArcGIS API for Python