Socrata was acquired by Tyler Technologies in 2018 and is now the Data and Insights division of Tyler. The platform is still powered by the same software formerly known as Socrata but you will see references to Data & Insights going forward.

Data Transform Listing

These are the transformation functions available in the Dataset Management API. These functions can be used to transform and validate your data before you publish your dataset for consumption.

These functions can be used in the “Data Transforms” editor of the the Dataset Management Experience interface. Check out some of the examples on our Support Portal here!

See the Dataset Management API docs for more info on how to use the transform functions as an API user.

Function Name Description
+ Keep a number’s sign
and Logical and of two boolean values
|| concatenate two strings
/ Divide a number by another
= Return true if the left side equals the right
== Return true if the left side equals the right
^ No documentation is available.
> Return true if the value on the left is greater than the value on the right
>= Return true if the value on the left is greater than or equal to the value on the right
< Return true if the value on the left is less than the value on the right
<= Return true if the value on the left is less than or equal to the value on the right
% Find the remainder(modulus) of one number divided by another
* Multiply two numbers together
not Invert a boolean
<> Return true if the left side does not equal the right
!= Return true if the left side does not equal the right
or Logical or of two boolean values
- Subtract a number from another
abs Produce the absolute value of a number
between Return true if the left is within the range of the right values
case Evaluate a series of true/false expressions (predicates) and return the next consequent.
centroid returns the geometric centroid of a polygon or multipolygon. Please refer to
coalesce Take the leftmost non-null value.
contains tell whether or not a string contains another string
county_boundary Returns the boundary of the US county as a multipolygon. The state name is not case sensitive.
date_extract_d Extract the day from the date as an integer
date_extract_dow Extracts the day of the week as an integer between 0 and 6 where
date_extract_hh Extract the hour the date as an integer
date_extract_m Extract the month as an integer
date_extract_mm Extract the minute from the date as an integer
date_extract_ss Extract the second from the date as an integer
date_extract_woy Extracts the week of the year as an integer between 0 and 51
date_extract_y Extract the year as an integer
date_trunc_y Truncates a calendar date at the year threshold
date_trunc_ym Truncates a calendar date at the year/month threshold
date_trunc_ymd Truncates a calendar date at the year/month/day threshold
datetime_add_d Adds or subtracts the specified number of days to the timestamp
datetime_add_hh Adds or subtracts the specified number of hours to the timestamp
datetime_add_mm Adds or subtracts the specified number of minutes to the timestamp
datetime_add_ss Adds or subtracts the specified number of seconds to the timestamp
datetime_diff Calculates the difference between two dates in seconds, minutes, hours, days, business days, weeks, calendar weeks, months, or years.
domain_categories Returns the categories currently configured on the domain. Useful primarily
domain_licenses Returns the licenses currently configured on the domain. Useful primarily
email_parse Parse an email. This is best effort as most things are actually
ensure_within ensure_within is a function which takes a point and a multipolygon
error Make an error. This is useful in conjunction with a case function,
floating_timestamp_day Extract the day from a calendar date
floating_timestamp_day_of_week Extract the day of the week as an integer between 0 and 6 where Sunday is 0.
floating_timestamp_hour Extract the hour from a calendar date
floating_timestamp_minute Extract the minute from a calendar date
floating_timestamp_month Extract the month from a calendar date
floating_timestamp_second Extract the second from a calendar date
floating_timestamp_week_of_year Extract the week from a calendar date as an integer between 0 and 51.
floating_timestamp_year Extract the year from a calendar date
forgive forgive can take an optional default argument
from_polyline convert a linestring encode in Google’s polyline format with the given precision to a Line
geocode geocode is a function which takes human readable addresses
geocode_esri geocode_esri is a function which takes human readable addresses
grapheme_length the length of a piece of text in unicode grapheme clusters.
greatest return the largest value among its arguments (ignoring null)
hash Construct a hash value from a string value using either the md5 or sha256 algorithm.
haversine_distance Return the distance of the line using haversine formula
http_get Make an HTTP Get request to a URL. The response is returned. If the server
in Whether or not a value is in a set of other values
is_empty Returns whether or not the input is empty. Empty means null values,
is_not_null Whether or not a value is not null
is_null Whether or not a value is null
is_within is_within is a function which takes a point and a multipolygon
json_array_contains Test if a json array contains an item. If the JSON passed to this function is not an array,
json_pluck Pluck a value out of a JSON string. The returned value will be a SoQL Json value.
json_pluck_boolean Pluck a boolean value out of a JSON string. The returned value must be a boolean, otherwise
json_pluck_number Pluck a number value out of a JSON string. The returned value must be a number, otherwise
json_pluck_text Pluck a text value out of a JSON string. The returned value may be a primitive like a
least return the smallest value among its arguments (ignoring null)
left_pad Pad text with the minimum number of copies of pad to reach desired_length.
length the length of a piece of text in unicode code points. This is usually, but not
like If a string is like another string.
location_address Extract the address from a location
location_city Extract the city from a location
location_point Extract the point from a location
location_state Extract the state from a location
location_to_point Turn a location value into a point
location_zip Extract the zip from a location
lower lowercase a string
make_location This function has been deprecated. Please use the make_point function instead.
make_point function to make a point out of a Y (latitude) and X (longitude) coordinate.
make_url No documentation is available.
not_between Return true if the left is not within the range of the right values
not_in Whether or not a value is absent from a set of other values
not_like If a string is not like another string.
parse_address Extract a street address from a full US address.
parse_city Extract a city from a full US address.
parse_point Extract the point from a full US address with point.
parse_state Extract a state from a full US address.
parse_zip Extract a ZIP code from a full US address.
point_latitude Extract the latitude from a point
point_longitude Extract the longitude from a point
polylabel Returns a point that must exist within the polygon borders. It uses the recursive grid-based algorithm described here: https://github.com/mapbox/polylabel#how-the-algorithm-works. When given a multipolygon, the point it returns is within the largest (by area) sub-polygon.
random_number_between Returns a random float using a uniform distribution between the lower and upper values supplied: random_number_between(lower, upper)
random_number_normal Returns a random float using a normal distribution with the mean and variance supplied: random_number_normal(mean, variance)
regex_capture function to capture a piece of text based on a regular expression
regex_named_capture capture a piece of text based on a regular expression
regex_replace function to replace a piece of text based on a regular expression
region_code Turn a point into the ID of a region, based on which region the point falls within. For example, if this dataset can produce
region_code_label Identical to region_code, but returns a text value.
repair_geometry Attempt to repair the geometry.
replace replace text with another piece of text
replace_first replace the first occurrence of a piece of text with another piece of text
reproject reproject a geometry from one projection to another.
reproject_to_wgs84 function to reproject a geometry to WGS84. This will allow the geometry
right_pad Pad text with the minimum number of copies of pad to reach desired_length.
round Round a number to a given precision. Trailing zeros are removed by default. Negative precisions round numbers to the left of the decimal.
set_projection function to explicitly set the projection value on geometries which do not have projection
simplify Returns a simplified version of the Line, Polygon, MultiLine, or MultiPolygon using
simplify_preserve_topology Returns a simplified version of the Line, Polygon, MultiLine, or MultiPolygon using
slice Get a substring of a specified length of a text from a start index
source_created_at Get the fixed timestamp that this data source was created (ie: started uploading or importing).
split_select function to split a piece of text on a token, and then select
starts_with tell whether or a not a string is prefixed with another string
state_boundary returns the boundary of the US state
title_case Make string title case with the exception of small words as defined by NYT Style Guide:
to_boolean cast a value to a true or false
to_checkbox No documentation is available.
to_fixed_timestamp Turn a text value into a datetime with a fixed timezone.
to_floating_timestamp Turn a text value into a floating datetime. “Floating” means the timezone
to_json cast a text value to json
to_line parse a WKT (text) representation of a line into a line value
to_location This function has been deprecated. Please use the to_point function instead.
to_multiline convert a line into a multiline
to_multipoint convert a point into a multipoint
to_multipolygon convert a polygon into a multipolygon
to_number cast a value to a number
to_point parse a WKT (text) representation of a point into a point value
to_polygon parse a WKT (text) representation of a polygon into a polygon value
to_text No documentation is available.
to_url No documentation is available.
trim trim characters off the start and end of a string
trim_leading trim characters off the start of a string
trim_trailing trim characters off the end of a string
upper uppercase a string
uri_parse Parse a URI.
url_decode URL Decode a value
url_description Extract the description part of a link.
url_encode URL Encode a value.
url_url Extract the url part of a link.
validate_geometry Test that the geometry is valid.
xml_pluck Pluck a value out of an XML string using XPath. The returned value will be a string.