[GIS] Writing the where clause in IQueryFilter

arcobjects

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.

The scenario is as follows.

I want to only retrieve records that are invalid and fall out side the below condition . The category field in the sample data should be matched with the category field in the "condition" and check the Limits in the "condition" with either "Limit-From" or "Limit-To" field. Either one of the "Limit-From" or "Limit-To" fields should match the limit in the condition. Also both the "Limit-From" and "Limit-To" fields can match the limits in the "Condition ".( when both match also that is not an error).

I have inserted a comments field for each record stating the reason for validity/invalidity. According to the sample data, as I want only invalid records, I want just record 6 and 7 as the output from the query.

Any help in writing the where clause in the query will be greatly appreciated. (I need only the invalid records. So in this case it should be just 6 and 7)

alt text

Best Answer

If the number of individual categories is fairly low (you have 3 now) something along those lines would be both simple and quite efficent:

WHERE NOT
(
  (LimitFrom > 130 and Category = 1)
  or (LimitTo > 130 and Category = 1)
  or (LimitFrom >= 101 and LimitFrom <= 130 and Category = 2)
  or (LimitTo >= 101 and LimitTo <= 130 and Category = 2)
  or (LimitFrom >= 91 and LimitFrom <= 100 and Category = 3)
  or (LimitTo >= 91 and LimitTo <= 100 and Category = 3)
)

Tested with your sample data on Oracle, should work virtually everywhere since it's standard SQL.

Related Solutions

[GIS] Getting unique value records from a field

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:

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

[GIS] Comparing two featureclasses using C#

Since you tagged this question with arcobjects and c#, and since not all geodatabases support the SQL IN with a subquery, the following untested code shows how you could do this with System.Collections.Hashtable.

private List<int> Diff(ITable table1, ITable table2, string idfldName)
{
    // return the oid of each row in table1 whose id doesn't appear in table2
    var outList = new List<int>();

    var ht1 = HashRows(table1, idfldName);
    var ht2 = HashRows(table2, idfldName);
    foreach (object key in ht1.Keys)
    {
        if (!ht2.ContainsKey(key))
            outList.Add((int)ht1[key]);
    }
    return outList;
}

private Hashtable HashRows(ITable table, string idField)
{
    Hashtable ht = new Hashtable();
    ICursor cur = table.Search(null, true);
    try
    {
        int idx = table.FindField(idField);
        if (idx == -1)
            throw new Exception("Field not found: " + idField);
        IRow row;
        while ((row = cur.NextRow()) != null)
        {
            if(!ht.ContainsKey(row.get_Value(idx)))
                ht.Add(row.get_Value(idx),row.OID);
            else
                throw new Exception("duplicate key encountered at OID = " + row.OID);
        }
    }
    catch
    {
        throw;
    }
    finally
    {
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cur);
    }
    return ht;
}

Best Answer

Related Solutions

[GIS] Getting unique value records from a field

[GIS] Comparing two featureclasses using C#

Related Question