Query language syntax

Introduction

This article describes the syntax of the query language. The query language is used to create filters for your data.

It is recommended to first read the Filtering data introduction article to get a feeling for the type of filters you can construct. Afterward, you can use this article as a syntax reference guide for when you’re writing your own filters.

How to read this document

All the filters and functions of the query language are documented in the following format:

FUNCTION_NAME(arg1,arg2)

With the following conventions for the arguments:

arg: a required argument
[args]: an argument that should have 1 or more values. Specifying multiple values is done by using a comma-separated list
arg?: an optional argument. To include this, you should use arg=value.
[args]?: an optional argument that should have 1 or more values. For each value, you should use arg=value.

Each of the arguments has a specific type (e.g. a string, a number), which is documented together with the function.

General structure of the filter pipeline

When specifying a filter pipeline, you can specify in order:

Asset filters (optional, unlimited number)
An asset route filter (optional, one at most)
Asset filters (optional, unlimited number)
Record filters (optional, unlimited number)

The order of those is fixed. Asset filters should come first, followed by asset route filters, more asset filters and record filters.

The output of each part of the filter pipeline is used as input for the next part.

The different filters are separated by the pipe symbol | .

For example the following filter pipeline combines an asset filter, an asset route filter and a record filter.

ASSET(EQ(START_PROP("type"), "car")) |
ASSET_ROUTE(
  INSIDE(LOC(), AREA(name="Madrid")),
  ANY(mandatory=false),
  INSIDE(LOC(), AREA(name="Barcelona"))
) |
AND(
  EQ(PROP("car-brand"), "BMW"),
  BETWEEN(PROP("passenger_count"), 1,2)
)

Different types of arguments

The following types are supported

Strings
Numbers
Booleans
Ids
Areas
Coordinates

Constants

You can create constant string, number and boolean values. These values don’t depend on the record.

For example, you could use this to create a filter that only retains the records with a speed property between 20 and 30 km/h, where 20 and 30 would be the constants.

Strings: string constants are quoted. For example "Madrid".
Numbers: number constants are just numbers. No quotes needed. For example 42 or 3.14
Booleans: either true or false

NO_DATA constant function

You can also create a constant which represents the no data value

NO_DATA()

For example if you want to only shows the ships that have no value for the name property, you would use

EQ(PROP("name"), NO_DATA())

Areas

Projects have a number of areas associated with it:

Areas drawn on the map by the users
Areas from Area of Interest layers
Areas from time series or static GeoJSON datasets

Each of these areas have:

A user-provided name
An id
A source (e.g. the Area of interest layer) which is identified by an id or a name

Constant area

You can refer to a specific, constant area using the AREA function.

This function creates an area type.

AREA(id?,name?,dataset_id?, dataset_name?, area_of_interest_id?, area_of_interest_name?)

id: (string) the id of the area. This property cannot be combined with the name property.
name: (string) the name of the area. When multiple areas match this name, each of those areas will be included. Use the id property if you don’t want this behavior.

This property cannot be combined with the id property.
dataset_id: (string) when specified, only the areas from that specific dataset will be considered. This property cannot be combined with the dataset_name property.
dataset_name: (string) when specified, only the areas from that dataset will be considered. When multiple datasets in the project have the same name, each of those datasets will be included. Use the dataset_id property if you don’t want this behavior.

This property cannot be combined with the dataset_id property.
area_of_interest_name: (string) when specified, only the areas from that specific area of interest will be considered. This property cannot be combined with the area_of_interest_id property.
area_of_interest_id: (string) when specified, only the areas from that area of interest will be considered. When multiple areas of interest in the project have the same name, each of those areas of interest will be included. Use the area_of_interest_id property if you don’t want this behavior.

This property cannot be combined with the area_of_interest_name property

When only the id or name property is specified, all the datasets and areas of interest contained in the project will be considered.

You should at least specify either the name or id property when using the AREA function

Origin area (origin-destination only)

When doing origin-destination calculations, you use the ORIGIN_AREA to refer to the area which is used as origin in the ongoing calculation.

This function creates an area type.

ORIGIN_AREA()

Destination area (origin-destination only)

When doing origin-destination calculations, you use the DESTINATION_AREA to refer to the area which is used as destination in the ongoing calculation.

This function creates an area type.

DESTINATION_AREA()

Dwell area (dwell time only)

When doing dwell time calculations, you use the DWELL_AREA to refer to the area for which the ongoing dwell time calculation is happening.

This function creates an area type.

DWELL_AREA()

Record data retrieval functions

