I have a large polygon feature class which I know contains duplicate features. I have tried using the Find Identical tool, but is just runs for days without producing a result. I have several different fields that I'm checking against:

  • Shape
  • COA
  • Shape_Length
  • Shape_Area

The COA field has fewer possible values (thousands vs millions) than the others and duplicates only exist when the COA code is shared. Therefore the logical way to search is to only search for matching shapes in places among fields that have the same COA.

Is there a was to tell ArcGIS 10.1 to search in this manner? I suspect is it currently checking each shape against every other shape in the feature class, which is why it is taking so long.

  • 1
    On which column(s) have you constucted indexes? Since the area and length would be identical if the shape is identical, adding these to the comparison is forcing a full table scan for each feature (N squared search complexity) – Vince Sep 1 '14 at 11:18
  • I had forgotten to add a spatial index, I have now created one an re-run the Find Identical. Its been running for 6 hour now. I'm worried that the computer is running out of memory as is has already filled the 16GB of RAM. I have a pagefile on SSD which should help but is not unlimted in size – falcs Sep 2 '14 at 14:15

ArcMap is not comparing each geometry with every geometry, just those identified by the spatial index as being "nearby," but that's enough. There does not appear to be any way to control how the program flow operates from outside the tool.

I whipped up a shapefile with 100k circles 1.0 degrees in diameter and randomly distributed, snapped to a 0.25 degree grid, with an ATTR1 string attribute containing one of the first 10 letters of the alphabet, randomly selected. I then ran Find Identical against the shapefile, and it took 3.6 minutes to execute. I then built an index on the ATTR1 field, and re-executed, and this also took 3.6 minutes.

I then copied the shapefile to a file geodatabase and repeated the procedure, with runtimes of 4 minutes (with and without attribute index).

Then I loaded the data into a PostgreSQL database with SDE.ST_GEOMETRY storage and again repeated Find Identical, and execution took 14.5 minutes.

Then I executed the query:

SELECT  a.objectid,b.objectid 
FROM    circles100k025 a, circles100k025 b 
WHERE   st_equals(b.shape,a.shape) = 't' AND
        b.attr1 = a.attr1 AND
        b.objectid > a.objectid

and this executed in one minute. Changing the order of the ST_EQUALS and "b.ATTR1 = a.ATTR1" WHERE terms had no impact on performance, nor did dropping the attribute index (though it is not safe to assume that all database optimizers will be equally clever).

From this I conclude that you could improve on performance by using a method other than Find Identical with this dataset. Among potential solutions are:

  • Use PostgreSQL with a variant of the above query
  • Split the table into multiple feature classes by COA attribute, then run Find Identical on the subsets
  • Write your own nested query in Python (or ArcObjects), where you'll have control over search constraints

Specifying the SHAPE_LENGTH and SHAPE_AREA attributes is not going to improve the results, or the query performance, so I recommend you leave them out of your search procedure.

  1. Create string field STRX=int (shape.centroid.x*100)
  2. Similar STRY
  4. Summary statistics on FID - first, NEWSTRINGFIELD as case field
  5. Join summry table to parent using NEWSTRINGFIELD
  6. Query FID does not equal first.fid. Selected records highly likely duplicates
  • 1
    You don't mention index construction in this workflow. With millions of features, indexes are absolutely critical. – Vince Sep 1 '14 at 11:13
  • 100% agree and like your answer, not sure about PostgresSQL part in ArcGIS solution – FelixIP Sep 1 '14 at 23:38

The Arc "Find Identical" tool has a bug. See http://support.esri.com/en/bugs/nimbus/TklNMDk2MzEx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.