Modify a query layer

After you add a query layer to a map, you may need to change the SQL query, the ID, or the spatial properties. To do this, open the Edit Query Layer dialog box from the Source tab of the query layer's Layer Properties dialog box.

Tip:

These instructions discuss changing the query layer-specific properties that you set on the Edit Query Layer dialog box. Other layer property changes can be made in the Layer Properties dialog box. See Set layer properties for information.

When you add a spatial table stored in a database to the map, it creates a query layer with default properties as determined by the first row in the table. These properties may not be what you need for a specific workflow, however. For example, you can model a spatial table containing a collection of cities in the database as both a point and a polygon geometry type. But feature layers in ArcGIS can have only a single geometry type, so the first row in the table provides the default geometry type property. In this example, if the first row in the spatial table containing cities data returns a point geometry, the cities query layer will only display point features. If you prefer to view the polygon features that are stored in the cities table, modify the query layer properties on the Edit Query Layer dialog box and change the geometry type to polygon.

Other properties—such as a query layer's dimensionality, spatial reference, SRID, and unique identifier field—are also determined by the first row returned from the spatial table. Similar to the geometry type, you can access these properties by clicking Next on the Edit Query Layer dialog box and modify them.

When you change the dimensionality of the query layer, the geometry field values of the displayed features are modified to match that change.

For example, for a layer defined with x-, y-, z-, and m-dimensionality in the database, if you edit the query layer and uncheck the Coordinates include M values. Used to store route data. and Coordinates include Z values. Used to store 3D data. options under Spatial Properties, all the features in the layer will display only the x- and y-values. Alternatively, if you edit the query layer and check the Coordinates include M values. Used to store route data. and Coordinates include Z values. Used to store 3D data. options, all the features will display x-, y-, z-, and m-values. If you check those options and a feature does not contain a z- or m-value in the database, those values will appear as null in the query layer.

When you change the spatial reference or SRID of the query layer in the Spatial Properties of the Edit Query Layer dialog box, the features displayed in ArcGIS AllSource are filtered based on that spatial reference or SRID value.

Note:

The properties you can alter vary depending on the query layer source.

Follow these steps to alter the definition and properties of a query layer:

1. Open the map in ArcGIS AllSource that contains the query layer.
2. Right-click the layer in the Contents pane and choose Properties from the context menu.
3. Click the Source tab on the Layer Properties dialog box.

   The Data Source table contains a property named Query, which displays the current SQL query for the layer.

4. Click the Edit Query button.

   

   The Edit Query Layer dialog box appears.

5. Alter the SQL query in the first window. Click Validate to ensure the SQL is correct.

   If you defined a materialized view for the query layer, altering and validating the SQL query will re-create the view.

   Note:

   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 settings on the Map and Scene tab 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.

You must proceed to the next window of the Edit Query Layer dialog box. However, the input you must provide on that window depends on which of the following two options you choose before you click Next:
• Let ArcGIS Pro discover spatial properties for the layer—If you choose this option, the existing unique ID and spatial properties that were set when you created the query layer remain set, but you can change them. Use this option if most of the original settings are sufficient, and you only need to change the unique ID field or a few spatial properties.
• Define spatial properties for the layer—If you choose this option, the existing unique ID field definition is preserved (though you can change it), but all existing spatial property settings and the layer extent are removed, and you must specify new spatial properties. Only use this option if you know the specific values, such as the layer extent coordinates or the spatial reference of the data, to assign to the query layer.
6. Choose one of the options described above and click Next.
7. If you chose the Let ArcGIS Pro discover spatial properties for the layer option, change which field or fields are used for the unique identifier or specify different spatial properties for the layer, and click Finish.

   Clicking Finish on the Edit Query Layer dialog box takes you back to the Layer Properties dialog box.

   The layer extent is automatically recalculated based on the changes you made to the query layer definition. However, the query layer definition changes and the extent changes are not saved until you click Apply or OK on the Layer Properties dialog box. To revert to the previous query layer definition, click Cancel on the Layer Properties dialog box instead.

8. If you chose the Define spatial properties for the layer option, follow these steps:
   1. Define which features are included in the layer based on their coordinate values and geometry type.
   2. Define the spatial reference of the data in the layer.
   3. Choose one of the following options to define the layer's spatial extent:

      • Input Extent—After you click Next, you must type the coordinates to use for the extent. Type the values in the extent fields as follows:
        • Top—Type the northern-most coordinate for the extent.
        • Right—Type the eastern-most coordinate for the extent.
        • Bottom—Type the southern-most coordinate for the extent.
        • Left—Type the western-most coordinate for the extent.
      • Use Map Extent—After you click Next, the extent fields are populated with the coordinates for the extent of the current map. You can edit these values by typing in the fields.
      • Use Spatial Reference Extent—After you click Next, the extent fields are populated with the coordinates for the spatial extent of the spatial reference you defined for the layer. You can edit these values by typing in the fields.

   4. Click Finish to close the Edit Query Layer dialog box.

   Clicking Finish on the Edit Query Layer dialog box takes you back to the Layer Properties dialog box. The layer extent values are updated.

   Note:

   The changes you made to the query layer definition and layer extent are not saved until you click Apply or OK on the Layer Properties dialog box. To revert to the previous query layer definition, click Cancel on the Layer Properties dialog box instead.