Skip To Content

Query Date Bins (Feature Service/Layer)

  • URL:https://<featureLayer-url>/queryDateBins
  • Required Capability:Query
  • Version Introduced:10.9.1

Description

The queryDateBins 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 outStatistics and returnCentroid 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 binField. 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 supportsQueryDateBins must be set to true.

To use pagination with aggregated queries on hosted feature services in ArcGIS Enterprise, the supportsPaginationOnAggregatedQueries 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 queryDateBins operation allows you to set the name of the output boundary field by setting a binBoundaryAlias input property.

Request parameters

ParameterDetails
binField

(Required)

The date field used to determine which bin each feature falls into.

Syntax

binField=<dateField>

Example

binField=birthdate
bin

(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 number and unit properties.

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 timezone property will convert the bin ranges into the given time zone. Time zones are provided as IANA strings.

The offset and timezone properties are optional. If timezone is not provided, the default UTC zone is used.

Note:

The number of units in a calendar bin is always one.

Syntax


//Calendar Bin syntax
{
  "calendarBin": {
    "unit": "<minute | hour | day | week | month | quarter | year>",
    "timezone": "<IANA Timezone description>",
    "offset": {
      "number": <integer>,
      "unit": "<second | minute | hour | day>"
    }
  }
}

//Fixed Bin syntax
{
  "fixedBin": {
    "unit": "<second | minute | hour | day>",
    "number": "<integer>",
    "offset": {
      "number": <integer>,
      "unit": "<second | minute | hour | day>"
    }
  }
}

Examples


//Calendar Bin example
{
  "calendarBin": {
    "unit": "year",
    "timezone": "US/Arizona",
    "offset": {
      "number": 5,
      "unit": "hour"
    }
  }
}

//Fixed Bin example
{
  "fixedBin": {
    "number": 12,
    "unit": "hour",
    "offset": {
      "number": 5,
      "unit": "hour"
    }
  }
}
time

Defines the beginning and end of the data in binField as epoch milliseconds. This acts as both a filter on the results (in addition to any other filters you apply) and as a strict designation of the first and last points of data, even if there are no data points present at those points.

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

time=<startTime>, <endTime>

Example


//1 January 2008 00:00:00 GMT to 1 January 2009 00:00:00 GMT
time=1199145600000, 1230768000000

A null value specified for either the start or end time will represent infinity.

Example

time=null, 1230768000000
binOrder

Results can be returned in ascending or descending order. The default is ascending (ASC).

Syntax

binOrder=<ASC | DESC>
outStatistics

(Required)

The definitions for one or more field-based statistics to be calculated.

Note:

If the outStatisticsFieldName property is empty or missing, the map server assigns a field name to the returned statistic field. A valid field name can only contain alphanumeric characters and an underscore. If the outStatisticsFieldName is a reserved keyword of the underlying DBMS, the operation can fail. If this happens, try specifying an alternative outStatisticFieldName.

Syntax

outStatistics=[
  {
    "statisticType": "<count | sum | min | max | avg | stddev | var>",
    "onStatisticField": "Field1", 
    "outStatisticFieldName": "Out_Field_Name1"
  },
  {
    "statisticType": "<count | sum | min | max | avg | stddev | var>",
    "onStatisticField": "Field2",
    "outStatisticFieldName": "Out_Field_Name2"
  }  
]

Example

outStatistics=[
  {
    "statisticType": "sum",
    "onStatisticField": "gender",
    "outStatisticFieldName": "PopulationByGender"
  },
  {
    "statisticType": "avg",
    "onStatisticField": "income",
    "outStatisticFieldName": "AverageIncome"
  }
]
where

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 like operator but do not support the not like operator or field equivalency expressions such as field1 = field2. The following is a list of supported SQL-92 with spatiotemporal-based feature services:


( '<=' | '>=' | '<' | '>' | '=' | '!=' | '<>' | LIKE )
(AND | OR)
(IS | IS_NOT)
(IN | NOT_IN) ( '(' ( expr ( ',' expr )* )? ')' )
COLUMN_NAME BETWEEN LITERAL_VALUE AND LITERAL_VALUE

For information on how to format time and date information, see the Date-time queries section.

Examples


where=POP2000 > 350000

where=CITY_NAME = 'Barrington'
returnCentroid

Returns the geometry centroid associated with all the features in the bin. If true, the result includes the geometry centroid. The default is false. This parameter is only supported on point data.

Values: true | false

geometry

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


//JSON structures
geometryType=<geometryType>&geometry={ geometry}

//Envelope simple syntax
geometryType=esriGeometryEnvelope&geometry=<xmin>,<ymin>,<xmax>,<ymax>

//Point simple syntax
geometryType=esriGeometryPoint&geometry=<x>,<y>

Examples


//Envelop example
geometryType=esriGeometryEnvelope&geometry={xmin: -104, ymin: 35.6, xmax: -94.32, ymax: 41}

//Envelope example
geometryType=esriGeometryEnvelope&geometry=-104,35.6,-94.32,41

//Point example
geometryType=esriGeometryPoint&geometry=-104,35.6
inSR

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 inSR is not specified, the geometry is assumed to be in the spatial reference of the layer.

outSR

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 outSR is not specified, the geometry is returned in the spatial reference of the map.

When using outSR with the pbf output format, the pbf format will use coordinate quantization for layer queries. When an output spatial reference is not provided for a query operation, the feature service derives coordinate quantization parameters from the layer's spatial reference. If the precision in the layer's spatial reference is inadequate for the client applicant's use, it should pass in a spatial reference with suitable precision as the output spatial reference. If the layer's source spatial reference has the desired precision and it is suitable for the client's use, the client can use the source layer's spatial reference as the output spatial reference.

spatialRel

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 (esriSpatialRelIntersects).

Values: esriSpatialRelIntersects | esriSpatialRelContains | esriSpatialRelCrosses | esriSpatialRelEnvelopeIntersects | esriSpatialRelIndexIntersects | esriSpatialRelOverlaps | esriSpatialRelTouches | esriSpatialRelWithin

quantizationParameters

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 extent, mode, originPosition, and tolerance. For more information, see the Quantization parameters JSON properties section below.

Note:

This parameter only applies if the supportsCoordinateQuantization property is true.

Examples


//upperLeft origin position
{
  "mode": "view",
  "originPosition": "upperLeft",
  "tolerance": 1.0583354500042335,
  "extent": {
    "type": "extent",
    "xmin": -18341377.47954369,
    "ymin": 2979920.6113554947,
    "xmax": -7546517.393554582,
    "ymax": 11203512.89298139,
    "spatialReference": {
      "wkid": 102100,
      "latestWkid": 3857
    }
  }
}

//lowerLeft origin position
{
  "mode": "view",
  "originPosition": "lowerLeft",
  "tolerance": 1.0583354500042335,
  "extent": {
    "type": "extent",
    "xmin": -18341377.47954369,
    "ymin": 2979920.6113554947,
    "xmax": -7546517.393554582,
    "ymax": 11203512.89298139,
    "spatialReference": {
      "wkid": 102100,
      "latestWkid": 3857
    }
  }
}
resultOffset

This parameter fetches query results by skipping the specified number of records and starting from the next record (that is, resultOffset + 1). The default is 0. You can also set this option to fetch records that are beyond maxRecordCount.

Note:

This parameter only applies if supportsPagination is true.

Example

resultOffset=100
resultRecordCount

This parameter fetches query results up to the resultRecordCount value specified. When resultOffset is specified, but this parameter is not, the map service defaults it to maxRecordCount. The maximum value for this parameter is the value of the layer's maxRecordCount property. The minimum value entered for this parameter cannot be below 1.

Note:

This parameter only applies if supportsPagination is true.

Example

resultRecordCount=100
returnExceededLimitedFeatures

When set to true, features are returned even when the results include "exceededTransferLimit": true. This allows a client to find the resolution in which the transfer limit is no longer exceeded without making multiple calls. The default value is false.

Values: true | false

binBoundaryAlias

Introduced at ArcGIS Enterprise 11.1. This parameter changes the default name of the boundary property returned in the response to a user-specified name. The boundary property indicates the value at the edge of each bin that is returned in a response. Unless changed using the binBoundaryAlias parameter, this field will be named boundary by default.

Example

binBoundaryAlias=temperatureBinValue
f

The response format. The default format is html.

Values: html | json | pjson | pbf

Date-time queries

Time zone properties

The dateFieldsTimeReference 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_DATE and CURRENT_TIMESTAMP values. Both can be used with + or - of INTERVAL values.

Note:

For both relational and spatiotemporal hosted feature services, CURRENT_TIMESTAMP will always pass and return the date and time in UTC. Values are also always stored in UTC.

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 SECOND
Note:

There are some additional considerations. Except for the second (.FFF) value, all values must be integers. If a date field is specified in the outFields list, the date-time will always be returned in formatted UTC.

To 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 SECOND

Quantization parameters JSON properties

The following table outlines the JSON properties for the quantizationParameters JSON object:

PropertyDescription
extent

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.

mode

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.

Note:

The edit value can only be used when the supportsQuantizationEditMode layer property is true.

Value: view | edit

originPosition

Integer coordinates will be returned relative to the origin position defined by this property value. The default value is upperLeft.

Values: upperLeft | lowerLeft

tolerance

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 tolerance are defined by outSpatialReference. If the outSpatialReference is not specified, tolerance is assumed to be in the unit of the spatial reference of the layer. If the tolerance is not specified, the maxAllowableOffset is used.

Note:

If the tolerance and maxAllowableOffset are not specified, a default 10,000 by 10,000 grid is used.

If mode is set to edit, the tolerance is always set to the full-resolution tolerance of the spatial reference regardless of what is passed in or set for the maxAllowableOffset. If mode is set to view and the tolerance and maxAllowableOffset are not specified, a default 10,000 by 10,000 grid is used.

Example usage

The following examples demonstrate two ways to modify the features returned by the queryDateBins 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/Log_Angeles 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/Log_Angeles 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 dateSold field as the bin field:

binField=dateSold

You'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, 1612195199999

Note 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=pjson

JSON 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=true

Example 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=pjson

JSON 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>
  ]
}