Create a query layer for data in a relational database—ArcGIS AllSource

You can drag a relational 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 a map in ArcGIS AllSource.

Tip:

This page explains how to create a query layer for a table in a relational 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 relational database using the New Query Layer dialog box.

Connect to the relational database

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

You can create a database connection in the Catalog pane, as explained in Connections to relational databases in ArcGIS AllSource, 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 them or dragging them 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 cast to a data type that ArcGIS supports. See Database data types supported in ArcGIS for a list of supported data types per database.

Tip:

ArcGIS AllSource added support for the following data types:

Big integer
Date only
Time only
Timestamp 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. To retain the data type mapping used in ArcGIS AllSource 3.1 and earlier, you can configure a Map and Scene setting for the ArcGIS AllSource installation.

Support for the new data types varies by database and cloud data warehouse platform. See Database 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 relational 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, as described in the first section below. 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 (.sde) in the Catalog pane, ArcGIS AllSource 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 AllSource 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 the feature class's 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.

ArcGIS AllSource also identifies the spatial properties of the first row and sets those as the spatial properties for the query layer. Only features with those same properties will draw in the map.

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

Define a query on the New Query Layer dialog box

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

In ArcGIS AllSource, open the map to which you want to add the query layer.
Click the Add Data button in the Layer group on the Map ribbon, and choose Query Layer from the drop-down menu.
The New Query Layer dialog box appears.
Specify a connection by doing one of the following:
- If the connection file (.sde) exists in the project, select it from the drop-down list.
- If the connection does not exist in the project, click the new database connection button and create a connection. After you create the connection, choose it from the drop-down list.
In the Name text box, provide a name for the query layer to 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 AllSource to use the spatial properties of the first row returned by the query. ArcGIS AllSource 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.
Click Validate to ensure that the query syntax is correct and that ArcGIS can use the query.
The validation process runs the query in the database to confirm that 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 AllSource selected.
All features in the map need a unique identifier. Therefore, the query layer must contain a unique identifier field that contains a value for each feature. Learn 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 AllSource to define the spatial properties.
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. This does not reproject the data.
If ArcGIS AllSource defined the spatial properties in step 6, click Finish to add the query layer to the map.
The next two steps do not apply if ArcGIS AllSource defined the spatial properties.
If you defined 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—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.