Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Building a desktop application using C#.net and arcobjects. I'm trying to retrieve records from a shape file 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....

Thanks

share|improve this question
    
If you use a SQL Expression, you can group them. "SELECT Category, Completed, Count(*) AS Count FROM Table GROUP BY Category, Completed HAVING Count > 1" –  CrazyEnigma Jan 6 '11 at 20:35

5 Answers 5

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?

share|improve this answer
1  
I need this to work on an attribute table where I query it using the IQueryFilter interface. Can we use statements like this in arcobject programming ? If so please guide me on how to use SQL on arcobjects. –  user1506 Jan 6 '11 at 22:24
    
I don't think there's any way at all to get this to work within the confines of ArcObjects. You'll have to go through a more procedural route to do the same thing, unfortunately. –  Herb Jan 24 '11 at 16:27

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.
share|improve this answer

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;
}
share|improve this answer
1  
Kirk, I know this is meant to be just a sample, but releasing a RCW of the cursor object should ALWAYS occur in a finally section of a try-finally clause. –  Petr Krebs Jan 7 '11 at 10:30
    
Thanks petr, see edit. –  Kirk Kuykendall Jan 7 '11 at 15:05
    
All the confusion between what to do with arcobjects and what to do with SQL is what is referred to as the "impedance mismatch". Linq was developed specifically to address this. I think it would be helpful if Esri included a Linq-to-Feature provider. I've never worked through this walkthrough, but it is interesting that it uses TerraServer (a database of aerial imagery) as an example. msdn.microsoft.com/en-us/library/bb546158.aspx –  Kirk Kuykendall Jan 7 '11 at 15:17
    
I forgot to mention that Microsoft also needs to extent Linq to support spatial types, though there do appear to be workarounds blogs.u2u.be/diederik/post/2010/01/03/… –  Kirk Kuykendall Jan 7 '11 at 15:21
    
Along these lines, it would be nice to have a LINQ provider and a custom ORM to work with ESRI data. I've tried to build something like this, but I lack the ORM experience and did not go beyong the very simple pieces. –  George Jan 9 '11 at 18:31

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.

share|improve this answer
    
I would suggest this for sure. There is no need to query the data complexly with IdataStatistics. –  George Jan 9 '11 at 18:30
    
@George: I believe there is, in some cases. If you want to get decent performance on large datasets by delegating as much work to the database as possible, there is actually no other way. –  Petr Krebs Jan 12 '11 at 18:05

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/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.