The functions in this section are all used to extract data from a record.

PROP: Value of a numeric/string property

You use the PROP function to specify the property value of a record.

If the property to which you refer is a numeric property, the PROP function creates a number type, and hence it can be used in other functions where a number is expected.

Similarly, if the property to which you refer is a string or enum property, the PROP function creates a string type, usable in other functions where a string is expected.

PROP(propertyIdentifier)

propertyIdentifier: (string or id) identifies the property. It should be:
- The name of the property (string type)
- The id of the property (id type)

Currently, the property names are unique in a dataset, and cannot be changed. Hence, it is possible to refer to a specific property by using the name.

However, if in future versions of the platform we would add the option to e.g. rename properties, filters using a property name would break if you rename that specific property.

You can avoid this by specifying the property id through the id function. The property id would remain stable, even if the property is renamed.

Using the name has its own benefits. It is easier to type, more readable, and it allows to re-use the same filter on another dataset with the same property names.

TIME: The time of the record

The TIME function creates a number, matching the timestamp of the record, expressed in seconds since the epoch.

This function creates a number.

TIME()

For example, to only retain the records in a specific time range you would use the following filter:

BETWEEN(
  TIME(),
  1686828000,
  1686829000
)

LOC: The location of the record

The LOC function extracts the location coordinates from a record.

This function creates a coordinate.

LOC()

For example, to only retain the records inside the Brussels area, you would use the following filter:

INSIDE(
  LOC(),
  AREA(name="Brussels")
)

START: checks if the first record

The START function returns true if the record is the first record, and false for all other records.

The first record is:

The first of the asset when the function is used in a stage of the filter pipeline before any asset route filter stages took place.
The first of the part of the asset retained by the asset route filter stage when used in a stage after the asset route filter stage.

This function creates a boolean.

START()

END: checks if the last record

The END function returns true if the record is the last record, and false for all other records.

The last record is:

The last of the asset when the function is used in a stage of the filter pipeline before any asset route filter stages took place.
The last of the part of the asset retained by the asset route filter stage when used in a stage after the asset route filter stage.

This function creates a boolean.

END()

Asset data retrieval functions

The functions in this section are all used to extract data from the whole collection of records of an asset.

In this section, first and last record refer to:

The first and last record of the asset when the function is used in a stage of the filter pipeline before any asset route filter stages took place.
The first and last record of the part of the asset retained by the asset route filter stage when used in a stage after the asset route filter stage.

DURATION: the duration of the asset

Calculates the difference between the timestamp of the last record and the timestamp of the first record, and returns this difference in seconds.

This function returns a number.

DURATION()

For example, to only retain the assets that contain records over a time span of at most one hour, use:

LTE(
  DURATION(),
  3600
)

START_TIME: the time of the first record of the asset

Returns the time of the first known record of the asset, expressed in seconds since the epoch.

This function returns a number.

START_TIME()

For example, if you only want to show the start positions of all assets, you could use a filter:

EQ(
  TIME(),
  START_TIME()
)

END_TIME: the time of the last record of the asset

Returns the time of the last known record of the asset, expressed in seconds since the epoch.

This function returns a number.

END_TIME()

For example, if you only want to show the end positions of all assets, you could use a filter:

EQ(
  TIME(),
  END_TIME()
)

START_LOC: the location of the first record of the asset

Returns the location of the first known record of the asset.

This function returns a coordinate.

START_LOC()

For example, if you only want to show the assets leaving from area "parking", you could use a filter:

INSIDE(
  START_LOC(),
  AREA(name="parking")
)

START_LOC() can only be used in an ASSET filter and only if the ASSET filter comes before the ASSET_ROUTE filter

END_LOC: the location of the last record of the asset

Returns the location of the last known record of the asset.

This function returns a coordinate.

END_LOC()

For example, if you only want to select the assets having their last record in the area "Port of Antwerp", you could use the filter:

INSIDE(
  END_LOC(),
  AREA(name="Port of Antwerp")
)

END_LOC() can only be used in an ASSET filter and only if the ASSET filter comes before the ASSET_ROUTE filter

START_PROP: a property of the first record of the asset

You use the START_PROP function to retrieve the property value of the first record of an asset.

If the property to which you refer is a numeric property, the START_PROP function creates a number type, and hence it can be used in other functions where a number is expected.

Similarly, if the property to which you refer is a string or enum property, the START_PROP function creates a string type, usable in other functions where a string is expected.

START_PROP(propertyIdentifier)

propertyIdentifier: (string or id) identifies the property. It should be:
- The name of the property (string type)
- The id of the property (id type)

