When you add a dataset as a layer to a map or scene, sometimes you only want to work with and draw a subset of the features in the dataset. Examples include the following:
- From a dataset of world cities, working with only those with a population greater than 1 million
- From a dataset of hydrology lines, working with only perennial streams
- From a dataset of parcels, working with only those that are zoned commercial
Definition queries allow you to define a filter that limits which features are retrieved from the dataset by the layer. This means that a definition query affects not only drawing, but also which features appear in the layer's attribute table and can be selected, labeled, identified, and processed by geoprocessing tools.
If you want to limit which features are drawn but retain all features for querying and processing, consider using display filters instead. Definition queries and display filters can be used together on a layer, but definition queries limit the features available in the layer, while display filters only limit which features are displayed.
Definition queries are composed of one or more clauses. There are two types of clauses: attribute clauses and spatial clauses. All clauses are written in SQL syntax and constructed using the query builder. See Introduction to query expressions to learn how to work with clauses and queries and how to save and load queries from files.
A layer can have multiple definition queries associated with it, but no more than one definition query can be active at a time. Even when multiple queries are defined for a layer, they can all be turned off, meaning no query is actively applied.
Note:
This topic outlines definition queries on a feature layer, but definition queries can be used in the same way on stand-alone tables.
Create a definition query for a layer
To create a definition query on a layer, follow these steps:
- Ensure a layer is selected in the Contents pane.
- On the Feature Layer tab set, click the Data tab.
- On the Data tab, in the Definition Query group, click the Build Definition Query button
to open the Layer Properties dialog box with its Definition Query tab open. - Click the New definition query button, or click its drop-down menu to create a query in designer mode (using clauses), editor mode (using SQL code), or a query expression file (.exp).
- In the query builder, choose values from the menus to construct the clause. These menus and their values are specific to the underlying source data.
- Optionally, click Add Clause
to add another attribute clause to the query. Choose values from the menus in the new clause as required. Repeat this step to add more attribute clauses, or click its drop-down menu to add a spatial clause. - Optionally, click Settings
to modify the display of the query or its field values. These settings are applied to all clauses.- Click Sort Ascending or Sort Descending to display the names of the fields in the drop-down menu in alphabetical order. Click Original order to return the fields to their default order.
- Show field aliases is selected by default. Click Show field names to include the name of the table in the clause's field drop-down menu. This is especially helpful if your layer participates in a join that shares common field names.
- Show visible fields is selected by default. Click Show all fields to display hidden fields in the clause's field drop-down menu.
- SQL autocomplete is an editor mode setting enabled by default that makes suggestions while writing SQL code. Click this setting to disable SQL autocomplete functionality.
- Show domain and subtype values only is selected by default. Click Show all values to include any invalid or incorrect values in the field's drop-down menu of unique values.
- Optionally, hover over the query name and click to rename it.
- Optionally, click Verify the SQL expression is valid
to verify the query. - To verify and finalize the query, in the query builder, click Apply.
- If this is the only definition query on the layer, it becomes the active definition query, and is immediately applied. If there are other definition queries on the layer, click Set as Active
to apply a definition query to the layer.
Add a spatial clause to a definition query
In addition to creating definition queries with one or more clauses, definition queries may include a spatial clause. Unlike regular clauses that filter the features retrieved from the dataset by specifying a field or range of values, spatial clauses use the geometry of a layer or spatial extent.
A definition query can only include one spatial clause.
Note:
While feature layers support them, spatial clauses are not supported with certain other layer types. Review your layer type's documentation to determine whether the layer type supports spatial clauses.
Follow these steps to add a spatial clause to an existing definition query:
- If necessary, right-click the layer in the Contents pane and click Properties to open the Layer Properties dialog box.
- On the Definition Query tab, click Edit to modify an existing query, or follow steps 1 through 6 in the above workflow to create a new definition query.
- Click the Add Clause drop-down menu and click Add Spatial Clause.
The Spatial Clause Geometry dialog box appears.
- On the Spatial Clause Geometry dialog box, choose one of the following:
- Choose a layer—Use the geometry of a layer in the active map. Graphics layers and multipatch layers cannot be chosen. You can use the geometry of all features in the layer, only selected features, or only features in view. If the layer used to generate the geometry is edited or removed from the map, the spatial clause geometry is not affected.
- Choose a custom extent—Use a custom extent from one of the following sources:
- The current visible extent
- The extent of the data in all layers
- The extent of all features or elements in a layer
- The extent of all selected features or elements
- Choose a layer or extent option in the list to generate the geometry for the spatial clause. A preview of the geometry is shown.
- Click OK.
A spatial clause is added to the query. In both editor and designer mode, the spatial clause appears below the attribute clause expressions. The AND operator appends the spatial clause to the rest of the expression.
Note:
The Shape field name and is intersected by operator are required components of the spatial clause. These values cannot be modified.
Set the active definition query
You can define more than one definition query for a layer or table, and switch between them. Only one is active at any time, or you can have no queries active. The definition queries are stored as a property of the layer.
To switch between active definition queries, follow these steps:
- Ensure that a layer is selected in the Contents pane.
- On the Feature Layer tab set, click the Data tab.
- On the Data tab, in the Definition Query group, choose a query from the Definition Query menu to apply to the layer.
- Alternatively, you can set the active query from the Definition Query tab of the Layer Properties dialog box.
- Click Set as Active and click Apply to apply that query to the layer. Any other queries become inactive. The active query is named at the bottom of the Properties dialog box. If you have many queries listed and the active query is out of view, click Show the active definition query
to scroll to it. - Click Clear active query and click Apply to have no query applied to the layer or table. All queries are retained by the layer or table, but none of them are currently applied.
- Click Set as Active
Tip:
You can filter the list of layers in the Contents pane to show only those layers with an active definition query.
Modify existing queries
With the layer selected in the Contents pane, on the Feature Layer tab set, on the Data tab, in the Definition Query group, click the Build Definition Query button to open the Layer Properties dialog box with its Definition Query tab open. To modify a definition query, hover over the query and follow any of these actions:
- To modify, add, or remove clauses from the definition query, click Edit in the query's cell.
- To edit a clause within a query, adjust values as necessary.
- To remove a clause from the query, click Remove Clause
. - To add a clause to the query, click Add Clause .
- To edit the spatial clause geometry of an existing query, click Geometry
.
Click Apply to apply the changes to the definition query.
- To remove the definition query from the layer or table, when not in editing mode, hover over the definition query cell and click Remove defintion query .
- To rename a definition query, click the name of a definition query and type a new name.
- To export the query syntax to a query expression file (.exp), when not in editing mode, hover over the definition query cell and click Save definition query
. - To replace the current query with a different query from a query expression file (.exp), when not in editing mode, hover over the definition query cell and click Load definition query
.
Set definition queries on multiple layers at once
If multiple layers share definition queries with a common name, even if the content of the queries is not the same, the queries can be activated for the layers simultaneously. This means you can construct a number of related queries for a certain scenario and apply them all at once. You can then switch to a different set of queries to satisfy a different scenario, if necessary.
To apply definition queries with a common name to multiple layers simultaneously, follow these steps:
- Ensure the layers are selected in the Contents pane.
- On the Feature Layer tab set, click the Data tab.
- On the Data tab, in the Definition Query group, choose a query from the Definition Query menu to apply to the layer.
Only definition queries with a name common to all selected layers are shown.
Automate definition query creation
You can automate the creation and operation of definition queries with Python by using a combination of ArcPy functions. For information on this topic, including code samples, see the Layer class documentation.
You can also generate a definition query from the selection on a feature layer with the Generate Definition Query From Selection tool.
Sharing layers with definition queries
When a layer is shared as a feature layer or within a map image layer, only the active definition query is retained and honored. All other inactive definition queries are dropped.
If the query contains a spatial clause, an error is returned. See 00428: The definition query spatial clause is not supported by the server for more information.