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.

I have a ListBox listing the unique values of a column in a feature class; the user can select any number of values and click a button to then zoom to the set of features matching those values. A function builds a WHERE clause that packs the selected values into an IN statement.

This works great when the number of selected values is <= 1000, but if there are over 1000 then no rows are returned. This appears to be an Oracle limitation (the issue does not occur on shapefiles or file geodatabase feature classes), for which some Oracle-specific workarounds are described in this StackOverflow question, but I want to make this function database-agnostic so that it works with shapefiles and file geodatabases as well as SDE geodatabases.

Can anyone suggest any alternative workflows that are 1) implemented in ArcObjects; and 2) database-agnostic; to using an IN statement with 1000+ values? I had thought about creating an in-memory table consisting of the selected values and joining against it but have not attempted this yet, pending any better ideas that may pop up here! If you know of the specific interfaces I should be looking at, that would be very helpful as well.

share|improve this question
    
The join is a good solution. Otherwise, if the total number of values is less than 2000, you can negate a selection of the unchosen values. –  whuber Jul 7 '11 at 18:54
add comment

2 Answers

up vote 3 down vote accepted

Depending on performance requirements, you might consider loading a dictionary of objectid lists keyed by unique attribute at startup. Populate your picklist from the dictionary keys, then when the user picks from the list you would call IGeodatabaseBridge2.GetFeatures(), which has no limit AFAIK on the size of the objectID array passed to it.

Here's the code to load the dictionary:

public Dictionary<string, List<int>> GetUnique(IFeatureClass fc, string fldName)
{
    var outDict = new Dictionary<string, List<int>>();
    int idx = fc.Fields.FindField(fldName);
    if (idx == -1)
        throw new Exception(String.Format("field {0} not found on {1}", fldName, ((IDataset)fc).Name));

    IFeatureCursor fCur = null;
    IFeature feat;
    try
    {
        var qf = new QueryFilterClass();
        qf.SubFields = fc.OIDFieldName + "," + fldName; // updated per comment
        fCur = fc.Search(qf, true);
        while ((feat = fCur.NextFeature()) != null)
        {
            string key = feat.get_Value(idx) is DBNull ? "<Null>" : feat.get_Value(idx).ToString();
            if (!outDict.ContainsKey(key))
                outDict.Add(key, new List<int>());
            outDict[key].Add(feat.OID);
        }
    }
    catch
    {
        throw;
    }
    finally
    {
        if(fCur != null)
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(fCur);
    }
    return outDict;
}
share|improve this answer
    
+1 Yeah, the use of GetFeatures is definitely a good idea if applicable. I wonder what the SQL query the SDE server issues to the database looks like, never occured to me to trace it. –  Petr Krebs Jul 7 '11 at 19:35
    
This looks like a promising solution. It seems this basically puts most of the heavy database queries up front, making the subsequent queries, which would likely happen more often (each time the user clicks the button vs. when the list is first populated) much lighter since it is going off the inherently-indexed OID column instead of a possibly non-indexed value column. –  blah238 Jul 7 '11 at 19:36
    
Since you are populating your list of unique values already, this might add only a bit more time to that step. –  Kirk Kuykendall Jul 7 '11 at 19:44
    
Yup, this worked out very nicely, thanks Kirk! –  blah238 Jul 7 '11 at 23:16
    
One little adjustment -- I needed to add the OIDFieldName to the QueryFilter.SubFields property otherwise I wouldn't get OIDs on geodatabase feature classes (shapefiles don't have that issue for some reason). –  blah238 Jul 8 '11 at 1:03
show 1 more comment

Breaking down the set into multiple sets at the SQL level is, in my opinion, a pragmatic solution, as well as far easier and more performant than any other approach involving any kind of intermediate in-memory storage.

If you break it down as follows:

COL in (1, 2, 3, ..., 1000) OR 
COL in (1001, 1002, 1003, ...,2000)

you will meet your requirement of database indifference since this will work across all the database engines ArcObjects are capable of working with. Introducing such logic will of course mean there is some kind of inherent implementation-level knowledge in your code, but it's a small price to pay compared to other solutions.

share|improve this answer
    
+1 Good idea. Could you explain why you think the composite 'in' statement would perform better than the join solution? It's plausible that 'in' and the in-memory join would work similarly under the hood (via a balanced tree, for instance), implying that multiple 'in's would take proportionately longer than a single join. –  whuber Jul 7 '11 at 19:07
    
Sorry, no theory under my sleeve to back it up here, just mere experience. The key rationale behind this is that it's always better idea to offload data-related heavy lifting to the database. Databases are good with dealing with data, I strongly believe it is THE place where such work is to be done, not the end application. This is a strong opinion on my part not only from performance standpoint, but even more importantly design-wise. In real-world software design, it's about managing complexity and going another way in this case gives no to very little advantage. –  Petr Krebs Jul 7 '11 at 19:26
1  
Besides, the optimizations database engines carry out in these common scenarios (if properly indexed and configured) will pretty much always do better job than you or I will ever get close to. –  Petr Krebs Jul 7 '11 at 19:27
    
I completely agree. But the join (to the "in-memory table") is implemented by the database, right? Or have I misunderstood the O.P.'s suggestion? –  whuber Jul 7 '11 at 21:28
    
No, the join would have been to an ArcObjects in-memory table. Sorry for the confusion. –  blah238 Jul 7 '11 at 21:43
add comment

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.