[GIS] Selecting features in an ArcGIS attribute table based on values of other features in the same table using SQL

arcpyattribute-tablefile-geodatabasesearchsql

I have a line feature class (saved in a feature dataset within a File GDB) which is comprised of a set of intersecting lines.

I have used the Add Geometry tool to add Start_X, Start_Y, End_X and End_Y fields to the attribute table. The lines are not all orientated in the same direction, so for certain pairs of features in the table, End_X = End_X and End_Y = End_Y.
I want to use the Select Layer By Attributes tool with an SQL query (if possible) to select only lines in the table with matching end coordinates and then run the Flip Line tool on the selection in order to reverse the direction of the selected lines.

It's also worth noting that the OIDs are in a random order, e.g. line 1 may be connected to line 10.

Is it possible to use an SQL statement to select features in an attribute table based on the values of other features?

I tried 

SELECT * FROM table_name WHERE:
END_X = END_X AND END_Y = END_Y AND OBJECTID <> OBJECTID

I'm sure it will be obvious to most that all that does is search each row individually and come up with nothing, though hopefully it gives an idea as to what I'm trying to do.

If it is not possible using SQL, is it something that could be done with Arcpy Search Cursors? (I am an extreme novice with Arcpy/Python scripting. I just know Search Cursors exist and can be used to find records in tables.)

Best Answer

I can think of a couple of options, if what you're saying is that the X and Y values both need to be the same, but the object ID different; one in ArcGIS, one in Microsoft Access.

ArcGIS:

  • Calculate a new string field (lets call it "END_XY_COMBO") that will be a combined value of your END_X and END_Y values for each row. Assuming your X/Y values are in number format, you'll need to convert to string, so calculate that new field like:

    END_XY_COMBO = str(!END_X!) + "-" + str(!END_Y!).

    This will combine the two X/Y values separated by a dash.

  • Then export this table as a second copy of itself.

  • Then join the second copy to the first copy on the "END_XY_COMBO" field, then run a Select By Attributes, and select the records from table1 where: TABLE1.ObjectID <> "TABLE2.ObjectID".

    This should select all records where the END_X and END_Y values are all the same, but the Object IDs are different.

Microsoft Access:

  • Bring your table into Access, then make a second copy of it.
  • In Table 1 create a new text flag field called "Match_Flag".
  • Open the query builder in design view and add Table 1 and Table 2.
  • Create a join between Table 1 and Table 2 on the "END_X" field, and create a second join between them on the "END_Y" field (using one-to-one join, meaning values have to match in both fields in both tables).
  • Set the query type to Update Query.
  • Add the "Match_Flag" field from Table 1 and set it to update to "Y".
  • Add the ObjectID field from Table 1, leave the "Update To:" part blank, but set the criteria to be: <> [Table2]![ObjectID]. - This will flag all records in table 1 where the END_X and END_Y values are the same, but the ObjectID values are different.
  • Then you can export Table 1 from Access as a .dbf (needs to have a table name shorter than 8 characters and all field names 10 characters or less - you can make the table name longer after the export b/c ArcMap doesn't care, but Access does), then join the .dbf back to your original table in ArcMap on ObjectID, and select the records where Match_Flag = "Y".
Related Question