Query layers are map layers or stand-alone tables defined by a SQL query. They do not store data themselves but instead provide a way to access and display spatial data (features) and nonspatial data (tables) from different sources (databases or cloud data warehouses) based on specific criteria that is defined in the SQL statement.

Benefits to using query layers in your workflow

Real-time data exploration—The SQL query that defines a query layer is a statement that is run inside the database or data warehouse when the layer is displayed in a map. This allows the latest information to be visible without making a copy or snapshot of the data. This is especially useful when working with dynamic information that is frequently changing, such as traffic counts to identify traffic growth patterns in real time.

Data filtering—You can create a query layer to filter data based on specific criteria, such as displaying only the buildings constructed after a certain year or showing only the customers who have made a purchase in the last month. This allows you to quickly explore subsets of your data without the need for complex queries or manual sorting.

Data integration—Query layers allow you to integrate data from different tables or feature classes to identify patterns that may not be apparent when analyzing data from a single source. You can create a query layer to combine data from different tables or feature classes based on a common field, such as displaying a building feature class with its associated owner information stored in a stand-alone table.

Spatial analysis—Query layers can be used to perform spatial analysis on data. For example, you can create a query layer to identify all the parcels that intersect with a specific buffer zone or find all the roads that are within a certain distance from a point of interest.

Data sources

The following graphics list the supported data sources to create query layers:

In a future release of ArcGIS Pro, the support for query layers will be enhanced by incorporating nonrelational databases access. This enhancement will allow you to seamlessly integrate and query nonrelational databases within your GIS workflows.

Note: To review the full list of database considerations for query layers, read the Database and client configurations for query layers help topic.

Create query layers

In ArcGIS Pro, you can use the New Query Layer dialog box on the ribbon to create a query layer. The dialog box can be accessed from the Map tab, on the Add Data drop-down menu.

When creating a query layer, the following parameters are required in the New Query Layer dialog box:

• The Connection file that points to the database or cloud data warehouse you want to connect.
  • You can select an existing database connection from the drop-down menu if you have an active database connection file in the Catalog
  • You can create a new database connection directly on the New Query Layer dialog box.

Note: Ensure the user connecting to the database has access to the feature classes or tables referenced in the query expression.

• Checking the List of Tables option displays a list of tables together with the columns specific to each table. This option is beneficial in constructing the SQL query because you can add the tables or columns directly to the Query text box, in SQL syntax.

• Provide a descriptive name of the resulting query layer in the Name text box.

• Define the SQL query using the Query text box. The tool uses the underlying SQL syntax to create the query. This allows for more customization and more robust expressions than when using standardized SQL. This example displays only the buildings with high solar potential where solar panels can be installed.

• Click Validate. TThe query must pass the validation process. This is a necessary step because during validation ArcGIS examines the properties of the first row returned from the table to assess the query’s validity. Additionally, ArcGIS uses these properties to filter which other rows will be displayed on the map. For instance, ArcGIS supports only one geometry type per table, so only features matching the geometry type of the first row in the query will appear on the map.

The validation process verifies the following:

• The result of the SQL query must have only one spatial field.
• The result of the SQL query must have only one spatial reference.
• The result of the SQL query must have only one entity type, such as point, multipoint, line, or polygon.
• The result of the SQL query cannot have field types that are not supported by ArcGIS. See ArcGIS field data types for descriptions of the field types supported in ArcGIS.

Once the query is validated, the Next button becomes available. Click the Next button to go to the second page of the dialog box to confirm the unique identifier field is checked and spatial properties are defined.

To use a field as a unique identifier, the field must be not null, must contain unique values, and must be one of the following data types:

• Integer (32- and 64-bit positive values only)
• String
• GUID
• Date

If your table does not have a primary key field or fields that meet the above criteria, you can select multiple fields to serve as the unique identifier.

Note: You can also use the Make Query Layer geoprocessing tool to create a query layer.