Currently, the property names are unique in a dataset, and cannot be changed. Hence, it is possible to refer to a specific property by using the name.

However, if in future versions of the platform we would add the option to e.g. rename properties, filters using a property name would break if you rename that specific property.

You can avoid this by specifying the property id through the id function. The property id would remain stable, even if the property is renamed.

Using the name has its own benefits. It is easier to type, more readable, and it allows to re-use the same filter on another dataset with the same property names.

END_PROP: a property of the last record of the asset

You use the END_PROP function to retrieve the property value of the last record of an asset.

If the property to which you refer is a numeric property, the END_PROP function creates a number type, and hence it can be used in other functions where a number is expected.

Similarly, if the property to which you refer is a string or enum property, the END_PROP function creates a string type, usable in other functions where a string is expected.

END_PROP(propertyIdentifier)

propertyIdentifier: (string or id) identifies the property. It should be:
- The name of the property (string type)
- The id of the property (id type)

Currently, the property names are unique in a dataset, and cannot be changed. Hence, it is possible to refer to a specific property by using the name.

However, if in future versions of the platform we would add the option to e.g. rename properties, filters using a property name would break if you rename that specific property.

You can avoid this by specifying the property id through the id function. The property id would remain stable, even if the property is renamed.

Using the name has its own benefits. It is easier to type, more readable, and it allows to re-use the same filter on another dataset with the same property names.

Type conversion functions

The functions in this section are all used to convert data from 1 type to another type.

ID: specify an identifier

The ID function converts a string into an id.

This function creates an id type.

ID(identifier)

identifier: (string) the id or identifier

HOUR: calculate the hour of the day

The HOUR function converts a timestamp (number) into the hour of the day (number).

This functions creates a number type

HOUR(timestamp, timezone?, use_configured_timezone?)

timestamp: (number) The absolute time, expressed in seconds since the epoch
timezone: (string) the name of the timezone in which the times are specified. You find the list of available time zones here

When the filter doesn’t specify any timezone information, the UTC timezone is used.

This property cannot be combined with the use_configured_timezone property.
use_configured_timezone: (boolean) when true, the timezone will be derived from what is configured in the UI or the public REST API. This property cannot be combined with the timezone property.
- Visual analytics page: the placeholder will be replaced with the timezone configured in the UI
- Origin-destination analytics page: same as visual analytics page
- Dwell time analytics page: same as visual analytics page
- Trend analytics page: for each area (global and local areas), the placeholder will be replaced with the timezone configured in the UI for that specific area
- Distribution analytics page: same as trend analytics page
- Public REST API: consult the REST API documentation

The resulting number goes from 0 (from midnight to 00:59) until 23 (from 23:00 until midnight).

For example, to only retain the records that happened between 6:00 and 8:59 in the morning in the "Europe/Brussels" timezone, you could use the following filter:

BETWEEN(
  HOUR(TIME(), timezone="Europe/Brussels"),
  6,
  8
)

WEEKDAY: calculate the day of the week

The WEEKDAY function converts a timestamp (number) into the day of the week (number).

This functions creates a number type

WEEKDAY(timestamp, timezone?, use_configured_timezone?)

timestamp: (number) The absolute time, expressed in seconds since the epoch
timezone: (string) same as for the HOUR function
use_configured_timezone: (boolean) same as for the HOUR function

The resulting number goes from 1 (=Monday) to 7 (=Sunday).

For example, to only retain the records that happened on Monday, Tuesday or Wednesday in the UTC timezone, you could use the following filter:

BETWEEN(
  WEEKDAY(TIME()),
  1,
  3
)

MONTH: calculate the month of the year

The MONTH function converts a timestamp (number) into the month of the year (number).

This functions creates a number type

MONTH(timestamp, timezone?, use_configured_timezone?)

timestamp: (number) The absolute time, expressed in seconds since the epoch
timezone: (string) same as for the HOUR function
use_configured_timezone: (boolean) same as for the HOUR function

The resulting number goes from 1 (=January) to 12 (=December).

For example, to only retain the records that happened in January or February in the UTC timezone, you could use the following filter:

BETWEEN(
  MONTH(TIME()),
  1,
  2
)

Filter functions

The functions in this section can be used to construct filters.

They all create boolean types.

Limitation

The functions in this section that work with multiple values all have the same limitation.

It is not supported to have a value based on a property, and another value based on the time or location of the record.

You can only compare property based values with constants or other property based values.

Same for times and locations. You can only compare those with constants or other times and locations.

GT: greater than

