How to query geodatabase objects


Summary The geodatabase application programming interface (API) allows for many different ways to query objects in the geodatabase. Using the IQueryDef, IQueryFilter, and ISpatialFilter interfaces, geodatabase classes—such as tables and feature classes—can be queried. This article briefly discusses how to query specific geodatabase objects, such as table and feature classes, using both QueryDefs and QueryFilters.

The code samples in this article were based on the Atlanta file geodatabase, installed in the C:\Program Files\ArcGIS\DeveloperKit10.0\SamplesNET\data directory by default.

Which Type of Query Should I Use?

Three common interfaces for querying geodatabase objects are the IQueryFilter, ISpatialFilter, and IQueryDef interfaces.  Each one has different capabilities, as the following table shows:
Requirement
IQueryFilter
ISpatialFilter
IQueryDef
Apply attribute constraints
True
True
True
Apply spatial constraints
False
True
False
Query results contain fields from multiple tables
False
False
True
Query results returned as a cursor
True
True
True
RecordSet objects can be created from results
True
True
True
Returned records can be edited
True
True
False

The IQueryFilter interface

The IQueryFilter interface is the primary interface for using query filters. A query filter is used to restrict the records retrieved from the database during a query (with the IQueryFilter.WhereClause property) and to specify which fields of the query result will be populated (with the IQueryFilter.SubFields property).  Query filters are common throughout the Geodatabase API:  they are used to create cursors and selection sets, to sort subsets of tables, during row count operations of tables and feature classes, when building relationship query tables, when loading bulk data, and in several other operations.
Query filters implement the IQueryFilterDefinition interface, which allows the specification of ORDER BY and GROUP BY clauses through the IQueryFilterDefinition.PostfixClause property.  This functionality is only available in SDE and Access geodatabases; file geodatabases and file-based data sources do not currently support it.
The following code example shows how to use a query filter to create a cursor that returns only restaurants from a table of businesses:
[Java]
static ICursor getRestaurants(ITable table)throws Exception{
    // Create the query filter.
    IQueryFilter queryFilter = new QueryFilter();
    // Select the fields to be returned - the name and address of the businesses.
    queryFilter.setSubFields("NAME, ADDRESS");
    // Set the filter to return only restaurants.
    queryFilter.setWhereClause("TYPE = 'Restaurant'");
    // Use the PostfixClause to alphabetically order the set by name.
    IQueryFilterDefinition queryFilterDef = (IQueryFilterDefinition)queryFilter;
    queryFilterDef.setPostfixClause("ORDER BY NAME");
    ICursor cursor = table.ITable_search(queryFilter, true);
    return cursor;
}
 

The ISpatialFilter interface

The ISpatialFilter interface is a type of query filter that includes both spatial and attribute constraints. A spatial filter has a single query geometry with which the features in the feature class will be tested. When defining a spatial filter, you must set the following properties:
  • ISpatialFilter.Geometry: The geometry to compare features with.  The geometry object used must implement IRelationalOperator, which includes "high-level" geometries such as points, polylines, and polygons, but excludes "low-level" geometries such as lines, paths and rings.
  • ISpatialFilter.GeometryField:  The name of the shape field in the feature class being queried. 
  • ISpatialFilter.SpatialRel:  The spatial relationship to test the query geometry and feature class with.
ISpatialFilter.SpatialRel requires a value from the esriSpatialRelEnum enumeration to define a spatial relationship.  Examples of these include esriSpatialRelIntersects, esriSpatialRelTouches, esriSpatialRelWithin and esriSpatialRelContains.  If the esriSpatialRelUndefined value is used, the spatial filter will be reduced to a regular query filter.
ISpatialFilter.SpatialRelDescription allows custom spatial relationships to be defined as strings.  For example, the CONTAINS relationship would be expressed as "TT*FFT***".  To use this property, the ISpatialFilter.SpatialRel property should be set to esriSpatialRelRelation.  For more information on these descriptions, follow the link to the property description.
For spatial relationships that are not commutative, it is important to select the correct spatial relationship.  For example, if you have a feature class of streets, and an envelope as a query geometry, to find the streets within the envelope, the esriSpatialRelIntersects or esriSpatialRelContains relationships should be used, not the esriSpatialRelWithin relationship.  The evaluation can be thought of in this way:
    [query_geometry] [spatial_relationship] [feature]
