Querying geodatabase tables

In this topic

• Determining the query type
• IQueryFilter interface
• ISpatialFilter interface
• IQueryDef interface
  • IQueryName2 interface
• WHERE clauses
  • Predicates
  • Subqueries
  • Functions
• SubFields

Determining the query type

IQueryFilter interface

// 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);
    }
}

' 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

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

// 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);
    }
}

' 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

// Cast the bag to the ISpatialIndex interface.
ISpatialIndex spatialIndex=(ISpatialIndex)geometryBag;
spatialIndex.AllowIndexing=true;
spatialIndex.Invalidate();

' Cast the bag to the ISpatialIndex interface.
Dim spatialIndex As ISpatialIndex=CType(geometryBag, ISpatialIndex)
spatialIndex.AllowIndexing=True
spatialIndex.Invalidate()

IQueryDef interface

// 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);
    }
}

' 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

// 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();

' 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

• [operand_1] [predicate] [operand_2]

Predicates

• Single-character wildcard (underscore, _)
• Multiple-character wildcard (percent sign, %)

• '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)

// 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;

' 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

Subqueries

Functions

• Date
• Numeric
• String
• Miscellaneous (for example, CAST and CONVERT)

• 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:
  

• 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):
  

• 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:
  

• 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 to the ISQLSyntax interface and get the correct function name.
ISQLSyntax sqlSyntax=(ISQLSyntax)workspace;
String functionName=sqlSyntax.GetFunctionName(esriSQLFunctionName.esriSQL_UPPER);

' 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

See Also:

• ESRI.ArcGIS.Geodatabase
• ESRI.ArcGIS.Geometry
• ESRI.ArcGIS.System (ESRI.ArcGIS.esriSystem)