Checks whether a provided number is strict greater than another number.

This function returns a boolean.

GT(value1, value2)

value1: (number) the value to check
value2: (number) the value to check against.

As an example: to show the records that have a speed over 50, use the following filter:

GT(PROP("speed"), 50)

You can also use it to compare 2 properties. For example to show the records where the speed is over the speed limit:

GT(PROP("speed"),PROP("speed_limit"))

LT: less than

Checks whether a provided number is strict less than another number.

This function returns a boolean.

LT(value1, value2)

value1: (number) the value to check
value2: (number) the value to check against

As an example: to show the records that have a speed under 50, use the following filter:

LT(PROP("speed"), 50)

You can also use it to compare 2 properties. For example to show the records where the speed is under the speed limit:

LT(PROP("speed"),PROP("speed_limit"))

GTE: greater than or equal

Checks whether a provided number is greater than or equal to another number.

This function returns a boolean.

GTE(value1, value2)

value1: (number) the value to check
value2: (number) the value to check against

As an example: to show the records that have a speed equal to or over 50, use the following filter:

GTE(PROP("speed"), 50)

You can also use it to compare 2 properties. For example to show the records where the speed is equal to or over the speed limit:

GTE(PROP("speed"),PROP("speed_limit"))

LTE: less than or equal

Checks whether a provided number is less than or equal to another number.

This function returns a boolean.

LTE(value1, value2)

value1: (number) the value to check
value2: (number) the value to check against

As an example: to show the records that have a speed equal to or under 50, use the following filter:

LTE(PROP("speed"), 50)

You can also use it to compare 2 properties. For example to show the records where the speed is equal to or under the speed limit:

LTE(PROP("speed"),PROP("speed_limit"))

BETWEEN: numeric value between 2 numbers

Checks whether a provided number is between two other numbers (inclusive).

This function returns a boolean.

BETWEEN(value, lowerBound, upperBound)

value: (number) the value to check
lowerBound: (number) the lower bounds of the interval that is accepted by the filter
upperBound: (number) the upper bounds of the interval that is accepted by the filter

As an example: to show the records that have a speed between 0 and 50, use the following filter:

BETWEEN(
  PROP("speed"),
  0,
  50
)

You can also use it to compare multiple properties. For example to show the records where the speed is between 0 and the speed limit:

BETWEEN(
  PROP("speed"),
  0,
  PROP("speed_limit")
)

EQ: equal values

Checks whether a value is equal to any of the other values in the collection.

This function returns a boolean.

EQ(value1, [collection_values])

value1: (string or number or boolean) the first value
collection_values: (string or number or boolean) the collection of values

Limitation

The types of value and collection_values should match.

For example, to select only the records that have a value motorway for the property road_type, use the following filter:

EQ(
  PROP("road_type"),
  "motorway"
)

You can also to check against multiple values. For example, to select only the records that have a value BMW or Audi or Mercedes for the property car_brand, use the following filter:

EQ(
  PROP("car_brand"),
  "BMW", "Audi", "Mercedes"
)

You can also use it to compare 2 properties. For example to show the records where the speed is equal to the speed limit:

EQ(
  PROP("speed"),
  PROP("speed_limit")
)

INSIDE: spatial check

This functions returns true when the location is contained inside the specified area.

This function returns a boolean.

INSIDE(value,area)

value: (coordinates): the coordinates to check
area: (area): the area to check against

For example, to only retain the records inside the Brussels area, you would use the following filter:

INSIDE(
  LOC(),
  AREA(name="Brussels")
)

AND combinator

This filter function will only return true when all input booleans are also true.

This function returns a boolean.

AND([arguments])

arguments: (boolean) a list of booleans which will be AND combined.

Typically, these arguments are other functions.

For example, if you want to see all records where cars of brand BMW with a passenger count of 2 are riding at a speed between 20 and 50, you would use

AND(
  EQ(PROP("brand"), "BMW"),
  BETWEEN(PROP("speed"), 20, 50)
)

OR combinator

This filter function will return true as soon as one of the input booleans is also true.

This function returns a boolean.

AND([arguments])

arguments: (boolean) a list of booleans which will be OR combined.

Typically, these arguments are other functions.

For example, if you want to see all BMW and Audi cars, you could use

OR(
  EQ(PROP("brand"), "BMW"),
  EQ(PROP("brand"), "Audi")
)

NOT combinator

This filter function will return true when the boolean argument is false, and vice versa.

This function returns a boolean.

NOT(argument)

argument: (boolean) the boolean argument to negate

Typically, this argument is another function

