- URL:
- https://<featureLayer-url>/queryDateBins
- Methods:
GET- Required Capability:
- Query
- Version Introduced:
- 10.9.1
Description
The query operation is performed on a feature service layer resource. This operation returns a histogram of features divided into bins based on a date field. The response can include statistical aggregations for each bin, such as a count or sum, and may also include the aggregate geometries (in other words, centroid) for point layers.
The parameters define the bins, the aggregate information returned, and the included features. Bins are defined using the bin parameter. The out and return parameters define the information each bin will provide. Included features can be specified by providing a time extent, where condition, and a spatial filter, similar to a query operation.
The contents of the bin parameter provide flexibility for defining bin boundaries. The bin parameter's unit property defines the time width of each bin, such as one year, quarter, month, day, or hour. Fixed bins can use multiple units for these time widths. The offset property defines an offset within that time unit. For example, if your bin unit is day , and you want bin boundaries to go from noon to noon on the next day, the offset would be 12 hours.
Features can be manipulated with the time , where , and geometry parameters. By default, the result will expand to fit the feature's earliest and latest point of time. The time parameter defines a fixed starting point and ending point of the features based on the field used in bin . The where and geometry parameters allow additional filters to be put on the data.
This operation is only supported on feature services using a spatiotemporal data store. As well, the service property supports must be set to true .
To use pagination with aggregated queries on hosted feature services in ArcGIS Enterprise, the supports property must be true on the layer. Hosted feature services using a spatiotemporal data store do not currently support pagination on aggregated queries.
New at 11.1
The query operation allows you to set the name of the output boundary field by setting a bin input property.
Request parameters
| Parameter | Details |
|---|---|
(Required) | The date field used to determine which bin each feature falls into. Syntax Example |
(Required) | A JSON parameter that describes the characteristics of the bins, such as the size of the bin and its starting position. The size of each bin is determined by the number of time units denoted by the The starting position of the bin is the earliest moment in the specified unit. For example, each year begins at midnight of January 1. An offset inside the bin parameter can provide an offset to the starting position of the bin. This can contain a positive or negative integer value. A bin can take two forms: either a calendar bin or a fixed bin. A calendar bin is aware of calendar-specific adjustments, such as daylight saving time and leap seconds. Fixed bins are, by contrast, always a specific unit of measurement (for example, 60 seconds in a minute, 24 hours in a day) regardless of where the date and time of the bin starts. For this reason, some calendar-specific units are only supported as calendar bins. The The Syntax Examples |
| Defines the beginning and end of the data in If the first or last bin does not fall evenly into the division of data, the bin will contain data that is cropped to the time extent. For example, if you have yearly bins from January to December, but the data is defined to be from July to July, the first and last bins will show as a full year but contain only six months of data. If no time is specified, the range of data encompasses the earliest and latest data points. Syntax Example A Example |
| Results can be returned in ascending or descending order. The default is ascending ( Syntax |
(Required) | The definitions for one or more field-based statistics to be calculated. Syntax Example |
| A WHERE clause for the query filter. SQL-92 WHERE clause syntax on the fields in the layer is supported for most data sources. Some data sources have restrictions on what is supported. Hosted feature services in ArcGIS Enterprise running on a spatiotemporal data source only support a subset of SQL-92. For example, spatiotemporal-based feature services support the For information on how to format time and date information, see the Date-time queries section. Examples |
| Returns the geometry centroid associated with all the features in the bin. If Values: |
| The geometry to apply as the spatial filter. The structure of the geometry is the same as the structure of the JSON geometry objects returned by ArcGIS REST API. In addition to the JSON structures, you can specify the geometry of envelopes and points with a simple comma-separated syntax. Syntax Examples |
| The spatial reference of the input geometry. The spatial reference can be specified as either a well-known ID or as a spatial reference JSON object. If the |
| The spatial reference of the returned geometry. The spatial reference can be specified as either a well-known ID or as a spatial reference JSON object. If When using |
| The spatial relationship to be applied to the input geometry while performing the query. The supported spatial relationships include intersects, contains, envelop intersects, within, and so on. The default spatial relationship is intersects ( Values: |
| A JSON object used to project the geometry onto a virtual grid, likely representing pixels on the screen. The properties of the JSON object include Examples |
| This parameter fetches query results by skipping the specified number of records and starting from the next record (that is, Example |
| This parameter fetches query results up to the Example |
| When set to Values: |
| Introduced at ArcGIS Enterprise 11.1. This parameter changes the default name of the Example |
| The response format. The default format is Values: |
Date-time queries
Time zone properties
The date property of the feature service identifies the time zone in which all dates are stored. Both relational and spatiotemporal hosted feature service dates are always stored in UTC.
Date and time format
The INTERVAL syntax can be used in place of the date-time queries and is standardized across all map and feature services. The INTERVAL syntax can be used to specify either the current date or timestamp in the query:
//Date
<DateField> >= CURRENT_DATE -+ INTERVAL '<IntervalValue>' <TimeStampFormat>
//Timestamp
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL '<IntervalValue>' <TimeStampFormat>For the syntax demonstrated above, you can interchange the CURRENT and CURRENT values. Both can be used with + or - of INTERVAL values.
The examples below outline the different ways in which the INTERVAL syntax can be modified for the purposes of your query:
//'DD' Day
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'DD' DAY
//'HH' Hour
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'HH' HOUR
//'MI' Minute
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'MI' MINUTE
//'SS(.FFF)' Second
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'SS(.FFF)' SECOND
//'DD HH' DAY TO HOUR
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'DD HH' DAY TO HOUR
//'DD HH:MI' DAY TO MINUTE
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'DD HH:MI' DTY TO MINUTE
//'DD HH:MI:SS(.FFF)' DAY TO SECOND
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'DD HH:MI:SS(.FFF)' DAY TO SECOND
//'HH:MI' HOUR TO MINUTE
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'HH:MI' HOUR TO MINUTE
//'HH:SS(.FFF)' HOUR TO SECOND
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'HH:SS(.FFF)' HOUR TO SECOND
//'MI:SS(.FFF)' MINUTE TO SECOND
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'MI:SS(.FFF)' MINUTE TO SECONDTo demonstrate the INTERVAL format, the example below uses the INTERVAL syntax to query data gathered over the 3 days, 5 hours, 32 minutes, and 28 seconds:
DateField >= CURRENT_TIMESTAMP - INTERVAL '3 05:32:28' DAY TO SECONDQuantization parameters JSON properties
The following table outlines the JSON properties for the quantization JSON object:
| Property | Description |
|---|---|
| An extent defining the quantization grid bounds. Its spatialReference matches the input geometry spatial reference if one is specified for the query. Otherwise, the extent will be in the layer's spatial reference. |
| Geometry coordinates are optimized for viewing and displaying of data. The view value specifies that geometry coordinates should be optimized for viewing and displaying of data. The edit value specifies that full-resolution geometries should be returned, which can support lossless editing. Value: |
| Integer coordinates will be returned relative to the origin position defined by this property value. The default value is Values: |
| The tolerance is the size of one pixel in the outSpatialReference units. This number is used to convert the coordinates to integers by building a grid with resolution matching the tolerance. Each coordinate is then snapped to one pixel on the grid. Consecutive coordinates snapped to the same pixel are removed to reduce the overall response size. The units of If |
Example usage
The following examples demonstrate two ways to modify the features returned by the query operation.
Example one
Suppose you want to do a daily breakdown of sales for the month of January 2021. You'd like to define your days as starting at 8:00 am America/ time.
You'd start by defining the bins. You want a breakdown along calendar days, so you'll use a calendar bin with the unit type day . By default, daily bins start at 00:00:00 UTC. To get the bins, you want to define your time zone as America/ and shift the time forward 8 hours by providing an offset. This gives you the bin definition.
bin={
"calendarBin": {
"unit": "day",
"timezone": "America/Los_Angeles",
"offset": {
"number": 8,
"unit": "hour"
}
}
}Since you're looking at sales, you'll use the date field as the bin field:
binField=dateSoldYou're not sure that the data stretches across every day of the month. The last two days fall on a weekend, and the stores are only open on weekdays. If you only have data to the January 29, you could miss the last two days from appearing in the result, but you would instead like those to show up as days with zero sales. To ensure that all days in January are displayed, as well as restricting the included features to only January (and to 7:59 am on February 1), you can define a time extent on the data from January 1 at 8:00 a.m. to February 1 at 7:59:59:9999 a.m.:
time=1609516800000, 1612195199999Note that if you wish to restrict the days to January, but to not include the last two days, you can use the where parameter.
You can define what information is returned using the statistics parameters. For analysis, you're interested in seeing the counts and averages of the data. This will return the count of items and the average sales price:
outStatistics=[
{
"statisticType": "count",
"onStatisticField": "objectid",
"outStatisticFieldName": "item_sold_count"
},
{
"statisticType": "avg",
"onStatisticField": "price",
"outStatisticFieldName": "avg_daily_revenue"
}
]Now you have the basics of your result the way you want it. You can further refine results by providing filters on the data, such as filtering certain products using a where filter, or using spatial filters to look at regional data.
Example URL
The following is a sample request URL for the workflow discussed above:
https://machine.domain.com/webadaptor/rest/services/Hosted/Sales21/FeatureServer/0/queryDateBins?binField= dateSold& bin={"calendarBin": {"unit": "day","timezone": "America/Los_Angeles","offset": {"number": 8,"unit": "hour"}}}& outStatistics=[{"statisticType": "count","onStatisticField": "objectid","outStatisticFieldName": "item_sold_count"},{"statisticType": "avg","onStatisticField": "price","outStatisticFieldName": "avg_daily_revenue "}]&where=& time=1609516800000, 1612195199999&binOrder=&geometry=&inSR=&outSR=&spatialRel=esriSpatialRelIntersects&returnCentroid=false&quantizationParameters=&resultOffset=&resultRecordCount=&returnExceededLimitFeatures=false&f=pjsonJSON Response example
The following JSON response is a sample of the information returned from the request:
{
"features": [
{
"attributes": {
"boundary": 1609516800000,
"avg_daily_revenue": 300.40,
"item_sold_count": 79
}
},
{
"attributes": {
"boundary": 1612108800000,
"avg_daily_revenue": null,
"item_sold_count": 0
}
}
],
"fields": [
{
"name": "boundary",
"type": "esriFieldTypeDate"
},
{
"name": "item_sold_count",
"alias": "item_sold_count",
"type": "esriFieldTypeInteger"
},
{
"name": "avg_daily_revenue",
"alias": "avg_daily_revenue",
"type": "esriFieldTypeDouble"
}
],
"exceededTransferLimit": false
}Example two
You want to create yearly bins for Arizona temperature data, beginning from 1/1/1976 and ending on the final date contained in the record data, starting at 5:00 a.m. You want to include only data that contains a temperature reading for each year together with the centroid. To achieve this, the following information is included in your request:
binField=recorded_date
bin={
"calendarBin": {
"unit": "year",
"timezone": "America/Phoenix",
"offset": {
"number": 5,
"unit": "hour"
}
}
}
outStatistics=[
{
"statisticType": "count",
"onStatisticField": "temperature",
"outStatisticFieldName": "results_count"
},
{
"statisticType": "avg",
"onStatisticField": "temperature",
"outStatisticFieldName": "temperature_avg"
}
]
where=temperature is not null
time=189331200000, null
returnCentroid=trueExample URL
The following is a sample request URL for the workflow discussed above:
https://machine.domain.com/webadaptor/rest/services/Hosted/AZTempRec/0/ queryDateBins? binField=recorded_date& bin={"calendarBin": {"unit": "year","timezone": "America/Phoenix","offset": {"number": 5,"unit": "hour"}}}& outStatistics=[{"statisticType": "count","onStatisticField": "temperature","outStatisticFieldName": "results_count"},{"statisticType": "avg","onStatisticField": "temperature","outStatisticFieldName": "temperature_avg"}]&where=temperature is not null& time=189331200000, null&binOrder=&geometry=&inSR=&outSR=&spatialRel=esriSpatialRelIntersects&returnCentroid=true&quantizationParameters=&resultOffset=&resultRecordCount=&returnExceededLimitFeatures=false&f=pjsonJSON Response example
The following JSON response is a sample of the information returned from the request:
{
"features": [
{
"centroid": {
"x": -84.02204922365141,
"y": 35.93228062956047
},
"attributes": {
"boundary": 189345600000,
"temperature_avg": 59.82,
"results_count": 60964
}
},
{
"centroid": {
"x": -84.32106073325814,
"y": 35.930795102124708
},
"attributes": {
"boundary": 220968000000,
"temperature_avg": 59.77,
"results_count": 67719
}
},
"fields": [
{
"name": "boundary",
"type": "esriFieldTypeDate"
},
{
"name": "results _count",
"alias": "results _count",
"type": "esriFieldTypeInteger"
},
{
"name": "temperature_avg",
"alias": "temperature_avg",
"type": "esriFieldTypeDouble"
}
],
"exceededTransferLimit": false,
"geometryType": "esriGeometryPoint"
]
}JSON Response syntax
{
"fields": [
{
"name": "<fieldName1>",
"type": "<fieldType1>",
"alias": "<fieldAlias1>"
},
{
"name": "<fieldName2>",
"type": "<fieldType2>",
"alias": "<fieldAlias2>"
}
],
"features": [
<feature1>,
<feature2>
]
}