3

I've been having a problem in ArcGIS 10.4.1 when performing larger joins (either attribute joins or spatial joins)--that is, with around 2,000 to 6,000 points. The join seems to work fine, but then when I export the joined tables out to a new table (all this is in one geodatabase), some of the records are repeated. That is, I may start with 3400 points in one table, join another smaller table, and then export the result (also 3400 records), but end up with 3412 points in the new table (the 12 extra records seem to be duplicates).

Any idea why this is happening, and how to handle it? Is it some kind of internal memory issue?

So far the only "workaround" I've found is to select and then copy all the records in the joined attribute tables out to Excel, then import them back to ArcGIS and re-plot the points, which is fairly tedious. Splitting the datasets into 2 or 3 smaller sets also seems to resolve the issue, but again, is annoying. Any advice would be most welcome.

  • 2
    You probably have a one-to-many join, which is producing the extra records. – klewis Sep 13 '16 at 18:21
  • 1
    As @klewis pointed there are duplicates in your second table. Export join to shape file, dbased table to avoid this. If this is what you really want – FelixIP Sep 13 '16 at 19:37
  • You can use the "Delete Identical" tool to purge unwanted records - desktop.arcgis.com/en/arcmap/10.3/tools/data-management-toolbox/…. – Brent Edwards Jun 27 '18 at 17:40
  • Brent thanks for the input, but this problem was resolved; it was indeed a one-to-many join issue I was having--see answer chain below. I thought I had unique IDs but did not, and was also confused by the way ArcGIS handles memory by only showing this behavior upon export, and not with the initial join. Richard did a great job of explaining this to me! – bwilkes Jun 29 '18 at 14:18
3

At ArcMap 10.1, for data in the same geodatabase, any time that you join two data sources together that have a 1:M relationship (two or more records match on the join field in the joined table) the full set of related records are expanded to complete all record combinations whenever the data is exported. So your join table has some duplicate values in the join field that match the same record in your primary table. This is a good behavior, since that is how Access and other databases deal with these relationships, and it exposes false assumptions that two data sources have a 1:1 relationship when in fact they have a 1:M relationship. Perform a summary on the join field in the join table and look for any values with a frequency of 2 or more.

For Spatial Joins the number of points should not expand unless you inputted a layer as the target that had a 1:M attribute join in place, or you chose the One To Many output option and had points that touched the boundary of two or more polygons/lines.

  • I wish that were the answer, but I am performing a one-to-one join, and each original table has unique IDs. The initial join result looks good, too. It's only when I try exporting to a new table that I get repeated/extra records. – bwilkes Sep 14 '16 at 13:35
  • 1
    You say the values are unique, but did you run a summary to confirm that? You need to support your statement that you have created a one-to-one join by explaining how you have tested your data to prove that is the case. Prior to export all 1:M joins appear as 1:1 joins in the tableview, so that is not proof that you don't have a 1:M join in reality. Only export causes the true 1:M join to be completely expanded. What version of ArcMap are you using? – Richard Fairhurst Sep 14 '16 at 14:30
  • I reread the post and see that you have version is 10.4.1. So far everything you have said is still consistent with my theory that in reality the values in the join table are not unique and you have created a 1:M join which will not be apparent when you initially join the data, but will be revealed when you export your data. – Richard Fairhurst Sep 14 '16 at 14:44
  • Thanks, Richard! I think you are right. I just ran a frequency table and lo and behold, several of my "IDs" are in fact duplicates in the joined table. I wonder why this only shows up upon export, and not with the initial join? – bwilkes Sep 14 '16 at 15:45
  • 2
    It does not show up because creating and maintaining the expanded set of records in memory is very slow (based on my bad experiences with the Make Query Layer tool which will expand the records of a 1:M join in memory so that they appear as new features on the map and new records in the tableview). Doing the expansion only for Geoprocessing to create a new feature class does not affect performance significantly and once done does not affect redraw speeds and overall performance of ArcMap. I am in full support of the way join currently behaves. – Richard Fairhurst Sep 14 '16 at 20:07
0

I've not heard of this before personally, so I don't know if this would fix it, but just a thought if copy and paste of records still works: Set up an empty feature class for the desired resulting output (you could even do this by running the export you currently are (that creates duplicates) and then running the delete features geoprocessing tool. That way you end up with an empty feature class with the desired schema. Then, you should be able to simply copy and past the features from your joined layer into the new empty feature class (and if schema matches up correctly, it should copy over all the attributes for you as well as the geometry).

Just a thought for another work-around though... again, no guarantees though as I don't know what might be causing the issue in the first place.

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.