For example, if you wish to see all cars except BMWs:

NOT(
  EQ(PROP("brand"), "BMW")
)

Asset filters

An asset filter is evaluated against the asset and his properties, and not against each individual record. It includes or excludes the whole asset.

ASSET(filter)

filter: (boolean) true to include the asset, false to exclude.

Limitation

As filtering happens on the asset level, the only data retrieval functions you can use in the filter are asset data retrieval functions. Record data retrieval functions are not supported in asset filters.

Asset route filters

An asset route filter selects the assets that traveled along a route while their state matches certain conditions. The filter will only retain the continuous set of records of the asset that make up that route.

ASSET_ROUTE(leg)

leg: the (composite) leg of the route

There are 4 types of legs. 2 simple types:

A filter leg: defines an area that the asset should enter and optionally the state the asset should have while in that area
An any leg: a placeholder that matches anything

and 2 composite types:

A linear leg: a linear combination of other legs.
A switch leg: an or combination of other legs to express that the leg that the asset follows should be one of the options in the switch

Figure 1. The different types of legs

For example, if the ASSET_ROUTE uses the following leg

Figure 2. The composite leg: a linear leg consisting of an any leg, a filter leg, a switch leg and another filter leg

it could extract the following parts from 2 assets:

Figure 3. The matching parts of the assets

Note how it is sufficient to match the switch when it matches one of the legs in the switch.

See the dedicated article for more asset route examples.

Filter leg

A filter leg defines a location, and optionally properties that the state of the asset should have while in the specified location.

FILTER_LEG(filter_function,mandatory?)

filter_function: (boolean) a filter which specifies the area, and optional the state of the asset in that area
mandatory: (boolean) Indicates whether this leg is required to be present in the route or not.

When not specified, the leg is considered mandatory

Limitations

The filter function should limit the geographical area. There are 3 options for this

One (or more OR or AND combined) INSIDE filters.

In this case, you can boolean combine the INSIDE filter with filters.

Those filters must include at least one record data retrieval function.
One (or more OR or AND combined) NOT(INSIDE) filters.

In this case, you cannot combine it with any data retrieval functions, except the START and END data retrieval functions.
A filter that consists of only the START or END data retrieval function

Any leg

The any leg indicates that you don’t care about the location nor the state of the asset at that point.

For example if you want to select all assets that travel from A to B but don’t care what they do between A and B, you would linearly combine the following legs:

A filter leg expressing a start in A
An any leg
A filter leg expressing an end in B

ANY_LEG(mandatory?)

mandatory: (boolean) Indicates whether this leg is required to be present in the route or not.

When not specified, the leg is considered mandatory

Linear leg

The linear leg combines other legs in a linear fashion.

LINEAR_LEG([legs], mandatory?)

legs: the list of legs.
mandatory: (boolean) Indicates whether this leg is required to be present in the route or not.

When not specified, the leg is considered mandatory

Switch leg

A switch leg allows to express that the asset should follow one of the specified legs.

SWITCH_LEG([legs], mandatory?)

legs: a list of legs from which the asset can choose
mandatory: (boolean) Indicates whether this leg is required to be present in the route or not.

When not specified, the leg is considered mandatory

Shorthand notations

As most of the legs used in an ASSET_ROUTE will be a linear leg, you can leave out the LINEAR_LEG keyword.

E.g. the following ASSET_ROUTE:

ASSET_ROUTE(
  ANY_LEG(),
  FILTER_LEG(INSIDE(LOC(), AREA(name="Brussels")))
)

is equivalent to

ASSET_ROUTE(
  LINEAR_LEG(
    ANY_LEG(),
    FILTER_LEG(INSIDE(LOC(), AREA(name="Brussels")))
  )
)

Of course, if you want to change the value of the mandatory optional argument of the LINEAR_LEG leg, you need to use the LINEAR_LEG keyword as well. The shorthand notation only works when you only want to specify the legs.

Similarly, in all the places where you want to use a filter FILTER_LEG and don’t need to specify the mandatory optional argument, you can leave out the FILTER_LEG part. This allows to further simplify the example above to

ASSET_ROUTE(
  ANY_LEG(),
  INSIDE(LOC(), AREA(name="Brussels"))
)

Since the ANY_LEG is used often, it is allowed to shorten it to ANY. The example above then becomes:

ASSET_ROUTE(
  ANY(),
  INSIDE(LOC(), AREA(name="Brussels"))
)

Record filters

A record filter in the filter pipeline is just a boolean function as described above.

Limitation

Each record filter must include at least one record data retrieval function.