Label | Explanation | Data Type |
Target Feature Class | The feature class or spatial table from an enterprise database. | Feature Class |
Target Join Field | The field in the target feature class on which the join will be based. | Field |
Related Table
| The input table containing the fields that will be used to calculate statistics. Statistics are joined to the Output Layer value. | Table; Feature Class |
Related Join Field | A field in the summary table that contains the values on which the join will be based. Aggregation or summary statistics are also calculated separately for each unique attribute value from this field. | Field |
Output Layer | The output name of the query layer that will be created. | Feature Layer |
Summary Field(s) (Optional) | Specifies the numeric field or fields containing the attribute values that will be used to calculate the specified statistic. Multiple statistic and field combinations can be specified. Null values are excluded from all statistical calculations. The output layer will include a ROW_COUNT field showing total count (or frequency) of each unique value from the Related Join Field value. The difference between the ROW_COUNT field and the Count statistic type is that ROW_COUNT includes null values while Count excludes null values. Available statistics types are as follows:
| Value Table |
Parameter Definitions (Optional) | Specifies one or more query parameters for criteria or conditions; records matching these criteria are used while computing aggregated results. A query parameter is similar to an SQL statement variable for which the value is defined when the query is run. This allows you to dynamically change query filters for the output layer. You can think of a parameter as a predicate or condition in a SQL where clause. For example Country_Name = 'Nigeria' in a SQL where clause is called a predicate in which the = is a comparison operator, Country_Name is a field name on the left, and 'Nigeria' is a value on the right. When you define more than one parameter, you must specify a logical operator between them (such as AND, OR, and so on). Learn more about defining parameters in a query layer When not specified, all records from the related table will be used in computing aggregated or summary results. The two parameter definition types are the following:
The following properties are available:
| Value Table |
Unique Identifier Field(s)
(Optional) | The unique identifier fields that will be used to uniquely identify each row in the table. | 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. By default, the shape type of the first record in the result set will be used. This parameter is ignored if the result set of the query does not return a geometry field.
| String |
Spatial Reference ID (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. By default, the SRID value of the first record in the result set will be used. 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. By default, the spatial reference of the first record in the result set will be used. This parameter is ignored if the result set of the query does not return a geometry field. | Spatial Reference |
Coordinates include M values
(Optional) | Specifies whether the output layer will include linear measurements (m-values).
| Boolean |
Coordinates include Z values
(Optional) | Specifies whether the output layer will include elevation values (z-values).
| Boolean |
Extent
(Optional) | Specifies the extent of the layer. The extent must include all features in the table.
| Extent |
Summary
Creates a query layer that summarizes, aggregates, and filters DBMS tables dynamically based on time, range, and attribute queries from a related table, and joins the result to a feature layer.
Usage
Query layers will only work with enterprise databases. File geodatabases are not a valid input workspace for this tool.
Aggregate results are always computed dynamically at the database level.
The Output Layer value will consist of fields containing the result of the statistical operation. The count statistic is included in the ROW_COUNT field by default
The statistical operations that are available with this tool are count, sum, average, minimum, maximum, and standard deviation.
A field will be created for each statistic type using the following naming convention: COUNT_<field>, SUM_<field>, AVG_<field>, MIN_<field>, MAX_<field>, and STDDEV_<field>, (where <field> is the name of the input field for which the statistic is computed).
The Related Join Field value is used in the Group By clause in the SQL statement generated by this tool. Statistics will be calculated separately for each unique attribute value from the Related Join Field value.
The layer that is created by the tool is temporary and will not persist after the session ends unless the project is saved, the layer is saved to a layer file, or the data is persisted by making a copy using Copy Rows or Copy Features.
Parameters
arcpy.management.MakeAggregationQueryLayer(target_feature_class, target_join_field, related_table, related_join_field, out_layer, {statistics}, {parameter_definitions}, {oid_fields}, {shape_type}, {srid}, {spatial_reference}, {m_values}, {z_values}, {extent})
Name | Explanation | Data Type |
target_feature_class | The feature class or spatial table from an enterprise database. | Feature Class |
target_join_field | The field in the target feature class on which the join will be based. | Field |
related_table | The input table containing the fields that will be used to calculate statistics. Statistics are joined to the out_layer value. | Table; Feature Class |
related_join_field | A field in the summary table that contains the values on which the join will be based. Aggregation or summary statistics are also calculated separately for each unique attribute value from this field. | Field |
out_layer | The output name of the query layer that will be created. | Feature Layer |
statistics [[statistic_type, field],...] (Optional) | Specifies the numeric field or fields containing the attribute values that will be used to calculate the specified statistic. Multiple statistic and field combinations can be specified. Null values are excluded from all statistical calculations. The output layer will include a ROW_COUNT field showing total count (or frequency) of each unique value from the related_join_field value. The difference between the ROW_COUNT field and the COUNT statistic type is that ROW_COUNT includes null values while COUNT excludes null values.
| Value Table |
parameter_definitions [[parameter_def_type, name, alias, field_or_expression, data_type, start_value, end_value, operator, default_value, operator_for_next_parameter],...] (Optional) | Specifies one or more query parameters for criteria or conditions; records matching these criteria are used while computing aggregated results. A query parameter is similar to an SQL statement variable for which the value is defined when the query is run. This allows you to dynamically change query filters for the output layer. You can think of a parameter as a predicate or condition in a SQL where clause. For example Country_Name = 'Nigeria' in a SQL where clause is called a predicate in which the = is a comparison operator, Country_Name is a field name on the left, and 'Nigeria' is a value on the right. When you define more than one parameter, you must specify a logical operator between them (such as AND, OR, and so on). Learn more about defining parameters in a query layer When not specified, all records from the related table will be used in computing aggregated or summary results. The two parameter definition types are the following:
The following properties are available:
| Value Table |
oid_fields [oid_fields,...] (Optional) | The unique identifier fields that will be used to uniquely identify each row in the table. | 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. By default, the shape type of the first record in the result set will be used. This parameter is ignored if the result set of the query does not return a geometry field.
| 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. By default, the SRID value of the first record in the result set will be used. 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. By default, the spatial reference of the first record in the result set will be used. This parameter is ignored if the result set of the query does not return a geometry field. | Spatial Reference |
m_values (Optional) | Specifies whether the output layer will include linear measurements (m-values).
| Boolean |
z_values (Optional) | Specifies whether the output layer will include elevation values (z-values).
| Boolean |
extent (Optional) | Specifies the extent of the layer. The extent must include all features in the table.
| Extent |
Code sample
The following Python window script demonstrates how to compute total crimes for each district from a point feature class, and join the result to a police district feature class.
import arcpy
arcpy.env.workspace = "C:/data/localhost.sde"
arcpy.management.MakeAggregationQueryLayer(
"PoliceDistricts", "district", "Crime_locations", "PdDistrict", "SF_Crimes")
The following Python window script demonstrates how to compute total crimes for each district and for each crime type from a point feature class, and join the result to a police district feature class.
import arcpy
arcpy.env.workspace = "C:/data/localhost.sde"
arcpy.management.MakeAggregationQueryLayer(
"PoliceDistricts", "district", "Crime_locations", "PdDistrict", "SF_Crimes",
None,
"DISCRETE crime_type # Category STRING # # 'INCLUDE VALUES' 'VANDALISM, BURGLARY/THEFT' NONE")
The following Python window script demonstrates how to compute total and average rainfall from a time series table and join the result to a water stations feature class.
import arcpy
arcpy.env.workspace = "C:/data/localhost.sde"
arcpy.management.MakeAggregationQueryLayer(
"weather_stations", "station_id", "observed_rainfall", "station_id",
"Total_Rainfall", [["SUM", "rainfall_inch"], ["MIN", "rainfall_inch"]])
The following Python window script demonstrates how to compute total and average rainfall from a time series table for any time window and join the result to a water stations feature class.
import arcpy
arcpy.env.workspace = "C:/data/localhost.sde"
arcpy.management.MakeAggregationQueryLayer(
"weather_stations", "station_id", "observed_rainfall", "station_id",
"Total_Rainfall", [["SUM", "rainfall_inch"], ["MIN", "rainfall_inch"]],
"RANGE TimeVar # collection_date DATE 1/1/2020 12/1/2020 NONE # NONE")