AnsweredAssumed Answered

Select By Location on a large SDE featureclass

Question asked by wmarcell on May 16, 2012
Latest reply on May 17, 2012 by wmarcell
I have looked around and have not found any solution to this problem.
I am writing a GP tool that will allow the user to define a point by clicking on a point within a map service.  The user has the ability to pick which feature set they would like to run the tool against.  This tool works in under 10 seconds for every data layer that is in the map service other than the Common Land Unit layer.  This layer is in SDE, has a spatial index, and has 36 million features (rows).

So in arcmap, I can simply use the selection tool > click on a polygon > right click > Selection\Create Layer from selected features and then export this polygon to a new featureclass.  I can do it in about 20 seconds depending on how fast i can click :p

Using python however this process took 33 minutes to run using the code attached below.  It looks like when I use MakeFeatureLayer_management it creates a copy in memory of the 36 million features.  Then when I do the spatial selection it finds the one record its looking for.  Using print statements, this part of the code does not appear to take very long.  It appears that the part taking a long time is CopyFeatures_management. 

To me it looks like it is using a search cursor to go through the 33million records to find the one that is selected and then exporting it to the scratch shapefile.  This makes no sense to me.  If the script can make a feature layer and do the selection in seconds then why does it take 30 minutes to export a single record?  Are there any environment settings that I need to use?  Is there a way to just use the features of this featureLayer that are in the current extent of the map service?  Is there a better approach than the code below?  Is there something that our SDE Manager should do to the data to make it faster to query on location?

I am thinking if I can do it manually in arcmap in a few seconds then there is something that is completely wrong with my script (or perhaps arcpy).

Any help or feedback would be greatly appreciated!!!


import arcpy, sys from arcpy import env  layerSelection = sys.argv[1] featureSet = sys.argv[2] scratch_ws = arcpy.env.scratchWorkspace arcpy.AddMessage(scratch_ws)  try:     arcpy.env.overwriteOutput = True     arcpy.MakeFeatureLayer_management(layerSelection,'lyr')     arcpy.AddMessage('Running Spatial Intersect...')     arcpy.SelectLayerByLocation_management('lyr', 'INTERSECT', featureSet, 0, r'NEW_SELECTION')         arcpy.AddMessage('Copying Selected Features to Output...')     arcpy.CopyFeatures_management('lyr', scratch_ws + r'\selection.shp')     arcpy.Delete_Management('lyr') except:     print arcpy.GetMessages()

Outcomes