Querying geodatabase tables


Summary
The geodatabase application programming interface (API) provides many different ways to query tables and feature classes in the geodatabase. This topic discusses how to query these datasets using query filters, spatial filters, and QueryDefs.


Determining the query type

Three common interfaces for querying geodatabase objects are the IQueryFilter, ISpatialFilter, and IQueryDef interfaces. Each one has different capabilities, as shown in the following table:
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
Records include edits made in active edit session
True
True
False

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 is populated (with the IQueryFilter.SubFields property).
Query filters are common throughout the geodatabase API. They are used to create cursors and selection sets, 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 IQueryFilterDefinition2 interface, which allows the specification of the ORDER BY clause through the PostfixClause property and DISTINCT through the PrefixClause property. This functionality is only available when working with geodatabases. Other data sources do not 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:
[C#]
// Create the query filter.
IQueryFilter queryFilter = new QueryFilterClass();

// Select the fields to be returned—the name and address of the businesses.
queryFilter.SubFields = "NAME, ADDRESS";

// Set the filter to return only restaurants.
queryFilter.WhereClause = "TYPE = 'Restaurant'";

// Use the PostfixClause to alphabetically order the set by name.
IQueryFilterDefinition queryFilterDef = (IQueryFilterDefinition)queryFilter;
queryFilterDef.PostfixClause = "ORDER BY NAME";

// Output the returned names and addresses.
int nameIndex = table.FindField("NAME");
int addressIndex = table.FindField("ADDRESS");
using (ComReleaser comReleaser = new ComReleaser())
{
  ICursor cursor = table.Search(queryFilter, true);
  comReleaser.ManageLifetime(cursor);
  IRow row = null;
  while ((row = cursor.NextRow()) != null)
  {
    String name = Convert.ToString(row.get_Value(nameIndex));
    String address = Convert.ToString(row.get_Value(addressIndex));
    Console.WriteLine("{0} - {1}", name, address);
  }
}
[VB.NET]
' Create the query filter.
Dim queryFilter As IQueryFilter = New QueryFilterClass()

' Select the fields to be returned—the name and address of the businesses.
queryFilter.SubFields = "NAME, ADDRESS"

' Set the filter to return only restaurants.
queryFilter.WhereClause = "TYPE = 'Restaurant'"

' Use the PostfixClause to alphabetically order the set by name.
Dim queryFilterDef As IQueryFilterDefinition = CType(queryFilter, IQueryFilterDefinition)
queryFilterDef.PostfixClause = "ORDER BY NAME"

' Output the returned names and addresses.
Dim nameIndex As Integer = table.FindField("NAME")
Dim addressIndex As Integer = table.FindField("ADDRESS")
Using comReleaser As ComReleaser = New ComReleaser()
  Dim cursor As ICursor = table.Search(queryFilter, True)
  comReleaser.ManageLifetime(cursor)
  Dim row As IRow = cursor.NextRow()
  While Not row Is Nothing
    Dim name As String = Convert.ToString(row.Value(nameIndex))
    Dim address As String = Convert.ToString(row.Value(addressIndex))
    Console.WriteLine("{0} - {1}", name, address)
    row = cursor.NextRow()
  End While
End Using

ISpatialFilter interface

The ISpatialFilter interface is a type of query filter that includes 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, set the following properties:
  • ISpatialFilter.Geometry—The geometry to compare features with. The geometry object that is used must implement IRelationalOperator, which includes "high-level" geometries such as points, polylines, polygons, and geometry bags, but excludes "low-level" geometries such as lines, paths and rings.
  • ISpatialFilter.GeometryField—The name of the shape field in the queried feature class. 
  • ISpatialFilter.SpatialRel—The spatial relationship in which to test the query geometry and feature class.
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 is reduced to a regular query filter.
To allow custom spatial relationships to be defined as strings, use ISpatialFilter.SpatialRelDescription. For example, the CONTAINS relationship would be expressed as "TT*FFT***". To use this property, set the ISpatialFilter.SpatialRel property to esriSpatialRelRelation. For more information on these descriptions, click the link to the property description.
For spatial relationships that are not commutative, it is important to select the correct spatial relationship. For example, given a feature class of streets and an envelope as a query geometry, to find the streets within the envelope, use the esriSpatialRelIntersects or esriSpatialRelContains relationships, not the esriSpatialRelWithin relationship. The evaluation can be thought of as follows:
  • [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 streets within an envelope while excluding ramps, highways, and interstates:
[C#]
// Create the envelope and define its position.
IEnvelope envelope = new EnvelopeClass();
envelope.PutCoords(-84.4078, 33.7787, -84.3856, 33.7997);

// Create the spatial filter and set its spatial constraints.
ISpatialFilter spatialFilter = new SpatialFilterClass
{
  Geometry = envelope,
  GeometryField = featureClass.ShapeFieldName,
  SpatialRel = esriSpatialRelEnum.esriSpatialRelIntersects
};

// Set the attribute constraints and subfields.
// You want to exclude ramps, highways and interstates.
spatialFilter.WhereClause = "NAME <> 'Ramp' AND PRE_TYPE NOT IN ('Hwy', 'I')";
spatialFilter.SubFields = "NAME, TYPE";

// Execute the query, displaying the roads returned.
int nameIndex = featureClass.FindField("NAME");
int typeIndex = featureClass.FindField("TYPE");
using (ComReleaser comReleaser = new ComReleaser())
{
  IFeatureCursor featureCursor = featureClass.Search(spatialFilter, true);
  comReleaser.ManageLifetime(featureCursor);
  IFeature feature = null;
  while ((feature = featureCursor.NextFeature()) != null)
  {
    String roadName = Convert.ToString(feature.get_Value(nameIndex));
    String roadType = Convert.ToString(feature.get_Value(typeIndex));
    Console.WriteLine("Name: {0}, Type: {1}", roadName, roadType);
  }
}
[VB.NET]
' Create the envelope and define its position.
Dim envelope As IEnvelope = New EnvelopeClass()
envelope.PutCoords(-84.4078, 33.7787, -84.3856, 33.7997)

' Create the spatial filter and set its spatial constraints.
Dim spatialFilter As ISpatialFilter = New SpatialFilterClass With _
{ _
  .Geometry = envelope, _
  .GeometryField = featureClass.ShapeFieldName, _
  .SpatialRel = esriSpatialRelEnum.esriSpatialRelIntersects _
}

' Set the attribute constraints and subfields.
' You want to exclude ramps, highways and interstates.
spatialFilter.WhereClause = "NAME <> 'Ramp' AND PRE_TYPE NOT IN ('Hwy', 'I')"
spatialFilter.SubFields = "NAME, TYPE"

' Execute the query, displaying the roads returned.
Dim nameIndex As Integer = featureClass.FindField("NAME")
Dim typeIndex As Integer = featureClass.FindField("TYPE")
Using comReleaser As ComReleaser = New ComReleaser()
  Dim featureCursor As IFeatureCursor = featureClass.Search(spatialFilter, True)
  comReleaser.ManageLifetime(featureCursor)
  Dim feature As IFeature = featureCursor.NextFeature()
  While Not feature Is Nothing
    Dim roadName As String = Convert.ToString(feature.Value(nameIndex))
    Dim roadType As String = Convert.ToString(feature.Value(typeIndex))
    Console.WriteLine("Name: {0}, Type: {1}", roadName, roadType)
    feature = featureCursor.NextFeature()
  End While
End Using
If a geometry bag is being used as the filter's query geometry, create a spatial index for the geometry bag before being assigned to the geometry property. This can drastically increase the query's efficiency. The following code example shows how to do this:
[C#]
// Cast the bag to the ISpatialIndex interface.
ISpatialIndex spatialIndex = (ISpatialIndex)geometryBag;
spatialIndex.AllowIndexing = true;
spatialIndex.Invalidate();
[VB.NET]
' Cast the bag to the ISpatialIndex interface.
Dim spatialIndex As ISpatialIndex = CType(geometryBag, ISpatialIndex)
spatialIndex.AllowIndexing = True
spatialIndex.Invalidate()

IQueryDef interface

The IQueryDef and IQueryDef2 interfaces can be used to execute a database query on one or more tables or feature classes. As with query filters, QueryDefs allow WHERE clauses and subfields to be defined through the IQueryDef.WhereClause and IQueryDef.SubFields properties, and IQueryDef2.PrefixClause and IQueryDef2.PostfixClause allow prefix and postfix clauses to be applied to the query (for example, DISTINCT and ORDER BY). In addition to these, the IQueryDef.Tables property allows multiple tables to be joined. A cursor can be generated from a QueryDef by using the IQueryDef2.Evaluate2 method. See the following code example:
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 Joining data. In this topic, it is 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.
[C#]
// Create the QueryDef.
IQueryDef2 queryDef2 = (IQueryDef2)featureWorkspace.CreateQueryDef();

// Specify the table and fields to query.
queryDef2.Tables = "Cities";
queryDef2.SubFields = "Name, Pop1996";
queryDef2.PostfixClause = "ORDER BY Pop1996 DESC";


// Execute the query.
using (ComReleaser comReleaser = new ComReleaser())
{
  ICursor cursor = queryDef2.Evaluate2(true);
  comReleaser.ManageLifetime(cursor);
  int nameIndex = cursor.FindField("Name");
  int pop1996Index = cursor.FindField("Pop1996");
  IRow row = null;
  while ((row = cursor.NextRow()) != null)
  {
    String cityName = Convert.ToString(row.get_Value(nameIndex));
    int population = Convert.ToInt32(row.get_Value(pop1996Index));
    Console.WriteLine("{0}: {1}", cityName, population);
  }
}
[VB.NET]
' Create the QueryDef.
Dim queryDef2 As IQueryDef2 = CType(featureWorkspace.CreateQueryDef(), IQueryDef2)

' Specify the table and fields to query.
queryDef2.Tables = "Cities"
queryDef2.SubFields = "Name, Pop1996"
queryDef2.PostfixClause = "ORDER BY Pop1996 DESC"

' Execute the query.
Using comReleaser As ComReleaser = New ComReleaser()
  Dim cursor As ICursor = queryDef2.Evaluate2(True)
  comReleaser.ManageLifetime(cursor)
  Dim nameIndex As Integer = cursor.FindField("Name")
  Dim pop1996Index As Integer = cursor.FindField("Pop1996")
  Dim row As IRow = cursor.NextRow()
  While Not row Is Nothing
    Dim cityName As String = Convert.ToString(row.Value(nameIndex))
    Dim population As Integer = Convert.ToInt32(row.Value(pop1996Index))
    Console.WriteLine("{0}: {1}", cityName, population)
    row = cursor.NextRow()
  End While
End Using

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. After the object is cast to the IName interface, the IName.Open method can then be called, as shown in the following code example:
[C#]
// Create a reference to a TableQueryName object.
IQueryName2 queryName2 = new TableQueryNameClass();
queryName2.PrimaryKey = String.Empty;

// Specify the query definition.
queryName2.QueryDef = queryDef;

// Get a name object for the workspace.
IDataset dataset = (IDataset)workspace;
IWorkspaceName workspaceName = (IWorkspaceName)dataset.FullName;

// Cast the TableQueryName object to the IDatasetName interface and open it.
IDatasetName datasetName = (IDatasetName)queryName2;
datasetName.WorkspaceName = workspaceName;
datasetName.Name = tableName;
IName name = (IName)datasetName;

// Open the name object and get a reference to a table object.
ITable table = (ITable)name.Open();
[VB.NET]
' Create a reference to a TableQueryName object.
Dim queryName2 As IQueryName2 = New TableQueryNameClass()
queryName2.PrimaryKey = String.Empty

' Specify the query definition.
queryName2.QueryDef = queryDef

' Get a name object for the workspace.
Dim dataset As IDataset = CType(workspace, IDataset)
Dim workspaceName As IWorkspaceName = CType(dataset.FullName, IWorkspaceName)

' Cast the TableQueryName object to the IDatasetName interface and open it.
Dim datasetName As IDatasetName = CType(queryName2, IDatasetName)
datasetName.WorkspaceName = workspaceName
datasetName.Name = tableName
Dim name As IName = CType(datasetName, IName)

' Open the name object and get a reference to a table object.
Dim table As ITable = CType(name.Open(), ITable)

WHERE clauses

A WHERE clause is a component of a Structured Query Language (SQL) statement that defines attribute constraints on a query. WHERE clauses are used as properties by the IQueryFilter, ISpatialFilter, and IQueryDef interfaces, and by interfaces outside of the geodatabase API. WHERE clauses can consist of one to many expressions, linked by logical connectors (AND and OR). The following is the valid format for a WHERE clause expression:
  • [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

The following table shows predicates, their meaning, and examples of each:
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
The following are the two types of wildcards that can be used with the LIKE predicate: 
  • Single-character wildcard (underscore, _)
  • Multiple-character wildcard (percent sign, %)
Strings containing single-character wildcards evaluates to true if the wildcard corresponds to a single character in the comparison string, whereas strings containing multiple-character wildcards evaluates to true if the wildcard corresponds with zero to many characters in the comparison string. The following shows 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, use a question mark (?) as a single-character wildcard and an asterisk (*) as a multiple-character wildcard.
The correct way to represent a date, or date and time value in a WHERE clause depends on the queried data source. For more information, see the Dates section in the Desktop Help topic, SQL reference for query expressions used in ArcGIS.
Note that some data sources, particularly file-based sources, such as shapefile workspaces and coverage workspaces, do not support all predicates. Use the ISQLSyntax interface to determine whether a particular predicate is supported, as shown in the following code example:
[C#]
// Cast to the ISQLSyntax interface and get the supportedPredicates value.
ISQLSyntax sqlSyntax = (ISQLSyntax)workspace;
int supportedPredicates = sqlSyntax.GetSupportedPredicates();

// Cast the predicate value to an integer and use bitwise arithmetic to check for support.
int predicateValue = (int)esriSQLPredicates.esriSQL_BETWEEN;
int supportedValue = predicateValue & supportedPredicates;
Boolean isSupported = supportedValue > 0;
[VB.NET]
' Cast to the ISQLSyntax interface and get the supportedPredicates value.
Dim sqlSyntax As ISQLSyntax = CType(workspace, ISQLSyntax)
Dim supportedPredicates As Integer = sqlSyntax.GetSupportedPredicates()

' Cast the predicate value to an integer and use bitwise arithmetic to check for support.
Dim predicateValue As Integer = CInt(esriSQLPredicates.esriSQL_BETWEEN)
Dim supportedValue As Integer = predicateValue And supportedPredicates
Dim isSupported As Boolean = supportedValue > 0
Expressions can be combined through the use of the AND and OR operators. Expressions combined with an AND operator evaluates to true if all of the expressions are true, and those combined with an OR operator evaluates to true if any of the expressions are true. See the following example:
CITY_NAME LIKE 'New%' AND POPULATION >= 100000
In addition to the AND and OR binary operators, there also exists the NOT unary operator. NOT inverts the evaluation of an expression. See the following example:
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. Use two apostrophes, as shown in the following WHERE clause example:
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, precede the characters by escape characters, which can be defined after 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 is 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 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 following first example does) or a single value (as the following second example does).
The following WHERE clause can be used to filter the query results from a table of country data, to restrict the results to countries that are World Trade Organization (WTO) members:
COUNTRY_NAME IN (SELECT COUNTRY_NAME FROM WTO_COUNTRIES)
The following WHERE clause can be used to return all countries with a gross domestic product (GDP) greater than the worldwide average:
GDP > (SELECT AVG(GDP) FROM COUNTRIES)
Geodatabases support subqueries, but file-based data sources do not. For more information, see the previously mentioned topic, SQL reference.

Functions

The following four types of functions can be used with WHERE clauses:
  • Date
  • Numeric
  • String
  • Miscellaneous (for example, CAST and CONVERT)
Function names and arguments can vary by data source; therefore, consult the specific DBMS documentation. A full list of available functions can be found in the previously mentioned Desktop Help topic, SQL reference. The following functions are supported by file geodatabases, but might not be supported by other workspaces.
The following examples show the four function types used in WHERE clauses:
  • Date—The records from a query using this WHERE clause are those with the year component of the START_DATE date (or date and time) field equaling 2000:
EXTRACT(YEAR FROM START_DATE) = 2000
  • Numeric—Given a feature class with a directional attribute expressed in degrees, this returns all the features with a northerly direction (between 45 and 135 degrees, 405 and 495 degrees, and so on):
SIN(Direction / 57.296) > 0.707
  • String—Given a country table with inconsistent character casing (for example, 'NORWAY' and 'Norway'), this returns all rows where the country name started with "Republic Of", regardless of the case used:
UPPER(COUNTRY_NAME) LIKE 'REPUBLIC OF %'
  • Miscellaneous—Given a feature class of highways with a text field called NAME, this takes the final three characters (for example, the "104" from "Highway 104"), converts them to an integer, and checks if the highway is a "100-series" highway:
CAST(SUBSTRING(NAME, CHAR_LENGTH(NAME) - 2, 3) AS INT) BETWEEN 100 AND 199
The ISQLSyntax interface can be used to find the correct function name for a workspace, as the following code example shows:
[C#]
// Cast to the ISQLSyntax interface and get the correct function name.
ISQLSyntax sqlSyntax = (ISQLSyntax)workspace;
String functionName = sqlSyntax.GetFunctionName(esriSQLFunctionName.esriSQL_UPPER);
[VB.NET]
' Cast to the ISQLSyntax interface and get the correct function name.
Dim sqlSyntax As ISQLSyntax = CType(workspace, ISQLSyntax)
Dim functionName As String = sqlSyntax.GetFunctionName(esriSQLFunctionName.esriSQL_UPPER)

SubFields

A subfields expression is the segment of a SQL statement that specifies which fields of the table or tables are 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 are returned by the query. Specifying subfields before executing a query can result in improved performance, as the amount of retrieved data can be significantly reduced. SubFields expressions should contain the names of the applicable fields, delimited by commas. See the following example:
FID, Shape, STATE_NAME, POPULATION
When a cursor is created with a subfields expression, its rows might include extra fields in addition to those specified. A common example of this is when creating an update cursor, the ObjectID field is required and will be included regardless of whether or not it is listed in the subfields expression.


See Also:

Joining data
SQL reference for query expressions used in ArcGIS




To use the code in this topic, reference the following assemblies in your Visual Studio project. In the code files, you will need using (C#) or Imports (VB .NET) directives for the corresponding namespaces (given in parenthesis below if different from the assembly name):
Development licensing Deployment licensing
ArcGIS for Desktop Basic ArcGIS for Desktop Basic
ArcGIS for Desktop Standard ArcGIS for Desktop Standard
ArcGIS for Desktop Advanced ArcGIS for Desktop Advanced
Engine Developer Kit Engine