[GIS] Getting unique value records from a field

arcobjects

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

alt text

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…

Best Answer

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.
Related Question