16

Is there a way to select distinct values from a column in ArcMap? I have the data in both GDB and SHP formats. I have searched for ways to select using SQL, QueryLayers, ModelBuilder and individual toolboxes and it appears as all selection options always SELECT * FROM tableName WHERE ...

In SQL I would write SELECT DISTINCT columnName FROM tableName.

17

Or you can run the ArcToolBox tool Frequency (Analysis Tools>>Statistics>>Frequency) which will output a table with unique values and a count of how many time they appear.

Or you could write python script that gets a SearchCursor on a field then build a list of all values of the form

if value not in myList:
    myList.append(value)
  • 3
    If you don't have an ArcInfo license then use this toolbox (tested on shapefiles only) resources.arcgis.com/gallery/file/geoprocessing/… – user681 Jan 27 '12 at 18:39
  • 3
    Its amazing how many functions that once required an ArcInfo function can now be implemented using some fairly simple Python code/modules. It's great! – RyanDalton Jan 29 '12 at 5:50
  • Thanks Dan, that did the trick. My license didn't support the Frequency toolbox. – Steve Jan 30 '12 at 14:32
12

Use a Python list comprehension.

import arcpy
fldName = 'val_fld'
fcName = 'feature_class.shp'
#set creates a unique value iterator from the value field
myList = set([row.getValue(fldName) for row in arcpy.SearchCursor(fcName)]) 

For large datasets a memory efficient method would be to use a generator expression.

myList = set((row.getValue(fldName) for row in arcpy.SearchCursor(fcName,fields=fldName))
  • 1
    Since you are only interested in one field, for better performance I would specify the optional fields argument, e.g. myList = set([row.getValue(fldName) for row in arcpy.SearchCursor(fcName, fields=fldName)]) – blah238 Jan 29 '12 at 1:03
  • Good idea. I'd be curious to test the performance difference. For SDE data sources it's likely significant, but for shapefiles and file geodatabases I'd be surprised if was appreciable. – tharen Jan 29 '12 at 4:21
  • This is great. I had a link that showed this nicely but it broke one day. Hope se will have this for a nice long time. – Justin Jan 29 '12 at 18:12
4

If your data is in PGDB format, you can do the following within the query builder dialogs (definition query, select by attributes, toolbox expressions etc.) using a subquery:

SELECT * FROM tableName WHERE ...

column_to_test_for_unique_values IN 
(SELECT column_to_test_for_unique_values
FROM table_name
GROUP BY column_to_test_for_unique_values HAVING
Count(column_to_test_for_unique_values)=1)

This will return the records for which the values in the column_to_test_for_unique_values are unique.

4

If you only have a Basic (formerly called ArcView) license, you could open the Table view, right-click a column, and select Summarize.

1

If your data is in a SDE (spatial database engine) one can use python and arcpy's ArcSDESQLExecute object. One can pass complex sql using this "method."

# set up executor for sql
executor = arcpy.ArcSDESQLExecute(sde_connection_sql)

# sql statements
select_max = """select max(LOAD_ID) from SDE.FEATURE_CLASS"""

# get load id
result_max_id = executor.execute(select_max)
0

Or use your Python script to export to CSV and then use the Python API of another database (say SpatiaLite) to read the csv and do a proper SQL query on it from within the same script. For a large table this might be a tiny bit quicker than rolling your own list builder - dunno.

However you do it, this is still a really annoying "feature" of ArcGIS.

0

What about using a distinct in a subquery (following is on a FGDB featureclass):

"STATE_NAME" = (select distinct "STATE_NAME" from EsriUsaCountiesDetailed)

Note from the help (10.0) that this does have limitations:

Coverages, shapefiles, and other nongeodatabase file-based data sources do not support subqueries.

0

As Justin suggests. I normally do a summary on the field I want, then do a select distinct on the dbf run a little calculation to categorize each distinct value then join that back to the original.
It is the long way around, and you have to fenagel with your favorite caclulation methods. but...
Whatever gets the job done.

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.