[an error occurred while processing this directive] [an error occurred while processing this directive]
ArcWatch: Your e-Magazine for GIS News, Views, and Insights

March 2009

Use Queries More Effectively

Faster Answers

These strategies will help you use queries more efficiently by accomplishing more in a single search.

 click to enlarge
When you understand your data, you can create more effective queries by ordering operations to minimize the number of records that must be searched.

Queries are requests for information that are posed to a GIS in a specific fashion. These questions can be based on table attributes or features and are expressed in a formal way using Structured Query Language (SQL). In ArcGIS Desktop, these queries are handled by the ArcMap interface, which adjusts the available operators based on the underlying database.

Queries on attribute tables can combine several selection operations using the logical operators AND, OR, LIKE, and NOT and the comparison operators =, <>, >, >=, <, and <= to find records based on several criteria in two or more attribute fields.

A single query such as

"LOT_SIZE" >= 1 AND "LOT_SIZE" <= 2 AND "SLOPE" < 5

could be used to locate parcels with lots between one and two acres in size that have a slope of less than 5 percent.

When using AND, both conditions must be true to return records. The OR operator is far more flexible and inclusive. For queries that use OR, only one condition must be true to return records.

Understanding the data distribution in a data source will also help you create more effective queries by ordering operations to minimize the number of records that must be searched. Ordinarily, the terms in a query are evaluated from left to right. However, any portion of a query enclosed in parentheses will be evaluated first. You can order searches to limit the number of records that must be searched to locate the information you seek.

To extend the previous example, you need to find not only parcels with a certain lot size and slope but also parcels that are zoned for a specific commercial use. If you know that most parcels in this area are zoned for residential use, you can eliminate those records first to decrease the total number searched. This means that the query

"ZONING" = 'C-3' AND "LOT_SIZE" >= 1 AND "LOT_SIZE" <= 2 AND "SLOPE" < 5

will be executed more quickly than this query:

"LOT_SIZE" >= 1 AND "LOT_SIZE" <= 2 AND "SLOPE" < 5 AND "ZONING" = 'C-3'

For more help on building queries, search the ArcGIS Desktop Online help (available at ArcGIS Resource Center) for the topic "SQL reference."

[an error occurred while processing this directive]