Or, "Does this envelope contain this street?"
The following code example demonstrates how to use a spatial filter to query a streets feature class, finding all of the streets within an envelope while excluding ramps, highways, and interstates:
[Java]
static IFeatureCursor getStreetsInEnvelope(IFeatureClass featureClass)throws
    Exception{
    // Create the envelope and define its position.
    IEnvelope envelope = new Envelope();
    envelope.putCoords( - 84.4078, 33.7787,  - 84.3856, 33.7997);
    // Create the spatial filter and set its spatial constraints.
    ISpatialFilter spatialFilter = new SpatialFilter();
    spatialFilter.setGeometryByRef(envelope);
    spatialFilter.setGeometryField(featureClass.getShapeFieldName());
    spatialFilter.setSpatialRel(esriSpatialRelEnum.esriSpatialRelIntersects);
    // Set the attribute constraints and subfields.
    // We want to exclude ramps, highways and interstates.
    spatialFilter.setWhereClause("NAME <> 'Ramp' AND PRE_TYPE NOT IN ('Hwy', 'I')");
    spatialFilter.setSubFields("NAME, TYPE");
    // Execute the query.
    IFeatureCursor featureCursor = featureClass.search(spatialFilter, true);
    return featureCursor;
}

The IQueryDef interface

The IQueryDef interface represents a database query on one or more tables or feature classes. A QueryDef must be built and executed to return results to the application.  As with query filters, QueryDefs allow where clauses and subfields to be defined through the  IQueryDef.WhereClause and IQueryDef.SubFields properties.  In addition to these, the IQueryDef.Tables allows multiple tables to be joined.  A cursor can be generated from a QueryDef by using the IQueryDef.Evaluate method.
Often, QueryDefs are used to join tables with the assurance a join query will be executed in the underlying database management system (DBMS). For more information on joining geodatabase objects, see How to join data.  Within this article, it will be assumed that only one table in being used in a QueryDef, but what is important to remember when using QueryDefs is that when multiple tables are being used in a geodatabase query, field names in subfields and where clauses must be fully qualified.
Also, unlike Row objects returned by cursors, those returned by a QueryDef are read-only and do not reference a parent table.
[Java]
// For example, tables = "customers"
// subFields = "NAME, ADDRESS"
// whereClause = "ADDRESS LIKE '%CENTER%'"
public ICursor useQueryDef(IFeatureWorkspace featureWorkspace, String tables, String
    subFields, String whereClause)throws Exception{
    // Create the query definition.
    IQueryDef queryDef = featureWorkspace.createQueryDef();
    // Provide a list of table(s) to join.
    queryDef.setTables(tables);
    // Declare the subfields to retrieve.
    queryDef.setSubFields(subFields);
    // Assign a where clause to filter the results.
    queryDef.setWhereClause(whereClause);
    // Evaluate queryDef to execute a database query and return a cursor to the application.
    ICursor cursor = queryDef.evaluate();
    return cursor;
}
 
The IQueryName2 interface
Along with creating cursors, a QueryDef can be used to generate a virtual table or feature class.  By using the IQueryName2 interface on a co-created table query name object, a QueryDef can be specified.  Following this, the name of the virtual table and the workspace it will be created in must be specified through the IDatasetName.Name and IDatasetName.WorkspaceName interfaces.  The IDatasetName.Open method can then be called, as shown in the following code example:
[Java]
public ITable createQueryTable(IWorkspace workspace, IQueryDef queryDef, String
    tableName)throws Exception{
    // Create a reference to a TableQueryName object.
    IQueryName2 queryName2 = new TableQueryName();
    queryName2.setPrimaryKey("");
    // Specify the query definition.
    queryName2.setQueryDef(queryDef);
    // Get a name object for the workspace.
    IDataset dataset = (IDataset)workspace;
    IWorkspaceName workspaceName = (IWorkspaceName)dataset.getFullName();
    // Cast the TableQueryName object to the IDatasetName interface and open it.
    IDatasetName datasetName = (IDatasetName)queryName2;
    datasetName.setWorkspaceNameByRef(workspaceName);
    datasetName.setName(tableName);
    IName name = (IName)datasetName;
    // Open the name object and get a reference to a table object.
    ITable table = (ITable)name.open();
    return table;
}

