Create a query layer for data in a database—ArcGIS Pro

You can drag a database layer onto the map to automatically create a query layer, or use the Add Data button on the Map tab to create a query layer in your map.

Tip:

This page explains how to create a query layer for a table in a database. To access data in a cloud data warehouse, see Create a query layer for data in a cloud data warehouse.

Query layers that access geodatabase system tables are not supported.

Prerequisites and workflows

Before you create a query layer, read Database and client configurations for query layers.

Next, read the subsections below to gain an understanding of the workflow to follow when creating a query layer for a database using the New Query Layer dialog box.

Connect to the database

Before you can define a query layer, you must connect to the database you want to query.

You can create a database connection in the Catalog pane, as explained in Database connections in ArcGIS Pro, add it to the project, and browse to it from the New Query Layer dialog box, or you can create the database connection from the New Query Layer dialog box.

Define an SQL query

Specify an SQL query in the Query text box to access the data you need from the table or view.

You can paste an SQL query in the Query text box or, if you don't have an SQL query prepared and you need to see a list of tables, check the List of Tables box. You can double-click the tables and columns that appear in the list to construct an SQL query by either double-clicking it or dragging it from the table list window into the Query text box.

Each column in a database has a specific data type. ArcGIS can work with most common database types. However, some less common database types are not supported. If the attribute column type is unknown, this indicates that ArcGIS does not support that data type. When you specify a query, all columns that have an unknown data type must either be excluded or changed in the query to a data type that ArcGIS supports. See DBMS data types supported in ArcGIS for a list of supported data types per database.

Tip:

ArcGIS Pro added support for the following data types in 3.2:

Big integer
Date only
Time only
Timestamp with offset

If the new data types are present in the source table when you create or modify a query layer, they will be recognized as the new types. Although ArcGIS Pro added support for these data types in 3.2, they are considered beta functionality in ArcGIS Enterprise at this time. To retain the mapping used in earlier ArcGIS releases, you can configure a Maps and Scenes setting for your ArcGIS Pro installation.

Support for the new data types varies by database and cloud data warehouse platform. See DBMS data types supported in ArcGIS for more information.

Use SQL syntax specific to the database when building a query layer. A common example is as follows: SELECT * FROM Test.myuser.US_States. This results in a query layer containing all rows from the US_States table. In the map, this displays all the United States.

To learn how to use variables in SQL queries, see Define parameters in a query layer.

Validate the SQL statement

After you define an SQL query, validate it. During validation, ArcGIS uses the properties of the first row returned in the table to determine the validity of the query.

ArcGIS also uses the properties of the first row returned by the query to filter which other rows will display in the map to meet ArcGIS requirements. For example, ArcGIS supports only one spatial reference in a spatial table. If the features in your feature class use different spatial reference IDs (SRID), by default, the query layer will return only those rows that have the same SRID as the first feature returned by the query. Similarly, ArcGIS supports only one geometry type per table. By default, only features with the same geometry type as the first row returned by the query will display on the map. To use a different SRID or geometry type, define the spatial properties of the query layer.

Specify a unique identifier

The unique identifier is one or many columns used by ArcGIS to identify each row in the table.

During validation, ArcGIS attempts to identify a single unique identifier column for the table. If it finds a qualifying column, you can use that for the query layer. If it cannot find a unique identifier or you want to use a different unique identifier, you can define that in the next panel of the dialog box.

The query layer unique identifier field must be a single, not null, unique, database-maintained column if you will publish a feature layer that contains the query layer.

Define spatial properties (optional)

For feature classes or views that contain a spatial column, you can define the following or use the defaults detected by ArcGIS when you validate:

Geometry type—This determines whether the layer stores point, multipoint, line, or polygon features.
Z and M values—These options determine whether the layer will contain features that have z- or m-coordinates.
Spatial reference—This is the coordinate system and other related spatial properties for the layer.
Sometimes, ArcGIS cannot accurately determine the spatial reference based on the current SRID value set on the features in the database. When that happens, the query layer will have an unknown spatial reference and you must define one. This does not apply to cloud data warehouses, for which you cannot define a spatial reference.
Note:
Defining a spatial reference for the query layer does not reproject the data; it only defines the spatial reference that should be used when mapping the results of the query in ArcGIS.
SRID—This is the layer's spatial reference identifier and is used to ensure that only geometries with the same spatial reference identifier are returned by the query. The SRID value will be blank if the query layer does not have a spatial field. If this value is set, all features that do not have the specified geometry SRID value will be excluded from the result set. If the data in your database uses a custom SRID, you must provide the SRID in the spatial properties for the query layer. This does not apply to cloud data warehouses.

