ArcGIS Pro

An introduction to query layers

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.

Query layers behind the scenes
Query layers behind the scenes

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:

Supported data sources with query layers
Supported data sources with 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.

Add Query Layer option on the ribbon
Add Query Layer option on the ribbon

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

Add the database connection file
Add the database connection file

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

The List tables option
The List tables option
The query layer name
The query layer name
The SQL expression
The SQL expression

The validation process verifies the following:

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:

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.

The unique identifier field
The unique identifier field

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.

The Solar Potential Buildings Madrid query layer
The Solar Potential Buildings Madrid query layer

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.

Use query layer as input for geoprocessing tools
Use query layer as input for geoprocessing 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.

Save query layer as a layer file
Save query layer as a layer file

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.

Access the Properties tab from the layer right click context menu
Access the Properties tab from the layer right click context menu

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.

Query Layers Properties dialog
Query Layers Properties dialog

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:

Share query layer as a web feature layer
Share query layer as a web feature layer

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.

Add data from a SQLite database to the map
Add data from a SQLite database to the map

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.

Editing tab on the Contents pane
Editing tab on the Contents pane

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

The Make Table View geoprocessing tool
The Make Table View geoprocessing tool

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:

__________________________________________________

The Make Query Table geoprocessing tool
The Make Query Table geoprocessing tool

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:

__________________________________________________

The Create Database View geoprocessing tool
The Create Database View geoprocessing tool

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:

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.

About the author

Product Engineer on the Geodatabase team, passionate about making a difference in people's lives using GIS. Hiker and a true Éclair and Crêpe lover in her free time.

Next Article

What's new in ArcGIS Business Analyst Enterprise | May 2024

Read this article