Where Clauses

A where clause is a component of a SQL statement that defines attribute constraints on a query.  Where clauses are used as properties by the IQueryFilter, ISpatialFilter, and IQueryDef interfaces, as well as by interfaces outside of the Geodatabase API.  A where clause can consist of one to many expressions, linked by logical connectors (AND and OR).  The valid format for a where clause expression is:
    [operand_1] [predicate] [operand_2]
Operands can consist of a field name from a table, a numeric constant, a character string, a simple arithmetic expression, a function call, or a subquery.
Predicates
Predicate
Meaning
Example
=
Equals
TYPE = 3
<>
Is not equal to
PROVINCE <> 'NS'
>=
Is greater than or equal to
POPULATION >= 10000
<=
Is less than or equal to
AVG_TEMP <= 25
>
Is greater than
HEIGHT > 10
<
Is less than
SPD_LIMIT < 65
[NOT] BETWEEN
Is between a minimum and maximum value
DIAMETER BETWEEN 5 AND 10
[NOT] IN
Is in a list or the results of a subquery
TYPE IN ('City', 'Town')
[NOT] EXISTS
Checks a subquery for results
EXISTS (SELECT * FROM PARCELS WHERE TYPE='RES')
[NOT] LIKE
Matches a string pattern
CITY_NAME LIKE 'Montr_al'
IS [NOT] NULL
Is value NULL
WEBSITE IS NULL
Two types of wildcards can be used with the LIKE predicate:  the single-character wildcard (an underscore: _) and the multiple-character wildcard (a percent sign: %).  Strings containing single-character wildcards will evaluate to true if the wildcard corresponds to a single character in the comparison string, whereas strings containing multiple-character wildcards will evaluate to true if the wildcard corresponds with zero to many characters in the comparison string.  Some example statements:
  • 'Belmont' LIKE '%mont'   -- evaluates to true
  • 'Belmont' LIKE '%elmont%'  -- evaluates to true
  • 'Belmont' LIKE 'Belmont_'   -- evaluates to false
  • 'Belmont' LIKE 'Bel_ont'   -- evaluates to true
 
