Hands On Points Using Operators When selecting by attribute, LIKE and NOT are often used to compare text strings and match patterns, often employing wildcards. Wildcards can replace one character or a group of characters that are unknown. The character used depends on the data source being queried. For personal geodatabases, use a question mark (?) for a single character and an asterisk (*) for a group of characters. For shapefiles, ArcSDE geodatabase feature classes, and other types of data, use an underscore (_) to replace a single unknown character and a percentage sign (%) to replace a group of characters. ArcMap will detect the type of database being queried and adjust the wildcard characters available through the Select by Attribute dialog box. LIKE is a good operator for finding text strings that contain variant spellings or possibly misspellings, of a text string. Rather than using an equal sign followed by the search term enclosed in quotes, use LIKE and enclose the search term and a wildcard character with quotes. Like the characters used for wildcards, the syntax used when querying dates depends on the underlying database. ArcMap automatically writes the proper syntax when you double-click on a date value in the Unique Values list of the Select by Attribute dialog box. The choice of spatial operator (i.e., the relationship tested by the query) depends on the types of features that will be used for the source and filter. Lines Polygons Spatial queries use the shape and location of one set of features to select another set of features. This illustration shows how a spatial query finds the point, line, or polygon features that intersect with polygon features in another layer. Answering More Complex Questions To meet multiple search criteria, several attribute queries can be combined using logical operators (such as AND, OR, LIKE, and NOT) to find records based on several criteria in two or more attribute fields. Remember that OR is the far more flexible and inclusive operator. When using AND, both conditions must be true to return records. For queries that use OR, only one condition must be true to return records. Ordinarily, queries are evaluated from left to right. However, any portion of the query enclosed in parentheses is evaluated first. The order of operations can be important both in obtaining a valid answer to the question being asked and in optimizing the way a query runs. 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. For more complex spatial queries, subqueries can be used to sieve data. The results of one query can be used as the basis for additional queries related to the currently selected features, either selecting from those features, adding to those features, or removing features from the selected set. These operations work in much the same way as AND and OR operators by creating subsets that will be the basis for additional selections. Working with Both Query Types Although attribute and spatial queries can work together in locating the desired information, because they are entered in ArcMap in different dialog boxes (Select by Attribute, Select by Location), these two different kinds of queries must be posed separately. To expand the previous example (and narrow the search), the desired parcels must have sold within the last year, be zoned C-3, and be located within five miles of a specific parcel in addition to being between one and two acres in size with a slope of less than 5 percent. The date sold and zoning information are also stored in the attribute table but identifying the parcels within five miles of the subject parcel will require a spatial query. Limiting the parcel search to those that fall into the five-mile buffer around the subject parcel will eliminate many records, so that query is performed first. Using the features selected by location, an attribute query that evaluates the sale date to limit the selection, then the zoning, lot size, and slope will identify features that fulfill all criteria. Conclusion Whether the question to answer is simple or complex, break down the question being asked into its query components--source, filter, and relationship--and arrange those components in the order that correctly and efficiently answers that question. Understanding the data distribution in a data source will also help you understand how to best order operations to minimize the number of records that must be searched. For more help on building queries, refer to the ArcGIS Desktop Help Online and search for the topic "SQL reference." www.esri.com ArcUser Spring 2009 57