Overview
Using Studio's expression syntax, you may create columns with constant values, reference other columns, or use built-in functions for more sophisticated data analytics.
Expression Editor
After creating a new column the expression editor appears. Expressions can be simple, employing simple math operators and column names. To find more advanced functions, check out our library of built-in functions in the left sidebar.
Type or drag and drop the function into the editor. When you start typing a function, all matching functions, and their descriptions along with expected parameters will appear in the editor.
Be sure to preview the results of your column in the preview of your dataset below the editor.
The expression editor defines functions that match your search.
Expression Function Library
Studio offers an extensive library of built-in analytic functions that enable sophisticated data analytics.
This library is separated into several categories:
- Aggregation functions
- Conversion functions
- Geometric functions
- Geohash functions
- H3 functions
- Mathematical functions
- Placekey functions
- S2 functions
- String functions
- Time/Date functions
Note: The tables below show only required or otherwise common parameters for each function call. For optional arguments and an API reference, please visit the function's dedicated page via the link provided.
Aggregation Functions
Studio provides several aggregation functions, allowing you to compute characteristics of entire columns. Aggregate expression functions can best be utilized in combination with the Big Number chart, allowing you to display aggregated data to users.
| Function | Description |
|---|---|
sum(real) | Returns the sum of all values in a column. |
count(real) | Returns count of the values of the column. |
mean(real) | Returns mean value of the column. |
agg_min(real) | Returns the min value of the column. |
agg_max(real) | Returns the max value of the column. |
quantile(real, k) | Returns the indexes of given k quantiles of the column. |
Conversion Functions
Use type conversion functions to cast entire columns to a new data type.
| Function | Description |
|---|---|
float(string) | Converts string to a float. |
int(string) | Converts string to an int. |
str(real) | Converts a number to a string. |
Geohash Functions
Use Geohash functions to manipulate, process, and derive new Geohash spatial indices.
| Function | Description |
|---|---|
geohashToBounds(geohash) | Returns a geojson containing the SW/NE lat/lng bounds of specified Geohash. |
geohashToGeo(geohash) | Returns a [lat, lng] pair representing the approximate center of the Geohash cell at reasonable precision. |
geohashToNeighbor(geohash, direction) | Returns the adjacent Geohash cell in given direction (N, S, E, W). |
geohashToNeighbors(geohash) | Returns a Geohash array containing the 8 adjacent cells to specified Geohash. |
geohashIsValid(geohash) | Returns true if the given Geohash string is valid, or false if the Geohash string is invalid. |
geoToGeohash(lat, lng, precision?) geoToGeohash( [lat, lng], precision?) | Encodes a lat/lng pair to Geohash, either to specified precision or to automatically evaluated precision. |
Geometric Functions
Use geometric functions to create geometries from points, derive centroids from polygons, and generate buffers around map features.
| Function | Description |
|---|---|
buffer(lat, lng, bufferDistance, distanceUnit, pointsPerArc) | Creates a buffer surrounding points on the map. |
bufferFeature(Feature, bufferDistance``distanceUnit, pointsPerArc) | Creates a buffer surrounding features on the map. |
bufferH3(h3, bufferDistance, distanceUnit, pointsPerArc) | Creates a buffer surrounding H3 cells on the map. |
centroid(Feature) | Gets the centroid coordinates from a GeoJSON Geometry feature. |
latLngToPoint(lat, lng) | Creates a GeoJSON Point feature for each set of coordinates. |
pointLat(Feature<Point>) | Gets the latitude coordinate from a GeoJSON Point feature. |
pointLng(Feature<Point>) | Gets the longitude coordinate from a GeoJSON Point feature. |
H3 Functions
Use H3 functions to leverage the H3 API from within Studio, opening up new spatial indexing possibilities.
| Function | Description |
|---|---|
degsToRads(degs) | Converts degrees to radians. |
experimentalH3ToLocalI(h3,h3) | (Experimental) Returns the local I coordinate for a cell (anchored by an origin cell). |
experimentalH3ToLocalJ(h3,h3) | (Experimental) Returns the local J coordinate for a cell (anchored by an origin cell). |
geoToH3(lat, lng, res) | Creates H3 cells from a set of coordinates at a specified resolution. |
getDestinationH3Index FromUnidirectionalEdge(h3 edge) | Returns the destination hexagon from the unidirectional edge H3 index. |
getH3UnidirectionalEdge(h3, h3) | Returns a unidirectional edge H3 index based on the provided origin and destination indexes. |
getOriginH3Index FromUnidirectionalEdge(h3 edge) | Returns the origin hexagon from the unidirectional edge H3 index. |
h3Distance(h3, h3) | Returns the distance in grid cells between the two indexes. |
h3GetBaseCell(h3) | Returns the base cell number of the index. |
h3GetResolution(h3) | Returns the resolution of the H3 index. |
h3IndexesAreNeighbors(h3,h3) | Returns 1 if indexes are neighbors. |
h3IsPentagon(h3) | Returns 1 if index represents a pentagonal cell. |
h3IsResClassIII(h3) | Returns 1 if index has resolution with Class III orientation. |
h3IsValid(h3) | Returns 1 if index is valid. |
h3ToCenterChild(h3, childRes) | Returns the center child of H3 cell at resolution childRes. |
h3ToLat(h3) | Returns centroid latitude of an H3 cell. |
h3ToLng(h3) | Returns centroid longitude of an H3 cell. |
h3ToParent(h3, parentRes) | Returns the parent of the H3 cell. |
h3UnidirectionalEdgeIsValid(h3 edge) | Returns 1 if index is a valid unidirectional edge index. |
radsToDegs(rads) | Converts radians to degrees. |
Mathematical Functions
Use a wide range of common mathematical functions while writing expressions.
| Function | Description |
|---|---|
abs(real) | Returns the absolute value of a number. |
acos(real) | Returns the arccosine of an angle. |
acosh(real) | Returns the hyperbolic arccosine of an angle. |
asin(real) | Returns the arcsine of an angle. |
asinh(real) | Returns the hyperbolic arcsine of an angle. |
atan(real) | Returns the arctangent of an angle. |
atan2(real,real) | Returns the two-variable arctangent of an angle. |
atanh(real) | Returns the hyperbolic arctangent of an angle. |
cbrt(real) | Returns the cube root of an item. |
ceil(real) | Rounds the floating number up to the nearest integer. |
cos(real) | Returns the cosine of an angle. |
cosh(real) | Returns the hyperbolic cosine of an angle. |
exp(real) | Raises e to the power of a number (inverse of natural logarithm). |
expm1(real) | Returns e^x - 1, where x is the provided argument. |
floor(real) | Rounds the floating number down to the nearest integer. |
fround(real) | Returns the nearest 32-bit single precision float representation of the provided argument. |
hypot(real, real) | Returns the square root of the sum of squares of the provided arguments. |
imul(int,int) | Returns the result of the C-like 32-bit multiplication of the two provided arguments. |
log(real) | Returns the natural logarithm of a number. |
log10(real) | Returns the common logarithm of a number. |
log1p(real) | Returns the natural logarithm of 1 + x, where x is the provided argument. |
log2(real) | Returns the binary logarithm of a number. number |
max(real, real) | Returns the maximum value from the provided arguments. |
min(real, real) | Returns the minimum value from the provided arguments. |
pow(base,exponent) | Raises a base to the power of an exponent. |
round(real) | Rounds the floating number to the nearest integer. |
sign(real) | Extracts the sign from a real number. |
sin(real) | Returns the sine of an angle. |
sinh(real) | Returns the hyperbolic sine of an angle. |
sqrt(real) | Returns the square root of a number. |
tan(real) | Returns the tangent of an angle. |
tanh(real) | Returns the hyperbolic tangent of an angle. |
trunc(float) | Returns the integer part of a number by removing any fractional digits. |
Placekey Functions
Studio allows for operation on Placekey identifier strings.
| Function | Description |
|---|---|
geoToPlacekey(lat, lng) | Returns the Where Part of a Placekey. |
h3ToPlaceKey(h3) | Returns a Placekey containing the Where part corresponding to the given aperture 10 hexagon. |
placekeyDistance(Placekey, Placekey) | Returns the distance between two place keys (based on H3 hexagon's centroids). |
placekeyIsValid(Placekey) | Returns true if the Placekey is valid. |
placekeyToH3(Placekey) | Returns the Where Part of the place key as an H3 index. |
placekeyToLat(Placekey) | Returns the latitude of the H3 hexagon's centroid in the Where Part. |
placekeyToLng(Placekey) | Returns the longitude of the H3 hexagon's centroid in the Where Part. |
S2 Functions
A range of functions are available to process S2 indexes in Studio expressions.
| Function | Return Type | Description |
|---|---|---|
s2IsValid(s2) | bool | Returns true if the given S2 cell is valid. |
geoToS2(lat, lng, level) | s2 | Returns an S2 index at the given lat/lng coordinate at the specified resolution level. |
s2ToLat(s2) | real | Returns the latitude of the S2 point index. |
s2ToLng(s2) | real | Returns the longitude of the S2 point index. |
s2ToGeo(s2) | geojson | Returns a GeoJSON point feature for the S2 index. |
s2ToBounds(s2) | geojson | Returns a GeoJSON polygon feature for the S2 polygon. |
s2ToParent(s2, level) | s2 | Returns the parent S2 index at the given resolution level. |
String Functions
String functions allow users to manipulate string columns, extracting useful information or cutting unnecessary characters from string data.
| Function | Description |
|---|---|
charAt(string, index) | Get a new string containing a single character at the specified index. |
contains(string, searchString) | Returns true if string contains substring. |
endsWith(string, searchString) | Returns true if a substring searchString ends with string. |
indexOf(string, substring) | Returns the index of the first occurrence of substring in string. |
lastIndexOf(string, searchString) | Returns the index of the last occurrence of substring in string. |
padEnd(string, targetLength, padString) | Pads the end of string with padString until it reaches targetLength. |
padStart(string, targetLength, padString) | Pads the front of string with padString until it reaches targetLength. |
replace(string, searchString, newSubString) | Replace subString in a string with newSubString |
startsWith(string, searchString, startIndex) | Returns true if a substring searchString starts with string. |
stringLength(string) | Returns the length of the string. |
substring(string, startIndex, endIndex) | Returns the extracted substring from the index range. |
toLowerCase(string) | Converts all characters in a string to lower case. |
toUpperCase(string) | Converts all characters in a string to upper case. |
trim(string) | Remove whitespace from the front and end of a string. |
trimEnd(string) | Remove whitespace from the end of a string. |
trimStart(string) | Remove whitespace from the front of a string. |
Time/Date Functions
Use time and date functions to process timestamp data and modify time zones.
| Function | Description |
|---|---|
formatTime(time) | Formats a timestamp into a string. |
geoToTimezone(lng, lat) | Lookup the time zone at a set of coordinates. |
| getTimezone() | Gets the user's local timezone. |
parseTime(time) | Parses a string into a timestamp. |
Expression Syntax
Studio expressions make use of a basic syntax. This page describes both operators and their precedence as well as conditional expression syntax in Foursquare Studio.
Using expressions, users can:
- Reference other columns (accessing the values in the same row)
- Inject constant values
- Create a new calculated column with standard mathematical operators
- Call built-in functions from a growing library
An example expression in Foursquare Studio.
Functions
Foursquare Studio offers an extensive library of built-in functions that can be used for sophisticated data analytics. These functions can be used in conjunction with operators and within conditional expressions.
Visit the expression home page to see a complete overview.
Data Types
Columns can be of different types: numbers, strings, timestamps, and geojson polygons.
When building new columns by combining values with operators and calling functions from the expression library, the data types of the various arguments matter. While the result is usually intuitive, refer to any function's API reference to find the return type of the argument.
Operators
The below operators can be used to calculate a new column. Currently, Studio supports:
Arithmetic Operators
Use the below arithmetic expressions on columns of float or int data type.
| Operator | Example | Description |
|---|---|---|
+ | num_bakeries + num_coffeeshops | Addition |
- | num_stores - coffeeshops | Subtraction |
* | revenue * tax_rate | Multiplication |
/ | retired_population/total_population | Division |
% | unit_sales% 2 | Modulus (Division Remainder) |
Equality Operators
Equality operators can be used with a ternary operator to create conditional expressions.
| Operator | Example | Description |
|---|---|---|
< | a < b ? c : d | Less than |
<= | a <= b ? c : d | Less than or equal to |
> | a > b ? c : d | Greater than |
>= | a >= b ? c : d | Greater than or equal to |
== | a == b ? c : d | Equals |
!= | a == b ? c : d | Does not equal |
=== | a === b ? c : d | Strict equals |
=== | a !== b ? c : d | Strict does not equal |
String Operators
The + operator can also be used to concatenate strings.
| Operator | Example | Description |
|---|---|---|
+ | first_name + " " + last_name | Concatenation |
Logical Operators
| Operator | Example | Description |
|---|---|---|
&& | is_forested && is_steep | Logical and |
|| | is_forested ||is_steep | Logical or |
! | is_forested ! is_steep | Logical not |
Bitwise Operators
| Operator | Example | Description |
|---|---|---|
& | column_1 & column_2 | AND |
| | column_1 | column_2 | OR |
^ | column_1 ^ column_2 | XOR |
~ | column_1 ~ column_2 | NOT |
<< | column_1 << 1 | Zero-fill left shift |
>> | column_1 >> 2 | Signed right shift |
>>> | column_1 >>> 3 | Zero-fill right shift |
Operator Precedence
| Operators | Example |
|---|---|
| Grouping (Parenthesis) | (a + b) |
| Function Call | abs(-1) |
| Logical Not, Bitwise Not, Unary Negation | !(a), a ~ b, -(a) |
| Multiplication, Division, Remainder | a * b, a / b, a % b |
| Addition, Subtraction | a + b, a - b |
| Bitwise shift left, right | a << b, a >> b, a >>> b |
| Less than, Greater than | a < b, a > b |
| Equality, Inequality | a == b, a != b |
| Bitwise And, Bitwise Or/Xor | a & b, a | b |
| Logical And, Logical Or | a && b, a|| b |
Conditional Expressions
The ternary (i.e. three-argument) operator ? : defined simple conditional expressions. a ? b : c is conceptually equivalent to if a then b else c.
The condition (the expression before the ?) is considered "truthy" if it is a non-zero number or a non-empty string, and is considered "falsy" for 0 numerical values and empty strings.
Example: Populate the cell with northern hemisphere if the latitude is greater than 0. Otherwise, populate the cell with southern hemisphere.
latitude > 0 ? "northern hemisphere" : "southern hemisphere";
You may also use multiple ternary operators to create more complex conditional expressions.
Example: Populate the cell with 0 if the longitude is greater than 40 and less than -120, or 1 if the longitude is greater than 40 but greater than or equal to -120. Otherwise, populate the cell with 2.
longitude > 40 ? (latitude < -120 ? 0 : 1) : 2;
Note: Studio supports only pure expressions, meaning it is not possible to write multi-line programs with declarations, statements, and side-effects.
Updated 10 months ago