When using an Access database, a question mark (?) should be used as a single-character wildcard, and an asterisk (*) should be used as a multiple-character wildcard.
The proper way to represent a date or date/time value in a where clause depends on the data source being queried.  For more about this, see the Dates subsection of the Desktop help article, SQL Reference.
Note that some data sources, particularly file-based sources such as shapefile workspaces and coverage workspaces, do not support all predicates.  The ISQLSyntax interface can be used to determine whether a particular predicate is supported, as shown in the following code:
[Java]
static boolean isPredicateSupported(IWorkspace workspace, int predicate)throws
    Exception{
    // Cast to the ISQLSyntax interface and get the supportedPredicates value.
    ISQLSyntax sqlSyntax = (ISQLSyntax)workspace;
    int supportedPredicates = sqlSyntax.getSupportedPredicates();
    //use bitwise arithmetic to check for support.
    int predicateValue = predicate;
    int supportedValue = predicateValue & supportedPredicates;
    boolean isSupported = supportedValue > 0;
    return isSupported;
}
Expressions can be combined through the use of the AND and OR operators.  Expressions combined with an AND operator will evaluate to true if all of the expressions are true, and those combined with an OR operator will evaluate to true if any of the expressions are true.  An example of this would be:
CITY_NAME LIKE 'New%' AND POPULATION >= 100000
In addition to the AND and OR binary operators, there also exists the NOT unary operator.  NOT will invert the evaluation of an expression.  An example of this could be:
NOT (PROV = 'NS' AND TERM = 'City')
When constructing a where clause, a special case to be aware of is attempting to match a string with an apostrophe, as an apostrophe is used as a string delimiter.  Two apostrophes should be used, as shown in the following where clause:
CITY_NAME = 'St. John''s'
Another special case is attempting to match a string that contains a wildcard character.  For example, trying to find all strings containing an underscore.  To search for strings containing wildcard characters, the characters must be preceded by escape characters, which can be defined following the string.  The following is an example of searching for all characters containing an underscore:
NAME LIKE '%$_%' ESCAPE '$'
The escape character can be any single character, but should be a character that is not found in the data.  For example, a hyphen would be a poor choice of an escape character when searching city names, as hyphens are occasionally found in city names.
If a field used in a where clause shares its name with a DBMS keyword, the field name should be delimited using quotation marks.  See the following example:
"DESC" LIKE '%cold%'
Strings are delimited with square brackets ('[' and ']') in Access.
Subqueries
A subquery is a query nested in the where clause of another query.  This often involves querying another table in the geodatabase or using a function.  Subqueries must return a single value per row (as the first example does) or simply a single value (as the second example does).
COUNTRY_NAME IN (SELECT COUNTRY_NAME FROM WTO_COUNTRIES)
This where clause could be used to filter the query results from a table of country data, to restrict the results to countries that are WTO members.
GDP > (SELECT AVG(GDP) FROM COUNTRIES)
This where clause could be used to return all countries with a GDP greater than the worldwide average.
Geodatabases support subqueries, but file-based data sources do not.  For more details, see the Desktop help article, SQL Reference.
Functions
Four types of functions can be used with where clauses: date functions, numeric functions, string functions, and miscellaneous functions (for example, CAST and CONVERT).
Function names and arguments may vary by data source, so consulting the specific DBMS' documentation is recommended.  A full list of available functions can be found in the Desktop Help article, SQL Reference.  The functions below are supported by file geodatabases, but may not be supported by other workspaces.
The examples below show the four types being used in where clauses:
EXTRACT(YEAR FROM START_DATE) = 2000
The records from a query using this where clause would be those with the year component of the START_DATE date (or date/time) field equaling 2000.
SIN(Direction / 57.296) > 0.707
Given a feature class with a directional attribute expressed in degrees, this would return all the features with a northerly direction (between 45 and 135 degrees, 405 and 495 degrees, etc.)
UPPER(COUNTRY_NAME) LIKE 'REPUBLIC OF %'
Given a country table with inconsistent character casing (e.g. 'NORWAY' and 'Norway'), this would return all rows where the country name started with "Republic Of", regardless of the case used.
CAST(SUBSTRING(NAME, CHAR_LENGTH(NAME) - 2, 3) AS INT) BETWEEN 100 AND 199
Given a feature class of highways with a text field called NAME, this would take the final 3 characters (for example, the "104" from "Highway 104"), convert them to an integer, and check if the highway is a "100-series" highway.
The ISQLSyntax interface can be used to find the correct function name for a workspace, as the following example shows:
[Java]
static String functionNameForWorkspace(IWorkspace workspace, int sqlFunctionName)
    throws Exception{
    // Cast to the ISQLSyntax interface and get the correct function name.
    ISQLSyntax sqlSyntax = (ISQLSyntax)workspace;
    String functionName = sqlSyntax.getFunctionName(sqlFunctionName);
    // If functionName is null, the function is not supported and an exception should be thrown.
    if (functionName == null){
        throw new Exception("SQL Function Not Supported.");
    }
    return functionName;
}

SubFields

A subfields expression is the segment of a SQL statement that specifies which fields of the table(s) will be returned by the query.  In the Geodatabase API, the IQueryFilter, ISpatialFilter, and IQueryDef interfaces have SubFields properties.
SubFields properties are initialized as "*", meaning all fields will be returned by the query.  Specifying subfields prior to executing a query can result in improved performance, as the amount of data retrieved can be significantly reduced.  SubFields expressions should contain the names of the desired fields, delimited by commas.  An example would be:
FID, Shape, STATE_NAME, POPULATION
When a cursor is created with a subfields expression, its rows may include extra fields in addition to those specified.  A common example of this is when creating an update cursor, the object ID field is required and will be included regardless of whether or not it's listed in the subfields expression.


See Also:

How to join data




Development licensing Deployment licensing
ArcView ArcView
ArcEditor ArcEditor
ArcInfo ArcInfo
Engine Developer Kit Engine Runtime