7

I have a feature (point) with a field called 'Sequence'. The values in this column range from 1 to n. I can use IQueryFilter to get the first value, 1.

                    Dim pQry As IQueryFilter = New QueryFilter
                    pQry.WhereClause = """Seq"" = 1"
                    pCursor = pFC.Search(pQry, True)
                    If TypeOf pCursor Is IFeatureCursor Then
                        pFeat = pCursor.NextFeature
                        If TypeOf pFeat Is IFeature Then
                            fStartElevation = pFeat.Value(nElevationFld)
                        End If
                    End If

I cannot figure out how to use this method to select the MAX value, since it varies across features. Is it possible? I'm thinking I just need the correct syntax. I have tried a few variations of pQry.WhereClause = """MAX""Seq""" after doing some forum reading regarding aggregate functions in ArcGIS, but I haven't found a solution that works.

If this isn't possible (I figured it should operate similarly to SQL), is there another preferable method for performing this task? I am aware of Select By Attribute, but the syntax for this is tricky (examples online do not work in Arc10) and still requires the attribute table to be read.

EDIT: Here is a working solution using @George's suggestion based on IDataStatistics. I may just turn this into a small function that takes in a Featureclass and field name for the sake of re-usability.

                    pCursor = pFC.Search(Nothing, False)
                    If TypeOf pCursor Is IFeatureCursor Then
                        Dim pData As IDataStatistics = New DataStatisticsClass
                        pData.Field = "Sequence"
                        pData.Cursor = pCursor

                        Dim pStatResults As IStatisticsResults = pData.Statistics
                        Dim intMaxSequence As Integer = pStatResults.Maximum
                    End If
  • Strictly speaking of PGDB and FGDB; should be possible if you are querying "Access" personal geodatabase. File Geodatabase capabilities are very limited in this; you might have to loop through the cursor to find max, min, etc. – Jakub Sisak GeoGraphics Jul 25 '11 at 16:24
  • @Jakub - I came across that somewhere while researching this question. For my purposes I have to be prepared to support all geodatabase formats - so I think the cursor option (combined with IDataStatistics) is the way to go. – Radar Jul 25 '11 at 17:18
10

If you are only interested in the MAX value you can use the interface IDataStatistics. Using it you can get a simple statistical result, with min, max, mean, etc.

http://edndoc.esri.com/arcobjects/9.2/ComponentHelp/esriGeoDatabase/IDataStatistics_Example.htm

  • Great - this is perfect. I've updated the question with a working code snippet. Thanks! – Radar Jul 25 '11 at 18:20
0

If you just want the MIN or MAX of a column, the quickest way would be to use the IQueryFilter.PostfixClause to ORDER BY your column ascending or descending then just grab the first row from your cursor. By using IDataStatistics the computer will have to fetch all the rows from the DB, iterate through them and gather stats. Using the ORDER BY we can put all the work on the DB then just fetch a single row over the wire.

IRow queueRow = null;

//use ORDER BY to get the row with the minimum RequestID.
//queueTable is previously opened ITable.

var queryFilter = new QueryFilterClass
{
    SubFields = "*", 
    PostfixClause = string.Format("ORDER BY {0}", requestIdField),
    WhereClause = ""
};

var cursor = queueTable.Search(queryFilter, true);
queueRow = cursor.NextRow();

return queueRow;
0

You can do this with a subquery in the where clause. e.g on a featureclass named "MyFC" with a field named "ID"

pQry.WhereClause = "ID = (select MAX(ID) from MyFC)"

There are limitations on what you can do with sub queries. See http://desktop.arcgis.com/en/arcmap/10.3/map/working-with-layers/sql-reference-for-query-expressions-used-in-arcgis.htm#GUID-F5320DB5-4E54-4D49-BF31-A58F5379ED9F

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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