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.


Examples of expressions in Studio.

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:

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.

Conversion Functions

Use type conversion functions to cast entire columns to a new data type.

FunctionDescription
float(string)Converts string to a float.
int(string)Converts string to an int.
str(real)Converts a number to a string.

Geohash Functions

Beta feature

Use Geohash functions to manipulate, process, and derive new Geohash spatial indices.

FunctionDescription
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.

FunctionDescription
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.

FunctionDescription
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.

FunctionDescription
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.

FunctionDescription
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.

String Functions

String functions allow users to manipulate string columns, extracting useful information or cutting unnecessary characters from string data.

FunctionDescription
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.

FunctionDescription
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.An example expression in Foursquare Studio.

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.

OperatorExampleDescription
+num_bakeries + num_coffeeshopsAddition
-num_stores - coffeeshopsSubtraction
*revenue * tax_rateMultiplication
/retired_population/total_populationDivision
%unit_sales% 2Modulus (Division Remainder)

Equality Operators

Equality operators can be used with a ternary operator to create conditional expressions.

OperatorExampleDescription
<a < b ? c : dLess than
<=a <= b ? c : dLess than or equal to
>a > b ? c : dGreater than
>=a >= b ? c : dGreater than or equal to
==a == b ? c : dEquals
!=a == b ? c : dDoes not equal
===a === b ? c : dStrict equals
===a !== b ? c : dStrict does not equal

String Operators

The + operator can also be used to concatenate strings.

OperatorExampleDescription
+first_name + " " + last_nameConcatenation

Logical Operators

OperatorExampleDescription
&&is_forested && is_steepLogical and
||is_forested ||is_steepLogical or
!is_forested ! is_steepLogical not

Bitwise Operators

OperatorExampleDescription
&column_1 & column_2AND
|column_1 | column_2OR
^column_1 ^ column_2XOR
~column_1 ~ column_2NOT
<<column_1 << 1Zero-fill left shift
>>column_1 >> 2Signed right shift
>>>column_1 >>> 3Zero-fill right shift

Operator Precedence

OperatorsExample
Grouping (Parenthesis)(a + b)
Function Callabs(-1)
Logical Not, Bitwise Not, Unary Negation!(a), a ~ b, -(a)
Multiplication, Division, Remaindera * b, a / b, a % b
Addition, Subtractiona + b, a - b
Bitwise shift left, righta << b, a >> b, a >>> b
Less than, Greater thana < b, a > b
Equality, Inequalitya == b, a != b
Bitwise And, Bitwise Or/Xora & b, a | b
Logical And, Logical Ora && 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.


Sign In