topic Re: Spatial Queries are very slow on large tables in File Geodatabase API Questions

Spatial Queries are very slow on large tables

SimonOlsberg — Fri, 03 Jun 2011 17:30:18 GMT

I am trying to use the api to perform spatial queries on a relatively large databases (e.g. census blocks in North Carolina - a little under 300,000 records)

I use the Table::Search() method with an envelope which covers only a handful of blocks.
The Search method call itself completes quickly but enumerating over the small number of features in the EnumRows object returned is very slow - sometimes a call to EnumRows::Next() takes several seconds to return.

Is the API not able to make proper use of the spatial index?

Re: Spatial Queries are very slow on large tables

LanceShipman — Mon, 06 Jun 2011 17:20:06 GMT

It's a bug with the usage of the spatial index.

Re: Spatial Queries are very slow on large tables

SimonOlsberg — Mon, 06 Jun 2011 18:03:49 GMT

So it is something that will likely be fixed in the next update? It's a pretty vital issue for making this a usable technology for working with file geodatabases.
Thanks

Re: Spatial Queries are very slow on large tables

LanceShipman — Mon, 06 Jun 2011 18:34:43 GMT

It's likely to be fixed in the next update.

Re: Spatial Queries are very slow on large tables

SimonOlsberg — Tue, 09 Aug 2011 15:05:03 GMT

I just tried the new 1.1 API and unfortunately I don't see any improvement with this issue.
Was it supposed to have been fixed? Are there any detailed release notes describing the changes and fixes in each release?
Thanks
Simon

Re: Spatial Queries are very slow on large tables

LanceShipman — Tue, 09 Aug 2011 15:07:38 GMT

The primary purpose of 1.1 was the .NET wrapper. We were able to fix a bug with domain assignment but the spatial index issue was not fixed in 1.1.

Re: Spatial Queries are very slow on large tables

SimonOlsberg — Fri, 23 Mar 2012 20:00:46 GMT

I had hoped that the new 1.2 version of the API would fix this issue - the release notes certainly seem to suggest that the spatial index is now being used correctly.
However I am still seeing a problem.
When I search on a large database within a small envelope, while the calls to enumRows->Next() that successfully return a found row within my envelope return quickly, the final call to enumRows->Next() which returns NULL indicating that there are no more rows with the envelope takes a very long time (i.e. upto 30 seconds in my example).
So something is still not right.

Simon

Re: Spatial Queries are very slow on large tables

LanceShipman — Mon, 26 Mar 2012 14:01:46 GMT

I tested spatial search with a moderately large dataset (30+ million polygon features) and was able to retrieve a few thousand features in under one second. No lag on the final NEXT. Can you send your code? How many attributes does your feature class included. Does it include any blobs. Was the feature class created with the API or through ArcGIS.

You may want to drop your spatial index and re-create it.