English

How To: 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

The sample VB code below uses Counties data for the United States and retrieves a sorted list of unique values. The code prints the values to DEBUG for simplicity.

  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