Create a query layer for a database table

If the table you want to query does not contain hundreds of thousands of records, you can drag the table onto the map to automatically create a query layer. For larger datasets, use the New Query Layer dialog box to define the query layer.

Drag the table onto the map

When you drag a database feature class, table, or view onto the map from a database connection in the Catalog pane, ArcGIS Pro automatically creates a query layer that selects all rows and fields from that feature class, table, or view. This only applies to databases, though, not enterprise geodatabases.

ArcGIS Pro calculates the extent of the layer when you drag it into the map. When you add a table that contains a lot of features, extent calculation can take a while. If you know the extent of the data or want to use the extent of your spatial reference instead of waiting for the extent to be calculated, you can do so. Click the appropriate button on the Calculate Extent dialog box. If you click Input Extent, you must type a valid extent that includes all the features in the table.

After you add the layer to the map, you can alter the query layer if needed.

Define a query

Follow these steps to define a query layer in a map in an ArcGIS Pro project using the New Query Layer dialog box:

Open the map in ArcGIS Pro to which you want to add the query layer.
Click the Add Data button on the Map ribbon and select the Query Layer button from the drop-down list.
The New Query Layer dialog box appears.
Specify a connection using one of the following methods:
- If the connection file (.sde) already exists in the project, select it from the drop-down list.
- If the connection does not exist in the project, select the new database connection button and create a connection.
In the Name text box, specify a name for the query that will be created.
This is the name that will appear in the Contents pane of the map.
Type or paste an SQL query in the Query text box.
To see a list of tables and their columns, check the List of Tables check box. You can double-click a table in the List of Tables section to add all the columns in the table to the Query text box and edit the query in the Query text box. For example, you can delete the columns from the query that you do not want included and add clauses to the query to limit the data included in the query layer.
Choose how the layer's spatial properties will be defined.
- Choose Let ArcGIS Pro discover spatial properties for the layer (the default option) to allow ArcGIS Pro to use the spatial properties of the first row returned by the query. ArcGIS Pro discovers these properties when you validate the SQL statement.
- Choose Define spatial properties for the layer if you want the layer to include features with spatial properties that are different than those of the first row.
You must validate that the query syntax is correct and that ArcGIS can use the query. Click Validate to initiate the validation process.
Validation executes the query in the database and verifies whether the result set from the query meets the data modeling standards enforced by ArcGIS. A query layer is not added to the map until it is valid.
Rules for validation are as follows:
- The result set must have, at most, one spatial field.
- The result set must have, at most, one spatial reference.
- The result set must have only one shape type.
- The result set cannot have any field types not supported by ArcGIS.
If the validation fails for any reason, an error message is returned so you can modify the query.
Validation is especially important when working with data in spatial databases that do not enforce the same standards as ArcGIS.
When the query passes validation, click Next.
Select the unique identifier field for the query layer or use the field that ArcGIS Pro selected.
All features in the map need a unique identifier. Therefore, your query layer must contain a unique identifier field that contains a value for each feature. See more about choosing a unique identifier field for a query layer.
Select the geometry type and spatial reference.
These parameters may already be set if you chose to allow ArcGIS Pro to determine the spatial properties for you.
1. Choose the geometry type from the drop-down menu.
2. Click the Coordinate System button to choose the coordinate system of the spatial reference used by the data, or import the spatial reference from an existing dataset. Remember, this does not reproject the data.
If you chose to let ArcGIS Pro to define spatial properties in step 6, click Finish to add the query layer to the map.
If you chose to define the spatial properties of the layer in step 6, an additional section appears on the dialog box shown above. In this section—Layer Extent Properties—you must specify the query layer's spatial extent by choosing one of the following:
- Input Extent—When you click Next, you must type the extent. The extent you specify must be valid and include all the features in the table.
- Use Map Extent—The extent of the current map will be used for the query layer extent. Click Next to review the map extent values.
- Use Spatial Reference Extent—The extent of the spatial reference of the data is used for the query layer extent. For some spatial references, this is the entire globe. Click Next and review the spatial reference extent values.
When you finish setting or reviewing the layer extent, click Finish to add the query layer to the map.