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. If you want 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.

Summary and explanation of creating a query layer for database data

The following sections provide a summary of how to create 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.

Choose a table

After you establish a connection to the database, a list of tables and views appears on the New Query Layer dialog box. These are the tables and views that the credentials you provided in the database connection allow you to access. When you select one of the tables, the columns for that table are displayed.

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 more information.

Define an SQL query

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

When building a query, you can add a whole table to the Query text box by either double-clicking it or dragging it from the List of Tables window into the Query text box. Likewise, you can add specific columns in a table to the query by double-clicking them or dragging them from the Columns window into the Query text box. You can also type specific queries or cut and paste a query from an external application into the Query text box.

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 a 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. If you are adding 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.
This opens the New Query Layer dialog box.
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.
A list of the tables and views to which you have access in the database populates the List of Tables section in the dialog box. You can select a table or view to work with by clicking its name; the columns display in the Columns window.
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.
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 alter the query from there. For example, you can delete columns from the query 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 does not enforce the same standards as ArcGIS.
When the query passes validation, click Next.
Select the unique identifier field for the query layer.
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 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.