Make Query Layer (Data Management)

Summary

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

Usage

  • Query layers will 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 executing 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.

Syntax

arcpy.management.MakeQueryLayer(input_database, out_layer_name, query, {oid_fields}, {shape_type}, {srid}, {spatial_reference}, {spatial_properties}, {m_values}, {z_values}, {extent})
ParameterExplanationData Type
input_database

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

Workspace
out_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 issued to the database.

String
oid_fields
[oid_fields,...]
(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 executing the tool if it is not the desired output shape type. This parameter is ignored if the result set of the query does not return a geometry field.

  • POINTThe output query layer will use point geometry.
  • MULTIPOINTThe output query layer will use multipoint geometry.
  • POLYGONThe output query layer will use polygon geometry.
  • POLYLINEThe 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 executing the tool if it is not the desired output SRID value. This parameter is ignored if the result set of the query does not return a geometry field.

String
spatial_reference
(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 executing the tool if it is not the desired output coordinate system. This parameter is ignored if the result set of the query does not return a geometry field.

Spatial Reference
spatial_properties
(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.

  • DEFINE_SPATIAL_PROPERTIESManually define the spatial properties of the layer. This is the default.
  • DO_NOT_DEFINE_SPATIAL_PROPERTIESLayer properties will be determined based on the first row returned in the query.
Boolean
m_values
(Optional)

Specifies whether the layer will have m-values.

  • INCLUDE_M_VALUESThe layer will have m-values.
  • DO_NOT_INCLUDE_M_VALUESThe layer will not have m-values. This is the default.
Boolean
z_values
(Optional)

Specifies whether the layer will have z-values.

  • INCLUDE_Z_VALUESThe layer will have z-values.
  • DO_NOT_INCLUDE_Z_VALUESThe layer will not have z-values. This is the default.
Boolean
extent
(Optional)

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

  • MAXOF—The maximum extent of all inputs will be used.
  • MINOF—The minimum area common to all inputs will be used.
  • DISPLAY—The extent is equal to the visible display.
  • Layer name—The extent of the specified layer will be used.
  • Extent object—The extent of the specified object will be used.
  • Space delimited string of coordinates—The extent of the specified string will be used. Coordinates are expressed in the order of x-min, y-min, x-max, y-max.
Extent

Derived Output

NameExplanationData Type
out_layer

The output query layer.

Table View

Code sample

MakeQueryLayer example 1 (Python window)

The following Python window script demonstrates how to use the MakeQueryLayer tool in immediate mode.

import arcpy

sr = arcpy.SpatialReference("WGS 1984 UTM Zone 12N")

arcpy.MakeQueryLayer_management("Connections/moab.sde",
                                "Slickrock",
                                "select * from moabtrails where name = 'slickrock'",
                                "OBJECTID",
                                "POLYLINE",
                                "32611",
                                sr)
MakeQueryLayer example 2 (stand-alone script)

The following stand-alone script demonstrates how to use the MakeQueryLayer tool.

# Name: MakeQueryLayer.py
# Description: Creates an output query layer based on a where clause.
#   This example shows how to create a spatial reference object using the
#   name of a coordinate system. It also demonstrates how to use two fields
#   to generate a dynamic unique row identifier for the query layer.


# Import system modules
import arcpy

# Create the spatial reference for the output layer.
sr = arcpy.SpatialReference("WGS 1984 UTM Zone 12N")

# Run the tool
arcpy.MakeQueryLayer_management("Connections/moab.sde",
                                "Single Track",
                                "select * from moabtrails where type = 'single'",
                                "UID;name",
                                "POLYLINE",
                                "32611",
                                sr)

Licensing information

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

Related topics