The query builder is used in the Find by Attributes and Location and Filter by Attributes tools to create expressions that meet the specified criteria.
Building a query
To build a query, click Build new query in the tool pane or on the tool dialog box.
To start, select the layer you want to find features from. For Find by Attributes and Location, that can be done in the query builder. For Filter by Attributes, the input data parameter must be populated before the query can be built.
Next, choose whether to start with an attribute expression, spatial expression (Find by Attributes and Location only), or expression group.
If the query has more than one expression, you must choose one of the following matching options to connect the expressions:
- All of the following are true (default)—Only the features that match all of the expressions are returned. This option is the equivalent to an AND clause.
- One of the following are true—Features that match any of the expressions are returned. This option is equivalent to an OR clause.
Attribute expressions
Each attribute query includes a field name, an operator, and a value.
For example, you can create a query on a layer containing a city's crime incident data (thefts, arson fires, assaults, and so on) to show only theft incidents. Create an attribute expression as follows:
- For the field name, choose Crime Type.
- For the operator, choose equals.
- For the value, write or select Theft.
Note:
Fields with null values will be excluded from all query results except when is blank is chosen as the operator.
In most cases, the query builder includes a list of available field values to aid in building an accurate expression. The list is not available for Filter by Attributes if the input dataset is an output from another tool. You must type the exact value into the input box, rather than choosing the value from a list.
Spatial expressions
Each spatial expression includes an operator and a second layer. Below the second layer, you can use the + Expression button to add an expression to further refine the features from the second layer that will be used in the original spatial expression.
For example, with another layer representing bakeries, you can further refine the attribute query to look for thefts that occurred within 100 meters of a donut shop. Add a spatial expression, as follows:
- For the operator, choose Within a distance of and enter 100 meters.
- For the layer, choose the bakeries layer.
- Click the + Expression button and choose Attribute expression.
- In the Where statement group, choose Shop Type for the field name, leave the operator as equals, and write or select Donut shops as the value.
Expression group
To create a more complex query, you can use expression groups to group multiple expressions. The way in which you group the expressions determines what you are finding. You can create up to three levels of grouping.
For example, you can use expression groups to further refine the crime incident filter described above. To show both arson fire and theft incidents reported within a distance of 100 meters of a donut shop, you can create an expression group to find arson fires or theft incidents. Within that group use the One of the following are true option. Then, outside of the group, add a spatial expression for finding features within 100 meters of donut shops.
Operators
Find by Attributes and Location uses operators for attribute and spatial queries, while Filter by Attributes uses operators for attribute queries only.
Attribute operators
There are several operators that you can use for an attribute query, depending on whether the field you are querying is categorical or numerical.
If the field is a string, you can use the following operators:
- equals—Strings that match the specified text.
- does not equal—Strings that do not match the specified text.
- is a member of—Strings that match one of the specified text values.
- is not a member of—Strings that do not match any of the specified text values.
- contains—Strings that have the provided text.
- does not contain—Strings that don't have the provided text.
- starts with—Strings that begin with the specified text.
- does not start with—Strings that do not begin with the specified text.
- ends with—Strings that finish with the specified text.
- does not end with—Strings that do not finish with the specified text.
- is blank—Strings that have no text and are not null.
- is not blank—Strings that have some text and are not null.
- is empty string—Strings that are null or have no text.
- is not empty string—Strings that have some text or are null.
If the field is numerical, you can use the following operators:
- is between—Numbers that are between the two specified values. The bounding values are included in the results.
- is not between—Numbers that are not between the two specified values. The bounding values are not included in the results.
- equals—Numbers that match the specified value.
- does not equal—Numbers that do not match the specified value.
- is a member of—Numbers that match one of the specified values.
- is not a member of—Numbers that do not match any of the specified values.
- is greater than—Numbers that are greater than the specified value.
- is at least—Numbers that are equal to or greater than the specified value.
- is less than—Numbers that are less than the specified value.
- is at most—Numbers that are less than or equal to the specified value.
- is blank—Numbers that match the specified value.
- is not blank—Numbers that have some value.
If the field is a date, you can use the following operators:
- is between—Dates that are between two specified dates. The bounding values are included in the results.
- is not between—Dates that are not between two specified dates. The bounding values are not included in the results.
- is on—Dates that are the same as a specified date.
- is not on—Dates that are not the same as a specified date.
- is before—Dates that are before a specified date.
- is after—Dates that are after a specified date.
- is blank—Dates that are null.
- is not blank—Dates that are not null.
Spatial operators
Spatial expressions are created by specifying operators such as completely within or intersects.
You can use the following spatial operators in a spatial query:
- Intersects—If a feature in the first layer intersects a feature in the second layer, the feature in the first layer is included in the output.
- Does not intersect—If a feature in the first layer intersects a feature in the second layer, the feature in the first layer is excluded from the output.
- Within a distance of—A feature in the first layer is selected (included in the output) if any part of it is within the specified distance of a feature in the second layer; for example, parcels within 100 meters of a river will be included in the output.
- Not within a distance of—A feature in the first layer is excluded (not included in the output) if it is within a distance of another feature; for example, parcels within 100 meters of a river will not be included in the output. You can think of this relationship as the feature is farther away than the specified distance.
- Completely contains—If a feature in the first layer completely contains a feature in the second layer, the feature in the first layer is included in the output.
- Does not completely contain—If a feature in the first layer completely contains a feature in the second layer, the feature in the first layer is excluded from the output.
- Completely within—If a feature in the first layer is completely within a feature in the second layer, the feature in the first layer is included in the output.
- Not completely within—If a feature in the first layer is completely within a feature in the second layer, the feature in the first layer is excluded from the output.
- Nearest to—If a feature in the first layer is nearest to a feature in the second layer, the feature in the first layer is included in the output.
Troubleshooting
Changes to a dataset may cause a previously valid expression to become invalid. Examples of changes that could cause an expression to become invalid include deleting or changing the field type for a field used in the expression, or changing the geometry of a dataset used in a spatial expression. Invalid expressions will not be displayed in the query builder when it is reopened.