Skip to main content

Make Query Layer (Data Management Tools)

Summary

Creates a query layer from a DBMS table based on an input SQL select statement.

Usage

  • Query layers only work with enterprise databases. File geodatabases are not a valid input workspace for this tool.

  • The layer that is created by the tool is temporary and will not persist after the session ends unless the project is saved or the data is persisted by making a copy using Copy Rows or Copy Features.

  • If the result of the SQL query entered returns a spatial column, the output will be a feature layer. If the SQL query does not return a spatial column, the output will be a stand-alone table.

  • The connection files necessary for this tool can be created using the Create Database Connection tool.

  • If the result of the SQL query does not return any rows, the output query layer will be empty, only containing the schema of the columns returned by the query. In this case, if the columns returned contain a spatial column, the tool will use the following defaults to create the query layer:

    • Geometry type—POINT

    • SRID—1

    • Spatial Reference—NAD1983

    Then you need to determine whether any of these values should be changed before running the tool.

  • For geographic data, each record in the result returned from the SQL statement should have an associated spatial reference identifier (SRID). The SRID value is used by the database to determine the spatial reference for the data. The specific functional differences for the SRID will vary between each DBMS platform. Some DBMS platforms support multiple SRID values within the same table. ArcGIS only supports one value. This tool allows you to choose the SRID value or it will default to the SRID from the first record in the result set.

Parameters

Label Explanation Data type

Input Database Connection

The database connection file that contains the data to be queried.

Workspace

Output Layer Name

The output name of the feature layer or table view to be created.

String

Query

The SQL statement that defines the select query to be run in the database.

Note:

This string must pass validation before the remaining controls will be enabled. Validation will be triggered when you click outside this input box. The validation process runs the query in the database and verifies whether the result of the SQL query meets the data modeling standards enforced by ArcGIS. If the validation fails, the tool will return a warning. The only exception is for ModelBuilder, in which case validation will not be triggered if the input is derived data.

Rules for validation are as follows:

  • The result of the SQL query must have only one spatial field.

  • The result of the SQL query must have only one spatial reference.

  • The result of the SQL query must have only one entity type, such as point, multipoint, line, or polygon.

  • The result of the SQL query cannot have any field types that are not supported by ArcGIS. ArcGIS field data types describes the field types supported in ArcGIS.

Validation is especially important when working with data in spatial databases that do not enforce the same standards as ArcGIS.

String

Unique Identifier Field(s)

(Optional)

One or more fields from the SELECT statement SELECT list that will generate a dynamic, unique row identifier.

String

Shape Type

(Optional)

Specifies the shape type of the query layer. Only those records from the result set of the query that match the specified shape type will be used in the output query layer. Tool validation will attempt to set this property based on the first record in the result set. This can be changed before running the tool if it is not the correct output shape type. This parameter is ignored if the result set of the query does not return a geometry field.

  • Point—The output query layer will use point geometry.

  • Multipoint—The output query layer will use multipoint geometry.

  • Polygon—The output query layer will use polygon geometry.

  • Polyline—The output query layer will use polyline geometry.

String

SRID

(Optional)

The spatial reference identifier (SRID) value for queries that return geometry. Only those records from the result set of the query that match the specified SRID value will be used in the output query layer. Tool validation will attempt to set this property based on the first record in the result set. This can be changed before running the tool if it is not the correct output SRID value. This parameter is ignored if the result set of the query does not return a geometry field.

String

Coordinate System

(Optional)

The coordinate system that will be used by the output query layer. Tool validation will attempt to set this property based on the first record in the result set. This can be changed before running the tool if it is not the correct output coordinate system. This parameter is ignored if the result set of the query does not return a geometry field.

Spatial Reference

Define the spatial properties of the layer

(Optional)

Specifies how the spatial properties for the layer will be defined.

During the validation process, dimensionality, geometry type, spatial reference, SRID, and unique identifier properties will be set on the query layer. These values are based on the first row returned in the query. To manually define these properties instead of the tool querying the table to get them, use the default value for this parameter.

  • Checked—Manually define the spatial properties of the layer. This is the default.

  • Unchecked—Layer properties will be determined based on the first row returned in the query.

Boolean

Coordinates include M values

(Optional)

Specifies whether the layer will have m-values.

  • Checked—The layer will have m-values.

  • Unchecked—The layer will not have m-values. This is the default.

Boolean

Coordinates include Z values

(Optional)

Specifies whether the layer will have z-values.

  • Checked—The layer will have z-values.

  • Unchecked—The layer will not have z-values. This is the default.

Boolean

Extent

(Optional)

The extent of the layer. This parameter is only used if the Define the spatial properties of the layer parameter is checked (spatial_properties = DEFINE_SPATIAL_PROPERTIES in Python). The extent must include all features in the table.

  • Current Display Extent Map View—The extent will be based on the active map or scene.

  • Draw Extent Square and Finish—The extent will be based on a rectangle drawn on the map or scene.

  • Extent of a Layer Layer—The extent will be based on an active map layer. Choose an available layer or use the Extent of data in all layers option. Each map layer has the following options:

    • All Features Select All—The extent of all features.

    • Selected Features Area from Selected Features—The extent of the selected features.

    • Visible Features Extent Indicator—The extent of visible features.

  • Browse Browse—The extent will be based on a dataset.

  • Clipboard Paste—The extent can be copied to and from the clipboard.

    • Copy Extent Copy—Copies the extent and coordinate system to the clipboard.

    • Paste Extent Paste—Pastes the extent and coordinate system from the clipboard. If the clipboard does not include a coordinate system, the extent will use the map's coordinate system.

  • Reset Extent Reset—The extent will be reset to the default value.

When coordinates are manually provided, the coordinates must be numeric values and in the active map's coordinate system. The map may use different display units than the provided coordinates. Use a negative value sign for south and west coordinates.

Extent

Derived output

Label Explanation Data type

Output Layer

The output query layer.

Table View

Environments

Current Workspace, Output Coordinate System

Licensing information

  • Basic: Yes
  • Standard: Yes
  • Advanced: Yes