Create a sorted list of unique values from a table or feature class

Summary

Instructions provided describe how to create a sorted list of unique values from a table or feature class. A sorted list of unique values can be created by first sorting a table based on the column of interest and then generating a list of unique values. Normally, a list of unique values is in the order of occurence of the unique value. If the table is in order, each value is encountered in order and produces an ordered list.

Procedure

1. Create a IFeatureClass object. This can be any workspace type (Coverage, Shapefile, Personal Geodatabase, or Geodatabase).
   
   Geodatabase
   
   

   Code:
       Dim pWorkspace As IWorkspace
       Dim pWorkspaceFactory As IWorkspaceFactory
       
       Set pWorkspaceFactory = New SdeWorkspaceFactory
           
       Dim pPropertySet As IPropertySet
       Set pPropertySet = New PropertySet
       With pPropertySet
           .SetProperty "Server", "testserver"
           .SetProperty "Instance", "5253"
           .SetProperty "Database", "sde"
           .SetProperty "user", "example"
           .SetProperty "password", "example"
           .SetProperty "version", "sde.DEFAULT"
       End With
   
       Set pWorkspace = pWorkspaceFactory.Open(pPropertySet, Me.hWnd)
       
       Dim pFeatureWorkspace As IFeatureWorkspace
       Set pFeatureWorkspace = pWorkspace
       Dim pFeatureClass As IFeatureClass
       
       Set pFeatureClass = pFeatureWorkspace.OpenFeatureClass("Counties")

   
   
   Personal Geodatabase
   
   

   Code:
       Dim pWorkspace As IWorkspace
       Dim pWorkspaceFactory As IWorkspaceFactory
       
       Set pWorkspaceFactory = New AccessWorkspaceFactory
       Set pWorkspace = pWorkspaceFactory.OpenFromFile("D:\Data\example.mdb", 0)
       
       Dim pFeatureWorkspace As IFeatureWorkspace
       Set pFeatureWorkspace = pWorkspace
       Dim pFeatureClass As IFeatureClass
       
       Set pFeatureClass = pFeatureWorkspace.OpenFeatureClass("Counties")
   

   
   
   Shapefile
   
   

   Code:
       Dim pWorkspace As IWorkspace
       Dim pWorkspaceFactory As IWorkspaceFactory
       
       Set pWorkspaceFactory = New ShapefileWorkspaceFactory
       Set pWorkspace = pWorkspaceFactory.OpenFromFile("D:\Data", 0)
       
       Dim pFeatureWorkspace As IFeatureWorkspace
       Set pFeatureWorkspace = pWorkspace
       Dim pFeatureClass As IFeatureClass
       
       Set pFeatureClass = pFeatureWorkspace.OpenFeatureClass("Counties")

   
   
   Coverage
   
   

   Code:
       Dim pWorkspace As IWorkspace
       Dim pWorkspaceFactory As IWorkspaceFactory
       
       Set pWorkspaceFactory = New ArcInfoWorkspaceFactory
       Set pWorkspace = pWorkspaceFactory.OpenFromFile("D:\Data", 0)
       
       Dim pFeatureWorkspace As IFeatureWorkspace
       Set pFeatureWorkspace = pWorkspace
       Dim pFeatureClass As IFeatureClass
       
       Set pFeatureClass = pFeatureWorkspace.OpenFeatureClass("Counties:polygon")
   

   
   
2. Set up an IFeatureCursor to retrieve the field value in order (sorted).
   

   Code:
       Dim sFieldName as String
       sFieldName = "state_name"
       Dim pTableSort As ITableSort
       Set pTableSort = New esriGeoDatabase.TableSort ' ArcGIS 9.X
       'Set pTableSort = New esriCore.TableSort ' ArcGIS 8.x
    
       With pTableSort
         .Fields = sFieldName
         .Ascending(sFieldName) = True
         .CaseSensitive(sFieldName) = False
         Set .QueryFilter = Nothing
         Set .Table = pFeatureClass
       End With
       
       pTableSort.Sort Nothing
       
       Dim pFeatureCursor As IFeatureCursor
       Set pFeatureCursor = pTableSort.Rows

   
   
3. Set up the IDataStatistics object to retrieve unique values.
   
   

   Code:
       Dim pDataStatistics As IDataStatistics
       Set pDataStatistics = New DataStatistics
       
       pDataStatistics.Field = sFieldName
       
       Set pDataStatistics.Cursor = pFeatureCursor
       
       Dim pEnumVar As IEnumVariantSimple
       Set pEnumVar = pDataStatistics.UniqueValues

   
   
4. Retrieve unique values.
   
   

   Code:
       Dim value As Variant
       value = pEnumVar.Next
   
       Do Until IsEmpty(value)
           Debug.Print value
           value = pEnumVar.Next
       Loop
   

   
   
5. Complete code for a shapefile.
   
   

   Code:
       Dim pWorkspace As IWorkspace
       Dim pWorkspaceFactory As IWorkspaceFactory
       
       Set pWorkspaceFactory = New ShapefileWorkspaceFactory
       Set pWorkspace = pWorkspaceFactory.OpenFromFile("D:\Data", 0)
       
       Dim pFeatureWorkspace As IFeatureWorkspace
       Set pFeatureWorkspace = pWorkspace
       Dim pFeatureClass As IFeatureClass
       
       Set pFeatureClass = pFeatureWorkspace.OpenFeatureClass("Counties")
   
       Dim sFieldName as String
       sFieldName = "state_name"
   
       'Set pTableSort = New esriCore.TableSort  'ArcGIS 8.X
       Set pTableSort = New TableSort   'ArcGIS 9.X
   
       With pTableSort
         .Fields = sFieldName
         .Ascending(sFieldName) = True
         .CaseSensitive(sFieldName) = False
         Set .QueryFilter = Nothing
         Set .Table = pFeatureClass
       End With
       
       pTableSort.Sort Nothing
       
       Dim pFeatureCursor As IFeatureCursor
       Set pFeatureCursor = pTableSort.Rows
   
       Dim pDataStatistics As IDataStatistics
       Set pDataStatistics = New DataStatistics
       
       pDataStatistics.Field = sFieldName
       
       Set pDataStatistics.Cursor = pFeatureCursor
       
       Dim pEnumVar As IEnumVariantSimple
       Set pEnumVar = pDataStatistics.UniqueValues
   
       Dim value As Variant
       value = pEnumVar.Next
   
       Do Until IsEmpty(value)
           Debug.Print value
           value = pEnumVar.Next
       Loop