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 or personal geodatabases are not valid input workspaces for this tool.
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
It will then be the up to the user to change any of these desired values before executing the tool.
For geographic data, each record in the result returned from the SQL statement should have an associated SRID (spatial reference identifier). 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 will only support one value. This tool provides the ability to choose the SRID value or will default to the SRID from the first record in the result set.
Syntax
Parameter | Explanation | Data Type |
input_database |
The database connection file that contains the data to be queried. | Workspace |
out_layer_name |
Output name for the feature layer or table view to be created. | String |
query | SQL statement defining 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 can be used to generate a dynamic, unique row identifier. | String |
shape_type (Optional) |
The shape type for 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.
| String |
srid (Optional) |
Sets the SRID (spatial reference identifier) 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) |
Sets 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 |
Code Sample
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("Database Connections/moab.sde",
"Slickrock",
"select * from moabtrails where name = 'slickrock'",
"OBJECTID",
"POLYLINE",
"32611",
sr)
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("Database Connections/moab.sde",
"Single Track",
"select * from moabtrails where type = 'single'",
"UID;name",
"POLYLINE",
"32611",
sr)