AnsweredAssumed Answered

Show distinct tuples regardless of column order

Question asked by jeff.thomasilm on Oct 31, 2018
Latest reply on Nov 9, 2018 by bixb0012

I just added a corresponding request to ArcGIS ideas:

Option to eliminate pairs from Generate Near Table 

But in the meantime, I'm wondering if anyone can help me figure out how to find duplicate pairs of attribute values from separate columns, regardless of column order, by way of SQL and/or ArcPy.

The problem (described in the ArcGIS Idea above) is that the Generate Near Table tool creates pairs of distance values that are identical, as uniquely identified by the pairing of IN_FID and NEAR_FID attributes. That is, the unique distance between FID "a" and FID "b" occurs twice: "a-b" and "b-a" in the IN_FID and NEAR_FID columns, respectively. I only want one value from that pair, otherwise it affects statistics like the count and average of distances. I've found that it is possible to find these pairs with SQL:

mysql - Show distinct tuples regardless of column order - Stack Overflow (I stole this question title for my post, obviously )

But of course, you can't easily break out of the WHERE clause in ArcGIS. Maybe this is possible in ArcPy, but I'm not sure how to adapt that SQL to work in ArcPy. If anyone could help me with that, it would be a boon to doing not just this specific SQL query but how to do any advanced SQL query in ArcPy (or other methods?) that's not otherwise possible within ArcGIS.

Note: the near tables I've created are in a file GDB, but I do have the option of putting them in SDE if that helps. I do not, however, have the option of accessing SDE outside of ArcGIS, e.g., through SQL Server Management Studio. I'd like to learn how to do advanced SQL queries (that is, SQL involving more than just WHERE on a single layer) in ArcGIS regardless of data source. If it's possible at all.

Thank you!

Outcomes