topic Re: efficient spatial query on many feature classes? in Python Questions

efficient spatial query on many feature classes?

davedoesgis — Wed, 14 Nov 2018 02:25:01 GMT

Short version: Is there an efficient way to query 300+ feature classes with the same select-by-location query for an area of interest? We can do this using spatial SQL(SQL Server), ArcGIS Server map/feature services, or Portal layers. I can load most of the data into ArcGIS OnLine, but licensing agreements will require me to keep some layers locally.

Long-ish version: I inherited some ArcPy code from a former coworker that starts with a polygon of interest, queries the SDE database (SQL Server) for any features that overlap, and creates a PDF report of them. There are over 300 different layers in the report, which would mean a lot of database queries. He solves this by combining all the data into a single feature class each for points, lines, and polygons. It's really quick with only 3 queries to the database. Since the input layers all have different attribute fields, his process appends all the fields into a single text field in the format "field1: value1; field2: value2; field3: value3; [field4 etc.]".

It's actually a pretty clever solution, except that it requires a bunch of processing to consolidate all the data into those 3 feature classes for data loading/updating. More code is needed for all the string parsing to turn the one delimited text field back into N fields. Plus, we lose track of field types, since everything is just a string.

Going forward, I'd like to just maintain all my feature classes separately and do away with the merged layers, but I also can't wait 10 minutes to do 300+ queries. Multi-threading could make it a bit faster, but might melt also my server. So, is there an efficient way to send the same select-by-location query to a whole bunch of feature classes in SDE? How about to ArcGIS Server, Portal, or ArcGIS Online?

thanks!

Re: efficient spatial query on many feature classes?

NeilAyres — Wed, 14 Nov 2018 08:21:44 GMT

You could try making a near table which supports multiple input features.

Just get the closest and read the table.

Generate Near Table—Help | ArcGIS Desktop

Re: efficient spatial query on many feature classes?

George_Thompson — Wed, 14 Nov 2018 11:41:23 GMT

Adding a few more communities that may be able to help: Geodatabase‌ Enterprise GIS‌

Re: efficient spatial query on many feature classes?

JoshuaBixby — Wed, 14 Nov 2018 17:03:46 GMT

It seems like this querying is part of a process to generate PDF reports, why is 10 minutes for the process to execute a problem? Is the process running more frequently than 10 minutes?

Re: efficient spatial query on many feature classes?

davedoesgis — Thu, 15 Nov 2018 02:23:09 GMT

Users run this by submitting the area of interest polygon from a web app. They are sitting there watching an hourglass until it returns the result. I agree that if it were just a cron job, then it could take longer, but there is a user waiting on the other end.

Re: efficient spatial query on many feature classes?

davedoesgis — Thu, 15 Nov 2018 02:30:45 GMT

Clever idea!

I wonder if this single command is really running N queries under the hood. I will try performance testing this, but if anyone out there has an idea, I'd appreciate the feedback.

Re: efficient spatial query on many feature classes?

davedoesgis — Thu, 15 Nov 2018 02:31:03 GMT

Thanks, George!

Re: efficient spatial query on many feature classes?

NeilAyres — Thu, 15 Nov 2018 08:46:19 GMT

I have found that the generation of near tables can be very quick.