After the query layer is created, it will be stored as a layer in the ArcGIS Pro project and automatically added to the active map.

Query layers in ArcGIS Pro

Query layers are similar to other feature layers and stand-alone tables in a map.

 

Spatial analysis

Query layers can be used as input features to geoprocessing tools and ModelBuilder tools.

Export query layers

Query layers are layers that exist only in the ArcGIS client. If the ArcGIS Pro project is deleted, the query layer will also be deleted. To reuse a query layer, you can save it as a layer file on disk or by saving the map document.

Modify query layers

Query layers are read-only layers, which means you cannot make edits to the data displayed in the layer. However, you can make edits the properties of the layer itself.

Right-click to access the layer’s context menu in the Contents pane and modify the layer properties.

On the Edit Query Layer dialog box, you can make modifications to the SQL expression, change the layer’s spatial properties, and change the geometry type. You can also add parameters in query layers to make the SQL statement dynamic. This is beneficial when you do not know SQL statement variables in advance and the variable value is defined when the query is run.

Note: To learn more about how to define parameters in query layers, read the Define parameters in a query layer help topic.

In the ArcGIS Pro 3.3 release, you can use a keyboard shortcut, Ctrl+Q, to open the Edit Query Layer dialog box, which improves access to editing query layer properties.

Publish Query Layers

Query layers are read-only layers when accessed directly from a database connection. However, query layers that are pointing to data in a relational database can be editable when shared as web feature layers.

You can also share query layers as copies to ArcGIS Online or by referencing a registered data store to ArcGIS Enterprise.

When shared by reference, you can access the web feature layer in your organization portal and consume it in web maps and web apps to support a wide variety of editing workflows. The edits made on the web will reference the source database. This is beneficial if the query layer is accidentally deleted; the edits made to the web feature layer will continue to reference the source data in the database.

To make query layers editable through the services, you must ensure the following conditions are met before publishing:

• The query layers cannot contain virtual columns.
• The ID field must be a single, not null, unique, automatically incrementing, 32-bit, integer field.
• The SQL statement cannot have where clauses.
• The SQL statement cannot have active joins.

Note: Query layers that access data in a cloud data warehouse are not editable, even through a web feature layer. To find out more about how to create a query layer for data in a cloud data warehouse, watch the short demo video Query Layers and Cloud Data Warehouses from the 2022 Esri Developer Summit and read the Leverage Cloud Data Warehouses with ArcGIS Pro and ArcGIS Enterprise blog article.

SQLite databases and OGC GeoPackages

If you are using SQLite databases or OGC GeoPackages to store your data, you may have noticed that when you drag a table from a database or data from an SQLite database or OGC GeoPackage in your project onto a map, ArcGIS Pro automatically creates a query layer that selects all fields and rows from the table. This is because query layers are the technology used to support access in ArcGIS Pro for this type of workspace.

One important difference is that query layers created from an SQLite database or OGC GeoPackage are editable directly in ArcGIS Pro. This is to allow for a full editing experience for the open-source user community who are using both Esri and open-source products.

Note: To learn more about this types of workspaces, read the How to Use OGC GeoPackages in ArcGIS Pro blog article.

Tool comparison

A lot of questions are coming up on the Esri Community Data Management page regarding the different use cases between query layers and other tools that use SQL expressions to retrieve and join data from different workspaces. Depending on your workflow, you may be familiar with table views, query tables, and database views as their functionality is similar to query layers

Make Table View

The Make Table View geoprocessing tool creates a table view from an input table or feature class. You can use this tool when you want to visualize a subset of your data that is stored in a stand-alone table or feature class in a geodatabase.

The tool has the following particularities:

• It supports a single input that can either be a table or feature class.
• It does not perform joins.
• The data source can be any geodatabase (file, mobile, or enterprise).
• It uses standardized SQL to create the expression.
• It creates a temporary output that does not persist after the ArcGIS Pro session ends, unless the document is saved.
• The output table view is editable.

