Create a query layer

Use the Add Data button on the Map tab to create a query layer in your map.

Tip:

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.

The following is a summary of how to create a query layer using the New Query Layer dialog box:

1. Connect to the database.

   The first step in creating a query layer in ArcGIS is to make a connection 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, or you can create the connection from the New Query Layer dialog box.

2. Choose a table.

   After a connection to the database is established, a list of tables and views found in that database appears on the New Query Layer dialog box. 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.

3. Define an SQL query.

   You specify an SQL query in the Query text box.

   When building a query, a whole table can be added 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.

   SQL syntax specific to the database should be used when building a query layer. A common example would be as follows: SELECT * FROM Test.myuser.US_States. This would result in a query layer containing all rows from the US_States table. In the map, this would display all the United States.

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

4. Validate the SQL statement.

   Once the query is created, it must be validated. During validation, ArcGIS attempts to determine the properties of the query layer based on the first row returned in the table.

   During validation, ArcGIS attempts to determine the properties of the query layer based on the first row returned in the table.

   The properties of a query layer are used to filter the rows returned to ArcGIS from the database. For example, if the features in your feature class use different SRIDs, the SRID property set on the query layer will be used to prevent any rows in the database table that don't match that SRID from being displayed on the map.

5. Specify a unique identifier or use the identifier found by ArcGIS during validation.

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

6. 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.
   • 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 manually define one. It is important to note that defining a spatial reference for the query layer does not reproject the data; it just defines the spatial reference that should be used when mapping the results of the query within 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.

Follow these steps to create a query layer in the map:

1. Query layers can be added to the map by clicking the Add Data button on the Map ribbon and selecting the Query Layer button from the drop-down list. This will open the query layer window.
2. On the New Query Layer dialog box, specify a name in the Name text box for the query that will be created. This is the name that will appear in the Contents pane.
3. When the new query layer window appears, you must select a database connection where your data is stored. If the connection already exists in the project, select the connection from the drop-down list. If the connection does not exist in the project, select the new database connection button and create a connection.
4. Enter an SQL query in the Query text box.

   After a connection to the database is established, a list of tables and views found in that database populates the left window of the dialog box. You can select a table to work with by clicking the table name; the table columns will be displayed in the right window.

   When building a query, a whole table can be added to the Query text box by either double-clicking it or dragging it from the List of Tables window into the Query text box.

   When you select one of the tables, the columns for that table are displayed. You can add specific columns in a table to the query by double-clicking the column names or by dragging them from the Columns window into the Query text box.

5. Choose whether you want to define the spatial properties for the layer, or use the default option to allow ArcGIS Pro do it for you.

   During the validation process, ArcGIS sets the dimensionality, geometry type, spatial reference, SRID, and unique identifier properties on the query layer. These values are based on the first row returned in the query. If you want to manually define these properties, select the Define spatial properties for the layer option.

6. Once the query is created, it must be validated. Click Validate to confirm that the query syntax is correct and returns data that can be used by ArcGIS. The validation process 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.

7. When you have successfully validated your query, click Next.

   

8. 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.

9. Define the spatial properties of the query layer by selecting the geometry type and spatial reference. These parameters are already set if you chose to allow ArcGIS Pro determine the spatial properties for you.
   1. Choose the geometry type from the drop-down menu.
   2. Click to choose the coordinate system to be used for the spatial reference, or import the spatial reference from an existing dataset.

   

10. If you chose to define the spatial properties of the layer in step 5, you need to select an option from the Layer Extent Properties. You can choose to type the extent, use the map extent, or use the spatial reference extent. If you type the extent, the extent you specify must be valid and include all the features in the table. Click Next to provide the extent values or to review the map or spatial reference extent values if you did not choose to provide your own values.
11. Click Finish to add the query layer to the map.

For information on changing query layer properties, see Modify a query layer.