How to search a table

Summary
This topic describes how to search a Table by using a Structured Query Language (SQL) statement for an attribute query or using a geometry for a spatial query. Example scenarios are provided to demonstrate how to use the Search and SearchByDistance methods. The Filter class is also used to specify search criteria and tips are provided on optimizing query performance.

In this topic

To use the code in this topic, add references to ESRI.ArcGISExplorer.dll and ESRI.ArcGISExplorer.Application.dll. Also add the following namespace references via using (C#) or Imports (VB .NET) statements:
  • ESRI.ArcGISExplorer.Data
  • ESRI.ArcGISExplorer.Geometry
  • ESRI.ArcGISExplorer.Mapping
  • ESRI.ArcGISExplorer.Application

Attribute queries

An attribute query returns the rows from a table, which satisfies a specified search condition. They can be performed on non spatial or a spatially enabled tables, and each consists of a SQL WHERE clause statement. SELECT and FROM clauses that would be specified in a database "select" statement are not required because they are inherent to a search given that a query acts on a specific table instance. 
Do the following steps to execute an attribute query:
  1. Create a Filter object.
  2. Define the SQL statement and set the WhereClause property.
  3. Optionally, define the columns to populate with data using the ColumnSubset property.
  4. Call the Search method, passing in the Filter object.
Alternatively, the Filter properties can be specified using one of the class constructor overloads. The following code example queries the Country column in the Cities table to find only those rows that are in the United Kingdom (UK):
[C#] 
Filter searchFilter = new Filter();
searchFilter.WhereClause = "COUNTRY = 'UK'";
RowCollection rows = citiesTable.Search(searchFilter);
//or
RowCollection rows2 = citiesTable.Search(new Filter("COUNTRY = 'UK'"));
[VB.NET] 
Dim searchFilter = New Filter()
searchFilter.WhereClause = "COUNTRY = 'UK'"
Dim rows As RowCollection = citiesTable.Search(searchFilter);
'or
Dim rows2 As RowCollection = citiesTable.Search(New Filter("COUNTRY = 'UK'"))
The following restrictions apply to the SQL contained within the WhereClause:
  • The SQL syntax must be supported by the underlying database.
  • GROUP BY clauses are not supported.
  • ORDER BY clauses are not supported.
  • The query will always return all columns. However, you can choose which columns get populated with data using the ColumnSubset property; all remaining columns will be empty (values will be null in C# or Nothing in VB .NET).
For more information on writing SQL statements, see Building a query expression and SQL reference for query expressions used in ArcGIS. These topics are written for ArcGIS Desktop users; however, the majority of the content is relevant to ArcGIS Explorer developers.
  • Scenario 1—Query a world Countries table to find all countries in South America and highlight them on the map.

    The table has a Continent column; therefore, it is possible to use a SQL statement to apply this scenario. See the following code example:
[C#] 
Map theMap = Application.ActiveMapDisplay.Map;
//Get the table associated with the Countries layer.
Table countries = ((FeatureLayer)theMap.FindByName("countries")).Table;
//The Search method takes a Filter object, which has the SQL query defined in its constructor.
RowCollection southAmericanCountries = countries.Search(new Filter(
    "CONTINENT = 'South America'"));
//Add graphics to the map for all South American countries.
foreach (Row country in southAmericanCountries)
{
    Graphic gr = new Graphic(country.Geometry, Symbol.Fill.Outline.Yellow);
    Application.ActiveMapDisplay.Graphics.Add(gr);
}
[VB.NET] 
Dim theMap As Map = Application.ActiveMapDisplay.Map
'Get the table associated with the Countries layer.
Dim countries As Table = DirectCast(theMap.FindByName("countries"), FeatureLayer).Table
'The Search method takes a Filter object, which has the SQL query defined in its constructor.
Dim southAmericanCountries As RowCollection = countries.Search(New Filter("CONTINENT = 'South America'"))
'Add graphics to the map for all South American countries.
For Each country As Row In southAmericanCountries
    Dim gr As Graphic = New Graphic(country.Geometry, Symbol.Fill.Outline.Yellow)
    Application.ActiveMapDisplay.Graphics.Add(gr)
Next
Scenario 1 results—The following screen shot shows the results of Scenario 1 in which only South American countries are highlighted:

  • Scenario 2—Find all the countries in South America that have a population greater than 30 million people and highlight them on the map.

    This scenario is more complicated as the Countries table does not contain population statistics. However, another table (called Demographics) contains a column with population totals for each country. These tables are related by a common column (called Federal Information Processing Standards code [FIPS_CODE ]); therefore, it is possible to use a subquery in the SQL statement to apply this scenario. See the following code example:
[C#] 
Map theMap = Application.ActiveMapDisplay.Map;
//Get the table associated with the Countries layer.
Table countries = ((FeatureLayer)theMap.FindByName("countries")).Table;
//Construct the SQL query string.
string whereClause = "CONTINENT = 'South America' AND FIPS_CODE IN " + "(" + 
    "SELECT FIPS_CODE FROM demographics " + "WHERE TOT_POP>30000000" + ")";
//Create a filter that specifies the SQL clause and the columns to populate with data when the search is executed.
//In this example, only the column storing the geometry will contain data.
Filter searchCriteria = new Filter(whereClause, new string[]
{
    countries.Columns.SpatialColumnName
}

);
//Execute the query.
RowCollection countriesPop30M = countries.Search(searchCriteria);
//Add graphics to the map for all South American countries that have a population > 30 million.
foreach (Row country in countriesPop30M)
{
    Graphic gr = new Graphic(country.Geometry, Symbol.Fill.Outline.Yellow);
    Application.ActiveMapDisplay.Graphics.Add(gr);
}
[VB.NET] 
Dim theMap As Map = Application.ActiveMapDisplay.Map
'Get the table associated with the Countries layer.
Dim countries As Table = DirectCast(theMap.FindByName("countries"), FeatureLayer).Table
'Construct the SQL query string.
Dim whereClause As String = "CONTINENT = 'South America' AND FIPS_CODE IN " & _
                            "(" & _
                            "SELECT FIPS_CODE FROM demographics " & _
                            "WHERE TOT_POP>30000000" & _
                            ")"
'Create a filter that specifies the SQL clause and the columns to populate with data when the search is executed.
'In this example, only the column storing the geometry will contain data.
Dim searchCriteria As Filter = New Filter(whereClause, New String() {countries.Columns.SpatialColumnName})
'Execute the query.
Dim countriesPop30M As RowCollection = countries.Search(searchCriteria)
'Add graphics to the map for all South American countries that have a population > 30 million.
For Each country As Row In countriesPop30M
    Dim gr As Graphic = New Graphic(country.Geometry, Symbol.Fill.Outline.Yellow)
    Application.ActiveMapDisplay.Graphics.Add(gr)
Next
Scenario 2 results—The following screen shot shows the results of Scenario 2 in which only South American countries with a population greater than 30 million people are highlighted:

Shapefiles do not support the use of subqueries in the SQL statement.

Spatial queries

A spatial query is useful when an attribute query is insufficient to apply a particular scenario. They only apply to spatially enabled tables. A spatial query using the Search method consists of a geometry and a spatial relationship, whereas a proximity-based query using the SearchByDistance method consists of a geometry and a distance. The following table shows example scenarios where spatial queries are useful:
Query example
Table
Search geometry
Spatial relationship
API method
Find all the mountains contained within a particular district.
Mountains
(point)
District
(polygon)
Contains
Search
Find out which district contains a particular mountain.
District
(polygon)
Mountain
(point)
Within
Search
Find the highways that intersect a particular state.
Highways
(polyline)
States
(polygon)
Intersects
Search
Find all the ATMs within five miles of my house.
 
 
ATM
(point)
House location
(point)
Within distance of
 
SearchByDistance
 
A spatial query uses the search geometry to test each row in the table for the specified spatial relationship. The Intersects relationship is conceptually the easiest to understand as it can be used to determine if there is any type of spatial relationship between the search geometry and geometries stored for each row (that is, to satisfy the query the search geometry can cross, be within, contain, touch, or overlap any of the row geometries).
For the other relationships, consider whether the spatial relationship is appropriate given the type of search geometry and that of the table. For example, in the first item in the preceding table, the Contains spatial relationship is appropriate as the search geometry is a polygon and the table contains point geometries. Choosing a Within relationship would not make sense as a polygon can never be inside a point. For the full list of possible spatial relationship options, see FilterSearchOptions.
To execute a spatial query (excluding proximity based searches), use the Search method in conjunction with the Filter object.
  1. Create a Filter object.
  2. Set the geometry and SpatialSearchType properties.
  3. Optionally, define an additional SQL statement to be executed in conjunction with the spatial query by setting the WhereClause property
  4. Optionally define which columns to populate with data using the ColumnSubset property.
  5. Call the Search method, passing in the Filter object.
Alternatively, the Filter properties can be specified using one of the class constructor overloads.
  • Scenario 3—The client of a realtor requested information about properties for sale in a particular district. The realtor can apply this request by clicking the applicable district on the map. All properties for sale within the district are highlighted and a property information report appears. See the following screen shot that shows the properties, properties for sale, districts, lakes, and parks:



    Scenario 3 can be applied by doing the following:
    1. Query the Districts table to determine the district the clicked location falls within.
    2. Query the Parcels table to find all properties that are for sale (FOR_SALE='Y') and that intersect the district.
    3. Use a TableBindingAdapter to bind the results to a DataGridView control.

      See the following code example:
[C#] 
Map theMap = Application.ActiveMapDisplay.Map;
//Get the table associated with the districts layer.
Table districts = ((FeatureLayer)theMap.FindByName("districts")).Table;
//Get the table associated with the parcels layer.
Table properties = ((FeatureLayer)theMap.FindByName("Parcels")).Table;
//Return geometry of the clicked district.
Point clickedPnt = Application.ActiveMapDisplay.TrackPoint();
//Query 1—Execute a spatial query to find the district that clicked point falls inside. 
Filter searchDistrictsFilter = new Filter(clickedPnt,
    FilterSearchOptions.Within);
Row foundDistrictRow = districts.Search(searchDistrictsFilter).GetFirst();
//Return the geometry of the district that has been found.
Geometry districtGeometry = foundDistrictRow.Geometry;
//Query 2—Execute a query to select all properties that are for sale and intersects
//the geometry of the district found in Query 1. This query is performing an attribute query
//and a spatial query in a single search.
Filter searchPropertiesFilter = new Filter(districtGeometry,
    FilterSearchOptions.Intersects, "FOR_SALE='Y'");
RowCollection foundProperties = properties.Search(searchPropertiesFilter);
//Loop over the found properties.
foreach (Row property in foundProperties)
{
    //Add graphics to the map for each property found.
    Graphic gr = new Graphic(property.Geometry, Symbol.Fill.Solid.Red);
    Application.ActiveMapDisplay.Graphics.Add(gr);
}

//Use a TableBindingAdaptor to create a tabular report by binding the 
//results of the search to a DataGridView control.
TableBindingAdapter tba = new TableBindingAdapter(foundProperties);
tba.Fill();
PropertyReportForm report = new PropertyReportForm();
report.dataGridView1.DataSource = tba;
report.Show();
[VB.NET] 
Dim theMap As Map = Application.ActiveMapDisplay.Map
'Get the table associated with the districts layer.
Dim districts As Table = DirectCast(theMap.FindByName("districts"), FeatureLayer).Table
'Get the table associated with the parcels layer.
Dim properties As Table = DirectCast(theMap.FindByName("Parcels"), FeatureLayer).Table
'Return geometry of the clicked district.
Dim clickedPnt As Point = Application.ActiveMapDisplay.TrackPoint()
'Query 1—Execute a spatial query to find the district that clicked point falls inside.
Dim searchDistrictsFilter As Filter = New Filter(clickedPnt, FilterSearchOptions.Within)
Dim foundDistrictRow As Row = districts.Search(searchDistrictsFilter).GetFirst()
'Return the geometry of the district that has been found.
Dim districtGeometry As Geometry = foundDistrictRow.Geometry
'Query 2—Execute a query to select all properties that are for sale and intersects
'the geometry of the district found in Query 1. This query is performing an attribute query
'and a spatial query in a single search.
Dim searchPropertiesFilter As Filter = New Filter(districtGeometry, FilterSearchOptions.Intersects, "FOR_SALE='Y'")
Dim foundProperties As RowCollection = properties.Search(searchPropertiesFilter)
'Loop over the found properties.
For Each prop As Row In foundProperties
    'Add graphics to the map for each property found.
    Dim gr As Graphic = New Graphic(prop.Geometry, Symbol.Fill.Solid.Red)
    Application.ActiveMapDisplay.Graphics.Add(gr)
Next
'Use a TableBindingAdaptor to create a tabular report by binding the
'results of the search to a DataGridView control.
Dim tba As TableBindingAdapter = New TableBindingAdapter(foundProperties)
tba.Fill()
Dim report As PropertyReportForm = New PropertyReportForm()
report.dataGridView1.DataSource = tba
report.Show()
Scenario 3 results—The following screen shot shows the results of Scenario 3 in which the "for sale" properties are highlighted on the map within the clicked district and an accompanying property report displays:

Searching by proximity

A common spatial analysis task is to find all rows in a table that are within a distance of another geometry or clicked location. You can use the SearchByDistance method to perform proximity-based queries with the following restrictions:
  • Search geometry (that is, seed location for search) must be a Point geometry
  • Search geometry can be used for geographic or projected units
  • Search distance must be in linear units
The following are options if your search location is not a point:
  • Use Geometry.GetCenter to get the center point of a polygon or polyline.
  • Use Polyline.GetPoint or Polygon.GetPoint to get a particular point location on a polyline or polygon. For example, to get the end point of a single-path polyline, use myline.GetPoint(myline.GetPath(0).Count - 1).
  • Scenario 4—Canton Regional airport encourages new visitors by promoting nearby golf courses. The airport wants accurate marketing statistics by performing an analysis to find golf courses within certain distances of the airport. See the following screen shot that shows golf courses in the vicinity of the Canton airport:

 
The following code example shows how the airport can perform an analysis using the SearchByDistance method to find all golf courses within five miles of the airport:
[C#] 
Map theMap = Application.ActiveMapDisplay.Map;
//Get the table associated with the AIRPORT layer.
Table airports = ((FeatureLayer)theMap.FindByName("AIRPORT")).Table;
//Get the table associated with the GOLF layer.
Table golfCourses = ((FeatureLayer)theMap.FindByName("GOLF")).Table;
//Return the row for "Akron - Canton Regional Airport" (CAK).
Row airportRow = airports.Search(new Filter("LOCID='CAK'")).GetFirst();
//Get the airport location.
Point airportPnt = airportRow.Geometry as Point;
//Find all golf courses within five miles of the airport.
RowCollection foundCourses = golfCourses.SearchByDistance(airportPnt, 5,
    Unit.Linear.MilesStatute);
//Print the number of golf courses found.
System.Diagnostics.Debug.Print(foundCourses.Count.ToString());
foreach (Row course in foundCourses)
{
    //Print the golf courses' names.
    System.Diagnostics.Debug.Print(course.Values["Name"].ToString());
    //Create a graphic for each golf course and add it to the map as a graphic.
    Graphic gr = new Graphic((Point)course.Geometry,
                             Symbol.Marker.Placemark.ArrowYellow);
    Application.ActiveMapDisplay.Graphics.Add(gr);
}
[VB.NET] 
Dim theMap As Map = Application.ActiveMapDisplay.Map
'Get the table associated with the AIRPORT layer.
Dim airports As Table = DirectCast(theMap.FindByName("AIRPORT"), FeatureLayer).Table
'Get the table associated with the GOLF layer.
Dim golfCourses As Table = DirectCast(theMap.FindByName("GOLF"), FeatureLayer).Table
'Return the row for "Akron - Canton Regional Airport" (CAK).
Dim airportRow As Row = airports.Search(New Filter("LOCID='CAK'")).GetFirst()
'Get the airport location.
Dim airportPnt As Point = DirectCast(airportRow.Geometry, Point)
'Find all golf courses within five miles of the airport.
Dim foundCourses As RowCollection = golfCourses.SearchByDistance(airportPnt, 5, Unit.Linear.MilesStatute)
'Print the number of golf courses found.
System.Diagnostics.Debug.Print(foundCourses.Count.ToString())
For Each course As Row In foundCourses
    'Print the golf courses' names.
    System.Diagnostics.Debug.Print(course.Values.Item("Name").ToString())
    'Create a graphic for each golf course and add it to the map as a graphic.
    Dim gr As Graphic = New Graphic(DirectCast(course.Geometry, Point), Symbol.Marker.Placemark.ArrowYellow)
    Application.ActiveMapDisplay.Graphics.Add(gr)
Next
Scenario 4 results—The following screen shot shows golf courses within five miles of the airport:

Improving query performance

The following are tips to optimize searches on a table:
  • Ensure that any column being queried has a physical index built against it. A column index can be created using the CreateIndex method or by working with the tables IndexCollection. Alternatively, you can use other software to do this, such as ArcGIS Desktop.
  • When searching a large table with many columns, set the ColumnsSubset property on the Filter object so that only the columns of interest will be populated with data.
  • When executing a spatial query on a table storing complex geometrical shapes, use the EnvelopeIntersects or IndexIntersects FilterSearchOptions, which involves less intensive calculations.
  • There can be performance gains if the search geometry used in a spatial query has the same CoordinateSystem as the table, as it will not need to be re-projected beforehand.


See Also:

Building a query expression
SQL reference for query expressions used in ArcGIS