__________________________________________________

Make Query Table 

The Make Query Table geoprocessing tool applies a SQL query to a database and the results are represented in either a feature layer or table view. Use this tool when you want to join multiple datasets from a geodatabase or OLE DB connection and generate a subset of the returned join.

The tool has the following particularities:

• It supports multiple inputs that can be both feature classes and stand-alone tables.
• It does support joins.
• The data source can be any geodatabase (file, mobile, or enterprise) or an OLE DB connection.
• It uses standardized SQL to create the expression.
• It creates a temporary feature layer or table view that is not persisted after the ArcGIS Pro session ends, unless the document is saved.
• The output is not editable, unless published as a feature service.

__________________________________________________

Create Database View

The Create Database View geoprocessing tool creates a view in a database based on a SQL expression. Use this tool when you want to create a persistent database view from a subset of data to which you want to give access to other users.

The tool has the following particularities:

• Tool input is the name of the connection file that is used to connect to the underlying database.
• It does support joins.
• The data source can be any geodatabase or database.
• It uses the underlying SQL syntax of the database you want to connect to create the expression.
• It creates a permanent view in the underlying database that can be used by applications other than ArcGIS.
• The resulting database view can be registered with the geodatabase but is not editable, even when published as a service.

Summary table

The table below highlights all four tools’ particularities.

	Make Table View	Make Query Table	Create Database View	Make Query Layers
Tool definition	Creates a table view from an input table or feature class.	Applies an SQL query to a database and the results are represented in either a feature layer or table view.	Creates a database view in a database based on an SQL expression.	Creates a query layer from a database or cloud data warehouse on an input SQL select statement.
Use case	Use this tool when you want to visualize a subset of your data that is stored in a stand-alone table or feature class in a geodatabase.	Use this tool when you want to join multiple datasets from a geodatabase or OLE DB connection and generate a subset of the join return.	Use this tool when you want to create a persistent database view from a subset of data to which you want to give access to other users.	Use this tool when you want to visualize multiple datasets from a database or cloud data warehouse.
Data source	Any geodatabase (file, mobile, or enterprise).	Any geodatabase (file, mobile, or enterprise) or OLE DB connection.	Any geodatabase (file, mobile, or enterprise) or database.	Enterprise geodatabase or cloud data warehouse connection.
Input datasets	The tool allows for a single input that can either be a table or feature class. Does not support joins.	The tool supports multiple inputs that can be both feature classes and stand-alone tables.	The name of the connection file that will be used to connect to the database.	The name of the connection file that will be used to connect to the database or cloud data warehouse.
Query expression	Supports standardized SQL to filter data.	Supports standardized SQL to filter and aggregate data.	Supports the underlying SQL syntax of the DBMS for more robust expressions.	Supports the underlying SQL syntax of the DBMS or cloud data warehouse for more robust expressions.
Output file	Temporary table view that does not persist after the ArcGIS Pro session ends unless the document is saved.	Temporary feature layer or table view that does not persist after the ArcGIS Pro session ends unless the document is saved.	Persisted view in the underlying database and can be used by applications other than ArcGIS.	Temporary query layer or query table that does not persist after the ArcGIS Pro session ends unless the document is saved.
Output capabilities	Data is editable and nonspatial.	Data is not editable. It can be either spatial or nonspatial depending on the input.	Data is not editable, even when published as a service.	Data is not editable unless is pointing to a cloud data warehouse connection and is published as a feature service.

To conclude, query layers allow you to visualize real-time changes in a dynamic editing environment. When you create a query layer from a database or cloud data warehouse, it is stored in the map or scene within your ArcGIS Pro project. It can then be shared as a web feature layer to your organization’s portal. The web feature layer can be consumed in web maps or dashboards for visualization purposes.

Check out the Geodatabase Resources Hub for more content and learning resources.