6

I have as set of shapefiles with approximately 300000 polygons each, as well as corresponding point shapefiles and tables with approximately the same number of row (equal or less rows than the polygons FC).

I need to join about 30 fields from the table of the point FC and from the stand alone table. It works, but it is quite slow.

I've tried different method : AddJoin + claculateField, JoinField and arcpy.da.UpdateCursor. Join Field is the slowest and the Cursor is the fastest but I found it tricky to manage when the two tables are not matching exactly. Nevertheless, I was surprised that a spatial join is sometimes faster than a table join: this makes me think that I a missing something. I saw on a Website that I should work with layer instead of the FC, but I used "makeFeatureLayer" in my script and it didn't help much.

Can I improve this with indexing, with other FC types or with another approach ? Note that this is part of a broader Python script with arcpy, so I need a solution that can be used within this script.

  • I don't know specifics as I've not tested this particular situation, but one of the whole reasons to build indexes is when you're doing attribute key field table to table joins. Indexes should help in that kind of a situation. Also, yes, I would recommend trying it as a feature class rather than shapefile (try in a FileGDB) and see if that helps out. I've generally had better performance with a FC over SHP with most other tasks, though again, I haven't specifically speed tested this situation between the two. – John May 14 '14 at 21:38
9

I tested different methods for joining massive shapefile tables: the fastest way is using cursors.

  1. Read the join table creating a dictionaries with values of join field as keys and the rest of the fields as dictionary values
  2. Create new fields in the target shapefile
  3. Update the target shapefile
  4. Use the if/else statements to join and populate the target fields

    import arcpy
    targshp = r"C:\dataset\shape1.shp"
    joinshp = r"C:\dataset\shape2.shp"
    
    ## Step 1     
    joinfields = ['Join_Field', 'Field1', 'Field2']
    joindict = {}
    with arcpy.da.SearchCursor(joinshp, joinfields) as rows:
        for row in rows:
            joinval = row[0]
            val1 = row[1]
            val2 = row[2]
            joindict[joinval]=[val1, val2]
    del row, rows
    
    ## Step 2
    arcpy.AddField_management(targshp, "New_Field1", "LONG", "5")
    arcpy.AddField_management(targshp, "New_Field2", "LONG", "5")
    
    ## Step 3
    targfields = ['Key_Field', 'New_Field1', 'New_Field2']
    with arcpy.da.UpdateCursor(targshp, targfields) as recs:
        for rec in recs:
            keyval = rec[0]
    
            ## Step 4
            if joindict.has_key(keyval):
                rec[1] = joindict[keyval][0]
                rec[2] = joindict[keyval][1]
            else:
                rec[1] = 0
                rec[2] = 0
            recs.updateRow(rec)
    del rec, recs
    
  • 2
    +1 You should see a marked improvement if you use the data access module for the cursors, though. – Paul Jun 19 '14 at 14:09
  • Thanks @Paul, I updated the codelet using data access module – Nonpenso Jun 19 '14 at 15:26

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.