Getting unique value records from a field

Question

Building a desktop application using C#.net and ArcObjects. I'm trying to retrieve records from a shapefile using IQueryFilter. Having trouble writing the where clause in my scenario and the applicable logic.

Here is the scenario:

I have a 'Category' and "Completed" field, (both are of type string). What I need to do is, for a given category to see whether all the values in the completed field are unique. If values are not unique I need to display those records out.

Sample data

In the above example Category B and C does not have unique values. So all these records for B and C should be displayed.

I suppose a using an IQueryFilter interface should work. But puzzled on how I can achieve this by using a correct where clause. (I just need to get where the combination of Category and Completed fields are not unique)

Please help...

Answer 1

You are hitting the limits of querying data using ArcObjects. ArcObjects will not let you write atribrary query using IQueryFilter, you are limited to only very simple queries.

I will not try to compose the query for you since others have already provided some answers. But, let me outline more complex querying possibilities in ArcObjects, even though they are not directly related to your particular scenario. You or someone else might find this information useful.

• More complex queries involving table joins can be performed using query defs, i.e. the IQueryDef interface. Refence to this interface can be obtained by calling IFeatureWorkspace.CreateQueryDef.

• If you need ORDER BY, GROUP BY on query filters, you can use IQueryFilterDefinition.PostfixClause. However, you will not be able to, for example, retrieve the count of rows within a group, because you cannot put expressions like COUNT(*) into the query filter's SubFields. It doesn't work because there is not any field named COUNT(*) in the table you are querying. You also cannot put a HAVING expression into the query filter's WhereClause. In real world applications, this effectively renders IQueryFilterDefinition useless for any grouping purposes.

• At ArcGIS 10, you can use IQueryFilterDefinition2.PrefixClause to specify clauses like DISTINCT.

• You can overcome some of the difficulties with query filters and GROUP BY with query defs, but only in ArcGIS 10. There is a new interface IQueryDef2 which lets you specify both PrefixClause and PostfixClause on query defs. Query defs do not force you to query only for fields which actually exist on a table, so you are able to compose queries like, say, SELECT A, COUNT(*) FROM TABLE GROUP BY A.

Now, you probably understand that none of this information is particularly helpful in your solution. There are basically few things which you CAN do:

1. Examining the table in a single query, storing data in intermediate data structures (lists, dictionaries) in order to identify groups and records within these groups. This could be quite memory intensive if you have lots of data.
2. Break down your problem into multiple queries and execute them one by one. This could also affect performance big time, but it might not be a problem if the amount of data is low.
3. Create a database view (using queries from the other answers, for example) and then query it using ArcObjects. You'll be able to open views just like any other tables and query them using both IQueryFilter or IQueryDef. This has the benefit of making use of existing database capabilities, but is a pain if you have limited database administration access. Works only on SDE geodatabases and your data must not be versioned. Otherwise, you are out of luck.

Answer 2

What petr k. has said is correct, But I'll like to expand it further.

I would do this by having multiple Queries.

You can get Distinct values from IDataStatistics.UniqueValues Property. So I would first get the Unique values from the Category field.

I would then loop on that and get the Uniquevaluecount for the Completed Field, (while getting the cursor with only those features with the particular Category)

This way you can find out which category has more than one Unique value.

Answer 3

There's got to be a better way than what I'm about to post - someone cleverer in the charms of SQL can undoubtedly fix this up:

SELECT * FROM table WHERE category IN (
  SELECT category FROM (
    SELECT category, COUNT(*) AS c2 FROM (
      SELECT category, completed, COUNT(*) AS c1 FROM table
      GROUP BY category, completed
    ) AS t1 
    GROUP BY category
  HAVING COUNT(*) > 1
  ) AS t2 
)

This does have the intended result of displaying all the records of B & C, since they have multiple values for "completed", but it's at the expense of being nearly incomprehensible.

B   No
C   Yes
B   No
B   Yes
C   No
C   Yes

If you wanted to make it less awful, you could remove the outer query, which would give you the records back that were failing, and their count.

I've tested the above on SQL Server, for what it's worth, but not Oracle. And it almost certainly won't work in Access. Any takers for simplifying this?

Answer 4

Since you asked for C#, below is a way to do this with System.Collections.Generic. I haven't tested it.

public void Test()
{
    try
    {
        var fLayer = ArcMap.Document.Maps.get_Item(0).get_Layer(0) as IFeatureLayer;
        var categories = Categorize((ITable)fLayer.FeatureClass, "Category", "Completed");
        foreach (KeyValuePair<string, List<string>> kvp in categories)
        {
            if (kvp.Value.Count > 1)
                Debug.Print("Category {0} has {1} unique values", kvp.Key, kvp.Value.Count);
        }
    }
    catch (Exception ex)
    {
        Debug.Print(ex.Message);
    }
}

public Dictionary<string, List<string>> Categorize(ITable table, string categoryFld, string otherFld)
{
    var outList = new Dictionary<string, List<string>>();
    IQueryFilter qf = new QueryFilter();
    qf.SubFields = String.Format("{0},{1}", categoryFld, otherFld);

    int idxCat = FindField(table,categoryFld);
    int idxOther = FindField(table,otherFld);
    ICursor cur = null;
    // **edited to release cursor in a finally block**
    try
    {
        cur = table.Search(qf, true);
        IRow row;
        while ((row = cur.NextRow()) != null)
        {
            string category = GetStrVal(row, idxCat);
            string other = GetStrVal(row, idxOther);
            if (!outList.ContainsKey(category))
                outList.Add(category, new List<string>());
            if (!outList[category].Contains(other))
                outList[category].Add(other);
        }
    }
    catch
    {
        throw;
    }
    finally
    {
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cur);
    }
    return outList;
}

public static string GetStrVal(IRow row, int idx)
{
    return row.get_Value(idx) is DBNull ? "<Null>" : row.get_Value(idx).ToString();
}

public static int FindField(ITable table, string fldName)
{
    int idx = table.FindField(fldName);
    if(idx == -1)
        throw new Exception(String.Format("field {0} not found on table {1}",
            fldName,((IDataset)table).Name));
    return idx;
}

Answer 5

It's probably impossible to write a LINQ provider, but here's a custom ORM for geodatabase. It works with anything that implements IFeatureClass or ITable.

http://jshirota.github.com/Earthworm/