General SQL Functions

This topic covers all functions built into the Manifold query engine, called SQL functions or query functions, that do not begin with Coord, Geom, String or Tile. The Manifold query engine also supports declaring and calling functions, including script functions, functions in external files and compiled functions as .NET assemblies.  

 

 

 

Manifold has so many SQL functions they have been grouped into several topics:

 

 

 

 

 

 

 

 

The list of functions below uses the same nomenclature as the function templates in the Query Builder.  SQL functions take arguments as specified in angle < > brackets.  The data type or value type the function returns is given after the : colon.    For example, Chr(<value>) : <string> takes a numeric <value> and returns a text <string>, as noted in the description a string that is a single character.   CoordSystems() : <table> takes no arguments and returns a table.

 

Examples in the list below which use an ? expression evaluation command are intended to be run in a Command Window.   Do not add a semicolon ; character when using the ? command in a Command window.  When using the ? command we use CALL when the function returns more than just a value, for example, when it returns a table.

 

Indexes are always zero based: for example, in an object consisting of three branches the first branch is branch 0, the second is branch 1 and the third is branch 2.

 

This is a long list, so to find a function it is best when viewing this topic in a browser to do a Ctrl-F to open a Find box and to enter the function name of interest to be able to jump to that function.  The list in this topic is comprehensive but new items are often added with updates to Manifold and may appear in the product before they appear in this documentation.   See the list in the query builder tab of the Command Window for an authoritative list of operators, commands and functions.

Constants

See the SQL Constants and Literals topic for some useful constants.   For information on functions that use a <filter> argument or which produce a filter definition matrix, see the How Matrix Filters Work topic.

Functions

Abs(<value>) : <value>

Returns the absolute value

Acos(<value>) : <radians>

Returns the arc cosine (inverse cosine) of the value.

Acosh(<value>) : <radians>

Returns the Inverse hyperbolic cosine

Asin(<value>) : <radians>

Returns the arc sine (inverse sine) of the value.

Asinh(<value>) : <radians>

Returns the inverse hyperbolic sine

Atan(<value>) : <radians>

Returns the arc tangent (inverse tangent) of the value.

Atan2(<y>, <x>) : <radians>

Returns the arc tangent (inverse tangent) of the ratio of y/x.

Atanh(<value>) : <radians>

Inverse hyperbolic tangent

BinaryStringBase64(<value>) : <string>

Takes a binary value and converts it to a base64-encoded string.

BinaryStringHex(<value>) : <string>

Takes a binary value and converts it to a hex-encoded string.  

BinaryWkbGeom(<wkb>) : <geom>

Takes WKB and returns a geom.  Supports EWKB as used by PostgreSQL.

Bound(<value>, <min>, <max>, <strict>): <value>

Bounds a numeric value to the specified range. <value> is the result if it is between <min> and <max>. If it is less than <min> then <min> is returned, and if it is larger than <max> then <max> is returned. If the <strict> parameter is TRUE, the minimum value has to be lower than or equal to the maximum value, otherwise the function returns NULL. If the <strict> parameter is FALSE, the minimum value can be greater than the maximum value.

 

For example, in the table below evaluating the expression

 

Bound([Numbers], 5000, 13000, TRUE)

 

and putting the result into the Result field produces the following:

All Numbers values less than 5000 are returned as 5000, and all Numbers values above 13000 are returned as 13000.  All Numbers within the min to max range are returned unchanged.

 

Cbrt(<value>) : <value>

Cubic root

Ceil(<value>) : <value>

Return smallest integer greater than or equal to x.

CeilDecs(<value>, <decimals>) : <value>

Return smallest number to given number of decimal places that is greater than or equal to x.

Chr(<value>) : <string>

Takes an ASCII code number and returns the corresponding character.

Coalesce(<value>, ...) : <value>

Returns the first non-NULL value out of the list of values.

CollateCode(<collate>) : <collate-code>

The Collate function takes a <collate> string with collation definition options like thosed used by the COLLATE SQL command, and returns a numeric value that encodes that collation which can be used as the <collate-code> argument in functions that use collations.   If the collation definition is external, the function returns NULL.

 

? CollateCode('en-US, nocase, noaccent')

 

The above generates the number 934, which encodes the use of a US English collation insensitive to case and insensitive to accents.   Options for the <collate> string include:

 

  • <name> - A short string that specifies the collation language. Collations are identified by names such as en-US, en-GB and zh-CN.   Use neutral for the default collation language, or the name of a collation taken from a roster of over 150 supported collations, similar to those used by PostgreSQL.   Collations are identified by names such as en-US, en-GB and zh-CN.   An empty name is allowed and will specify the default collation.  The only option supported by neutral is nocase: all other options for neutral are ignored. The short form of '' for the entire default collation is accepted, but 'neutral, nocase' must spell the collation language as neutral and cannot omit it or use an empty string.
  • noaccent - Ignore diacritical marks, accents, and other similar characters, referred to as non-space characters in Unicode jargon.  The default is to use such characters when sorting.
  • nocase - Ignore case.  The default is case-sensitive sort order.
  • nokanatype - Ignore kana type for Asian languages.  The default is to utilize the kana type, so that two instances of the same character in, say, hiragana and katakana, are considered to be different when sorting.
  • nosymbols - Ignore symbols and punctuation.  The default is to utilize symbols and punctuation.
  • nowidth - Ignore character widths for Asian languages. The default is to utilize width, so that two instances of the same character which only vary in width are considered to be different when sorting.
  • wordsort - Ignore space between words, that is, sort only on the characters that make up words and not also the spaces between words. The default is that space between words matters when sorting, so that words are not treated distinctly but only the full content of the string, words and spaces both.

 

See the COLLATE topic.

CollateCodeDef(<collate-code>): <collate>)

Given a numeric code for a (non-external) collation, the CollateCodeDef function returns the collation definition string for that collation.    See the COLLATE topic.

Compare(<value>, <value>) : <value>

Return -1 if the first value is less than the second value, 1 if the first value is greater than the second and 0 if the first and second values are equal.  Can use any data type.

ComponentBounds(<component>, <forceCompute>): <rectx4>

Given a component report the bounds of the component as a rectangular extent given by a four part (x4) number given the diagonally opposite corners of the rectangle as x1,y1, x2 y2 values.  Can compute bounds for virtual components.  Bounds computed for an image are limited to visible pixels.

 

<forceCompute> - A boolean value.  If the forceCompute parameter is FALSE, the function returns bounds stored in a spatial index or the rectangle stored in the metadata for a physical image.  All select and transform operations use rectangles from virtual images that include data from all records, but retrieving the rectangle stored in the metadata is still useful, for example, for creating a copy of an image. If the forceCompute parameter is TRUE, the function computes bounds from the field values, and for a tile field it also restricts bounds to visible pixels. If the forceCompute parameter is TRUE and the tile field belongs to a web image known to be very big, the function ignores the parameter, thus protecting against inadvertent attempts to compute bounds by downloading all tiles from Bing or Google to compute bounds by examining tile values.

 

Examples of components that automatically maintain their bounds are images, and also drawings, and labels that are in a .map file based on a table with an RTREE index on the needed geometry field.   Examples of components that do not automatically maintain their bounds are drawings and labels based on queries.

ComponentCoordSystem(<component>) : <system>

Takes a component and returns its coordinate system, and forces the coordinate system for an image to use XY axis ordering.   For maps and components that can participate in maps as layers (drawing, image, labels).

 

Because an image is stored in a special way, the axes of an image's coordinate system are always interpreted as XY / XYH. Using this function  allows passing the returned coordinate system without any further modifications to the coordinate converter object.

ComponentCoordSystemAutoXY(<component>): <system>

Removed.  Instead, use ComponentCoordSystem, which now does the same thing.

ComponentCoordSystemScaleXY(<component>): <valuex2>

Removed.  Instead, use ComponentCoordSystem and then CoordSystemScaleXY.

ComponentCoordSystemScaleXYZ(<component>): <valuex3>

Removed.  Instead, use ComponentCoordSystem and then CoordSystemScaleXYZ and CoordSystemScaleXYZBounds.

ComponentFieldBounds(<component>, <field>, <forceCompute>): <rectx4>

Given a component and a geometry or tile field in that component, returns the bounds of data for the entire component in that geometry or tile field as an x4 value.

 

<forceCompute> - A boolean value.  If the forceCompute parameter is FALSE, the function returns bounds stored in a spatial index or the rectangle stored in the metadata for a physical image.  All select and transform operations use rectangles from virtual images that include data from all records, but retrieving the rectangle stored in the metadata is still useful, for example, for creating a copy of an image. If the forceCompute parameter is TRUE, the function computes bounds from the field values, and for a tile field it also restricts bounds to visible pixels. If the forceCompute parameter is TRUE and the tile field belongs to a web image known to be very big, the function ignores the parameter, thus protecting against inadvertent attempts to compute bounds by downloading all tiles from Bing or Google to compute bounds by examining tile values.

 

Example: Given a drawing called Mexico using a geometry field called Geom in the drawing's table, to report the bounds of data in the drawing use:

 

? ComponentFieldBounds([Mexico], 'Geom', True)

 

ComponentFieldCoordSystem(<component>, <field>): <system>

Given a component and a geometry or tile field in that component, returns the coordinate system for that geometry or tile field.

 

Example: Given a drawing called Mexico using a geometry field called Geom in the drawing's table, to report the coordinate system use:

 

? ComponentFieldCoordSystem([Mexico], 'Geom')

 

ComponentFieldDrawing(<component>, <field>): <drawing>

Given a component and a geometry field in that component, creates a virtual drawing for that  geometry field.

 

Example: Given a drawing called Mexico using a geometry field called Geom in the drawing's table, to create a virtual drawing use:

 

TABLE CALL ComponentFieldDrawing([Mexico], 'Geom')

 

The result table will be the table from that virtual drawing.

ComponentFieldImage(<component>, <field>): <image>

Given a component and a tile field in that component, creates a virtual image for that tile field.

 

Example: Given an image called 1475 ELEVATION Raster using a tile field called Tile in the image's table, to create a virtual image use:

 

TABLE CALL ComponentFieldImage([1475 ELEVATION Raster], 'Tile')

 

The result table will be the table from that virtual image.

ComponentName(<component>) : <value>

Takes a component and returns its name.

ComponentPath(<component>, <child>) : <value>

Takes a pair of components and returns the path (across data sources) from one component to the other.

ComponentProperty(<component>, <property>) : <value>

Takes a component and a property name, and returns the value of the specified property.

ComponentType(<component>) : <value>

Takes a component and returns its type.

Cos(<radians>) : <value>

Returns the cosine of the value.

Cosh(<radians>) : <value>

Returns the hyperbolic cosine of the value.

DataLength(<value>) : <value>

Takes a value of an arbitrary type and returns its length in bytes. String values include the closing zero (and can be either ANSI or Unicode).  The DataLength of a NULL value is also NULL.

 

? DataLength('Manifold')

 

Text literals entered into the Command window are stored as Unicode internally using UTF16, that is, two bytes per character, so the above example gives a result of float64: 18, meaning 18 bytes are used for the string: two bytes for each of the 8 characters in the word "Manifold" plus two more bytes for the closing zero.   

 

If we wanted to compute the length of the string as ANSI text using one byte per character we could cast the default Unicode of string literals into the ANSI data type of VARCHAR by writing

 

? DataLength(CAST ('Manifold' AS VARCHAR))

 

for a result of float64: 9, meaning one byte for each of the 8 characters plus one byte for the closing zero.

DateTimeAddDays(<date>, <days>): <date>

Given a datetime and a number of days, adds the number of days to the specified date to return a new date.  When the <days> argument is positive, the number of days are added to create a later date.   When the <days> argument is negative, the number of days are subtracted to create an earlier date.   

 

The <days> argument can have decimal fraction parts, to add or to subtract by part of a day.   For example, a <days> value of 10.5 will add ten days and 12 hours (half of a day) to the datetime.  

 

? DateTimeAddDays(#1/1/2020 14:25:13#, 15)

 

Returns:

 

datetime: 1/16/2020 14:25:13

 

Adding 15.5 days

 

? DateTimeAddDays(#1/1/2020 14:25:13#, 15.5)

 

Returns:

 

datetime: 1/17/2020 2:25:13

 

Adding the extra half day (12 hours) pushes the resulting datetime into the early morning of the next day.

 

? DateTimeAddDays(#1/1/2020 14:25:13#, -15)

 

Returns:

 

datetime: 12/17/2019 14:25:13

 

Using negative values for <days> subtracts days to return an earlier date, automatically crossing month and year boundaries as necessary.

DateTimeClearTime(<date>): <date>

Given a datetime, returns that datetime with the time part cleared, that is, set to 0:00:00.    See the Clear Time transform for examples.

 

? DateTimeClearTime(#5/25/2020 16:41:09#)

 

Returns:

 

datetime: 5/25/2020 0:00:00

 

DateTimeDay(<date>) : <value>

Given a datetime value returns the day of the month.  The example returns 21.

 

? DateTimeDay(#01/21/2017#)

 

DateTimeDifferenceDays(<begin>, <end>): <days>

Given two datetime values for the beginning and the end of a time span, returns the number of days between them.

 

The difference can be positive, if the beginning date is before the end date, or negative, if the beginning date is after the end date.  The difference will have a decimal fractional part if the times in the two datetimes are not the same.  

 

? DateTimeDifferenceDays(#January 1, 2020#, #November 26, 2020#)

 

Returns:

 

float64: 330

 

Add a time specification to the beginning datetime, creating a fractional difference:

 

? DateTimeDifferenceDays(#1/1/2020 0:25:13#, #11/26/2020#)

 

Returns:

 

float64: 329.98248842592875

 

Use exactly the same time in the ending datetime:

 

? DateTimeDifferenceDays(#1/1/2020 0:25:13#, #11/26/2020 0:25:13#)

 

Returns:

 

float64: 330

 

An end date after the beginning date results in a negative difference:

 

? DateTimeDifferenceDays(#12/25/2020#, #11/26/2020#)

 

Returns:

 

float64: -29

 

There are 29 days between Thanksgiving and Christmas in 2020.

DateTimeHour(<date>) : <value>

Given a datetime value returns the hour of the day, reporting the hour using 24 hour time.  

 

? DateTimeHour(#01/21/2017 12:05:15#)

? DateTimeHour(#01/21/2017 08:05:15 PM#)

? DateTimeHour(#01/21/2017 08:05:15 AM#)

? DateTimeHour(#01/21/2017 20:05:15#)

 

DateTimeMake(<year>, <month>, <day>): <date>

Given a year number, a month number, and a day number, return a datetime value.  See the Transform - Datetime: Compose transform for examples.

 

? DateTimeMake(2020, 5, 25)

 

Returns:

 

datetime: 5/25/2020 0:00:00

 

DateTimeMakeFull(<year>, <month>, <day>, <hour>, <minute>, <second>, <millisecond>): <date>

Given numeric arguments for year, month, day, hour, minute, second, and millisecond, return a datetime value.

 

? DateTimeMakeFull(2020, 5, 25, 17, 14, 33, 523)

 

Returns:

 

datetime: 5/25/2020 17:14:33

 

To get the millisecond from the constructed date, we could use DateTimeMillisecond (all on one line):

 

? DateTimeMillisecond(DateTimeMakeFull(2020, 5, 25, 17, 14, 33, 523))

 

Returns:

 

float64: 523

 

DateTimeMillisecond(<date>) : <value>

Given a datetime value returns the millisecond value for the time. The example returns 126.

 

? DateTimeMillisecond(#01/21/2017 12:01:15.126#)

 

DateTimeMinute(<date>) : <value>

Given a datetime value returns the minute of the hour value for the time. The example returns 5.

 

? DateTimeMinute(#01/21/2017 12:05:15#)

 

DateTimeMonth(<date>) : <value>

Given a datetime value returns the month of the year.  The example returns 1 for January.

 

? DateTimeMonth(#01/21/2017 12:05:15#)

 

DateTimeSecond(<date>) : <value>

Given a datetime value returns the second of the minute.   The example returns 15.

 

? DateTimeSecond(#01/21/2017 12:05:15#)

 

DateTimeWeek(<date>, <weekStart>): <value>

Given a datetime value reports the week number for the datetime, counting the week containing January 1 as week 1.   The <weekStart> argument specifies what day a week starts, with 0 being Sunday, 1 being Monday and so on.

 

The week number for the last day of the year depends on the days of the week for that date and for January 1, and on the <weekStart> argument.  

 

For example, January 1 in the year 2020 falls on a Wednesday, while December 31 in year 2020 falls on a Thursday.   Therefore, using 0 as the <weekStart> argument to choose Sunday as the starting day of the week:

 

? DateTimeWeek(#1/1/2020 0:25:13#, 0)

 

Returns:

 

float64: 1

 

as expected, since January 1 is always in week 1.

 

? DateTimeWeek(#December 31, 2020#, 0)

 

Returns:

 

float64: 53

 

but, using 4 as the <weekStart> argument to choose Thursday as the starting day of the week:

 

? DateTimeWeek(#December 31, 2020#, 4)

 

Returns:

 

float64: 54

 

DateTimeWeekDay(<date>, <weekStart>): <value>

Given a datetime value reports the day of the week number for that date, counting from the start of the week.   The <weekStart> argument specifies what day a week starts, with 0 being Sunday, 1 being Monday and so on.

 

Using 0 for <weekStart>, a date that falls on a Sunday will return a day of the week number of 1, a date that falls on Monday will return a day of the week number of 2, and so on to a date that falls on Saturday, which will return a day of the week number of 7.

 

For example, January 1 in the year 2020 falls on a Wednesday, while December 31 in year 2020 falls on a Thursday.   Therefore, using 0 as the <weekStart> argument to choose Sunday as the starting day of the week:

 

? DateTimeWeekDay(#1/1/2020 0:25:13#, 0)

 

Returns:

 

float64: 4

 

as expected, since the day of the week number for Wednesday is 4, starting from Sunday as day of the week 1.

 

? DateTimeWeekDay(#December 31, 2020#, 0)

 

Returns:

 

float64: 5

 

but, using 4 as the <weekStart> argument to choose Thursday as the starting day of the week:

 

? DateTimeWeekDay(#December 31, 2020#, 4)

 

Returns:

 

float64: 1

 

since Thursday is day of the week 1 when we specify that a week starts on Thursday.   

DateTimeYear(<date>) : <value>

Given a datetime value returns the year.   The example returns 2017.

 

? DateTimeYear(#01/21/2017 12:05:15#)

 

DateTimeYearDay(<date>): <value>

Given a datetime value reports the day number for the datetime, counting January 1 as day 1 and December 31 as day 365.

 

? DateTimeYearDay(#December 31, 2019#)

 

Returns:

 

float64: 365

 

We can use a variety of formats to express datetimes, as set forth in the SQL Constants and Literals topic:

 

? DateTimeYearDay(#26/11/2020  16:41:09#)

 

Returns:

 

float64: 331

 

Thanksgiving 2020 is the 331st day of the year.  

 

? 365 - DateTimeYearDay(#November 26, 2020#)

 

Returns:

 

float64: 34

 

On Thanksgiving, 2020, there are only 34 days to the New Year.

DateTimeYearLeap(<date>): <value>

Given a datetime value returns a boolean true or false whether the year in the datetime is a leap year.  

 

? DateTimeYearLeap(#01/21/2017 12:05:15#)

 

Returns:

 

boolean: false

 

Given the high tolerance for parsing datetime literals as set forth in the SQL Constants and Literals topic, we can quickly see if any given year is a leap year, using expressions such as:

 

? DateTimeYearLeap(#May 2020#)

 

Returns:

 

boolean: true

 

While:

 

? DateTimeYearLeap(#May 2019#)

 

Returns:

 

boolean: false

 

 

Erf(<value>) : <value>

Computes error function.

Erfc(<value>) : <value>

Computes complementary error function.

Exp(<value>) : <value>

Computes the exponential function: Return e to the power of the value.  For a value of x, returns e^x.

Exp10(<value>) : <value>

Base 10 exponent: Return 10 to the power of the value. For a value of x, returns 10^x.

Exp2(<value>) : <value>

Base 2 exponent: Return 2 to the power of the value. For a value of x, returns 2^x.

Expm1(<value>) : <value>

For a value of x, returns e^x-1.

Floor(<value>) : <value>

Returns the largest integer that is less than or equal to the value.     See the note below on the difference between Floor and Trunc functions.

FloorDecs(<value>, <decimals>) : <value>

Returns the largest number to the given number of decimal places that is less than or equal to the value.     See the note below on the difference between Floor and Trunc functions.

Fract(<value>) : <value>

Returns the decimal fractional portion of the value.   

FractDecs(<value>, <decimals>) : <value>

Returns the decimal fractional portion of the value beyond the specified number of decimal places.  

GeocodeAddress(<dataSource>, <address>) : <valuex2>

Given a geocoding data source and an address, returns the longitude/latitude coordinates as a float64x2 value.   See the Street Address Geocoding topic.

 

Example: using a Bing geocoding data source, returns the longitude,latitude coordinates for the specified street address in California.

 

? GeocodeAddress([bing], '1170 W. Branch St., Arroyo Grande, CA 93420')  

  

Example: using a Bing geocoding data source, returns the longitude,latitude coordinates for 'Chicago'.

 

? GeocodeAddress([bing], 'Chicago')

 

Running the above Command Window example requires creating a Web Server: geocodeserver data source from the Bing Geocoder and naming it bing.  We can then use [bing] in the function.

 

GeocodeAddressMatches(<dataSource>, <address>) : <table>

Given a geocoding data source and an address, returns a table of matches. Each match is a string with the format of the string depending on the geocoding server in use.   Most geocoding servers return JSON.  See the Street Address Geocoding topic.

 

? CALL GeocodeAddressMatches([bing], 'W. Branch St., Arroyo Grande, CA 93420')

 

(In the Command Window when trying the above, hover the mouse over a Results table cell to see the JSON for that cell in a tooltip.)

 

Running the above Command Window example requires creating a Web Server: geocodeserver data source from the Bing Geocoder and naming it bing.  We can then use [bing] in the function.

 

GeocodeAddressSupported(<dataSource>) : <value>

Given a geocoding data source, returns true if the data source supports GeocodeAddress and GeocodeAddressMatches functions.  See the Street Address Geocoding topic.

 

? GeocodeAddressSupported([bing])

 

Running the above Command Window example requires creating a Web Server: geocodeserver data source from the Bing Geocoder and naming it bing.  We can then use [bing] in the function.

 

GeocodeLocationMatches(<dataSource>, <valuex2>) : <table>

Reverse geocoding.  Given a geocoding data source and a longitude/latitude location as a float64x2 value, returns a table of matches around that location.  See the Street Address Geocoding topic.

 

? CALL GeocodeLocationMatches([bing], VectorMakeX2(-120.597913, 35.127259))

 

(In the Command Window when trying the above, hover the mouse over a Results table cell to see the JSON for that cell in a tooltip.)

 

Running the above Command Window example requires creating a Web Server: geocodeserver data source from the Bing Geocoder and naming it bing.  We can then use [bing] in the function.

 

GeocodeLocationSupported(<dataSource>) : <value>

Given a geocoding data source, returns true if the data source supports the GeocodeLocationMatches function.   See the Street Address Geocoding topic.

 

? GeocodeLocationSupported([bing])

 

Running the above Command Window example requires creating a Web Server: geocodeserver data source from the Bing Geocoder and naming it bing.  We can then use [bing] in the function.

 

GeocodeSearchMatches(<dataSource>, <centerx2>, <radius>, <address>, <filter>): <table>

Searches for geocoding matches for an address with a filter (eg, 'hotel') near a lat/lon location with or without radius in meters.   See the Street Address Geocoding topic.

 

GeocodeSearchRectMatches(<dataSource>, <boundsx4>, <address>, <filter>): <table>

Searches for geocoding matches for an address with a filter in a lat/lon rectangle.   See the Street Address Geocoding topic.

GeocodeSearchSupported(<dataSource>): <value>

Checks whether a geocoding data source supports GeocodeSearchMatches and GeocodeSearchRectMatches functions.     See the Street Address Geocoding topic.

 

If we create a data source that we call Bing Geocoder for the Web Server: geocodeserver choice called Bing Geocoder, we can try:

 

? GeocodeSearchSupported([Bing Geocoder])

 

Hypot(<x>, <y>) : <value>

The hypotenuse function: Given x and y returns sqrt(x2 +y2).  

J0(<value>) : <value>

A Bessel function of the first kind: given a value x returns the result of the Bessel function J0(x).

J1(<value>) : <value>

A Bessel function of the first kind: given a value x returns the result of the Bessel function J1(x).

Jn(<order>, <value>) : <value>

A Bessel function of the nth kind: given a value x returns the result of the Bessel function Jn(x) for Order n.  

Lgamma(<value>) : <value>

Given a value, returns the log-gamma function for the value.  The log-gamma function takes the natural logarithm of the gamma function for the value.

Log(<value>) : <value>

Given a value, returns the base e logarithm (also called the natural logarithm) of the value.

Log10(<value>) : <value>

Given a value, returns the base 10 logarithm (also called the common logarithm) of the value.

Log1p(<value>) : <value>

Given a value x, return Log(x+1) where Log is the base e logarithm (also called the natural logarithm) of the value.

Log2(<value>) : <value>

Given a value, returns the base 2 logarithm (also called the binary logarithm) of the value.

NullIf(<value>, <value>) : <value>

Returns NULL if the first argument is equal to the second argument, otherwise returns the first argument.  If the value of either argument is NULL, returns NULL.

 

Unlike in Oracle and some other databases, NullIf arguments are only evaluated once.   In Oracle, NullIf(x, y) is an alias for CASE WHEN x=y THEN NULL ELSE x END, an expression which might evaluate x twice.

Pow(<base>, <power>) : <value>

Given a base x and a power y returns x^y.

PragmaValue(<pragma>) : <value>

Returns the value of the specified pragma.  

 

PRAGMA ('custom'='abc');

VALUES (PragmaValue('custom'));

 

See the PRAGMA topic.

Rcbrt(<value>) : <value>

The reciprocal cube root.   Also known as the inverse cube root: returns 1 divided by the cube root of the input value.

Round(<value>) : <value>

Returns the input value rounded up or down to the nearest integer.  

RoundDecs(<value>, <decimals>) : <value>

Returns the input value rounded up or down to the specified number of decimal positions.

Rsqrt(<value>) : <value>

The reciprocal square root.   Also known as the inverse square root: returns 1 divided by the square root of the input value.

Selection(<table>, <selected>) : <table>

For tables and components based on tables, such as drawings.   Takes the name of a table and a boolean <selected> flag, and returns a table of either selected or unselected records in the table depending on the value of the flag (True for selected, False for unselected).    For drawings, the name of the drawing can be used as the name of the table, or the drawing's table name can be used as the name of the table.   The examples below show both usages.

 

? CALL Selection([Table], True)

 

Generates a results table that shows all selected records in Table.    Suppose we have a drawing named Mexico with some objects in the drawing selected.

 

? CALL Selection([Mexico], True)

 

Generates a results table that shows all selected records in that drawing's table.

 

Note: Attempts to create a selection on a selection will be ignored.

 

Expert Commentary:

 

We liked the ease with which we could not only read, but change the selected drawing objects in Release 8:

 

--SQL

UPDATE [t] SET [f]=[f]+1 WHERE [Selection (I)]

;

 

This did not work for standalone tables not bound to drawings, but for drawing tables, it worked well.

 

Here is how we do that in Manifold Release 9:

 

--SQL

UPDATE CALL Selection([t], TRUE) SET [f]=[f]+1;

 

For example, if we are working with the Products table in the nwind example database and we would like to increase the Units On Order by 10 for all selected records:

 

--SQL

UPDATE CALL Selection([Products], TRUE)

SET [Units On Order] = [Units On Order] + 10;

 

This works for all tables, even those coming from other data sources. The only requirement is that the table must have a btree or btreenull index.

 

We can also do:

 

--SQL

TABLE CALL Selection([Table], True);

 

...and edit values for the selected records in the result window.

 

The Selection function greatly simplifies code generated for the transforms.  For example, without this function the code for running Normalize Topology on a selection would have to create temporary components.   With this function, restricting a transform to a selection is a matter of substituting ... FROM [component] with ... FROM CALL Selection([component], True).

 

For an illustrated example, see the SQL Example: Using Selection Query Functions topic.

SelectionWindow(<table>,<windowName>, <layerName>, <selected>) : <table>

For queries and components based on queries, since a selection for a query or component based on a query is dependent upon the specific window.   Can also be used for tables and components based on tables.   Takes the names of a table,   window and layer, and a boolean <selected> flag, and returns a table of either selected or unselected records in the table depending on the value of the flag (True for selected, False for unselected).  The layer name should be an empty string for windows other than map windows.   

 

Note: Attempts to create a selection on a selection will be ignored.

 

In the example below the Mexico drawing is open in a drawing window called Mexico, and also participates as a layer in an open map window called Map.  First, the map window:

 

? CALL SelectionWindow([Mexico], 'Map', 'Mexico', True)

 

and second, the drawing window:

 

? CALL SelectionWindow([Mexico Table],'Mexico', '', True)

 

For an illustrated example, see the SQL Example: Using Selection Query Functions topic.

SelectionIsEmpty(<table>) : <value>

Given a table or a component based on a table, such as a drawing, returns True if it does not contain a selection.

 

? SelectionIsEmpty([Mexico Table])

 

For an illustrated example, see the SQL Example: Using Selection Query Functions topic.

SelectionIsEmptyWindow(<table>,

<windowName>, <layerName>) : <value>

For queries and components based on queries, since a selection for a query or component based on a query is dependent upon the specific window.   Can also be used for tables and components based on tables.   Takes the names of a table,  window and layer, and returns True if it does not contain a selection.

 

? SelectionIsEmptyWindow([Mexico], 'Map', 'Mexico')

 

In the above a map window called Map shows a drawing called Mexico in a layer called Mexico.

SelectionIsInverted(<table>) : <value>

Given a table or a component based on a table, such as a drawing, returns True if a selection lists selected records and False if it lists unselected records.

 

? SelectionIsInverted([Mexico Table])

 

For an illustrated example, see the SQL Example: Using Selection Query Functions topic.

SelectionIsInvertedWindow(<table>,

<windowName>, <layerName>) : <value>

For queries and components based on queries, since a selection for a query or component based on a query is dependent upon the specific window.   Can also be used for tables and components based on tables.   Takes the names of a table,  window and layer, and returns True if a selection lists selected records and False if it lists unselected records.

 

? SelectionIsInvertedWindow([Mexico], 'Map', 'Mexico')

 

In the above a map window called Map shows a drawing called Mexico in a layer called Mexico.

SelectionKeys(<table>) : <table>

Given a table or a component based on a table, such as a drawing, returns a table of key field values for selected records.

 

? CALL SelectionKeys([Mexico Table])

 

In the above if Mexico Table uses a mfd_id field as a key field, and records with mfd_id of 3 and mfd_id of 5 are selected, the results table will have one column named mfd_id with two records in it, one with a value of 3 in that column and one with a value of 5.

 

For an illustrated example, see the SQL Example: Using Selection Query Functions topic.

SelectionKeysWindow(<table>,

<windowName>, <layerName>) :  <table>

For queries and components based on queries, since a selection for a query or component based on a query is dependent upon the specific window.   Can also be used for tables and components based on tables.   Takes the names of a table,  window and layer, and returns a table of key field values for selected records.

 

? CALL SelectionKeysWindow([Mexico], 'Map', 'Mexico')

 

In the above a map window called Map shows a drawing called Mexico in a layer called Mexico.  

 

If the drawing's table uses a mfd_id field as a key field, and records with mfd_id of 3 and mfd_id of 5 are selected, the results table will have one column named mfd_id with two records in it, one with a value of 3 in that column and one with a value of 5.

Sign(<value>) : <value>

Given a value returns the sign of the value as -1, 0 or 1 for negative numbers, zero, or positive numbers.

 

? Sign(-23)

 

? Sign(0)

 

? Sign(9)

 

Sin(<radians>) : <value>

Given a value in radians return the sine of the value.

Sinh(<radians>) : <value>

Given a value in radians return the hyperbolic sine of the value.   

Sqrt(<value>) : <value>

Given a value returns the square root of the value.

SystemCpuCount() : <count>

Returns the number of CPUs in the system.   Multiple CPU cores, including hypercores, are counted as CPUs if treated as CPUs by the operating system.

 

? SystemCpuCount()

 

The function appears in virtually every query automatically created by Edit Query button in the Transform pane, in the line:

 

THREADS SystemCpuCount()

 

The above line takes whatever the SystemCpuCount function returns and sets the number of threads for the query to use to that number.   That tells Manifold to use all CPU cores for the query.  

 

SystemGpgpuCount() : <count>

Returns the number of GPGPU-capable GPUs in the system.   The function reports the number of GPU chips in the system, not the number of processing cores in each.  Use the SystemGpgpus function to look up the name of each GPU, which can then be used on the NVIDIA web site to learn details, such as the number of processing cores, of each GPU.

 

? SystemGpgpuCount()

 

SystemGpgpus() : <table>

Returns a table giving the name of each GPU in the system.  

 

? CALL SystemGpgpus()

 

TableCache(<table>, <writable>): <table>

Takes a table and a <writable> parameter and caches the table's data, allowing either reads or both reads and writes depending on the <writable> parameter. A <writable> parameter of TRUE means the result table is writable.  The result table has all indexes that were in the original table. Writes to the result table update both the original table and the cache.

 

Given the TableCache function, we can:

 

  • Write a SELECT ... WHERE x=y query in Manifold which hits the index on the database, and have that query run fast on the database.  We can then...
  • Wrap the result into a write-through cache using TableCache or TableCacheIndexGeoms.   We can then...
  • Display the result as a drawing and work with it, with any changes to the drawing being seamlessly transported through the cache into the database.

 

Example

 

For a hypothetical connection into SQL Server, we write a query:

 

TABLE CALL TableCache(

 (SELECT * FROM [sqlserver]::[dbo.geom_of_fields]

  WHERE [company_id] = '011900'),

  TRUE -- allows writing through the cache into the database

);

 

Right-click the query in the Project pane, select Create - New Drawing, select the geometry field, specify coordinate system, and click OK.  This creates a drawing from the query.

 

We can now work with the drawing. It will only have objects for the company_id we specified, with filtering for company_id being done in SQL Server, accelerated by any indexes it might have.

 

Opening the drawing will fetch all data for the specified company_id into the cache, with the spatial index built on just the fetched data:.  Therefore,  renders / pans / zooms and other operations in the map window will be fast.

 

The drawing is editable: all changes go through the cache into the database. All this is accomplished without creating any views within SQL Server.

TableCacheIndexGeoms(<table>, <writable>): <table>

The same as TableCache and also adds an RTREE index on each geometry field.

Tan(<radians>) : <value>

Given a value in radians, returns  the tangent of the value.

Tanh(<radians>) : <value>

Given a value in radians, returns  the hyperbolic tangent of the value.

Tgamma(<value>) : <value>

Given a value, returns the gamma function of the value.  

ThreadConfig(<threads>) : <config>

Given a number indicating the number of desired threads, return a JSON string encoding that number of threads as a configuration string for use in parallelized functions that take a <config> parameter to enable setting the number of desired threads.  The default use of ThreadConfig is with SystemCpuCount(), to use all available threads.

 

Specifying a number of threads generates a thread configuration JSON string for the desired number of threads:

 

? ThreadConfig(14)

 

Returns an nvarchar JSON string of

 

{ "threads": "14" }

 

Example: create a set of contours for the given heights using all available threads:

 

TABLE CALL TileContourAreasPar([german_alps], 0,

  (VALUES (500), (1000), (1500), (3500)), -- heights

  true, ThreadConfig(SystemCpuCount()));

 

Example: create a set of contours for the given heights using four threads:

 

TABLE CALL TileContourAreasPar([german_alps], 0,

  (VALUES (500), (1000), (1500), (3500)), -- heights

  true, ThreadConfig(4));

 

Trunc(<value>) : <value>

Given a value returns the truncated integer value of the input.

 

? Trunc(3.1415926536)

 

Returns 3.

 

See the note below on the difference between Floor and Trunc functions.

TruncDecs(<value>, <decimals>) : <value>

Given value and a number of decimal positions returns the truncated value, to the specified number of decimals, of the input.

 

? TruncDecs(3.1415926536, 4)

 

Returns 3.1415.

 

See the note below on the difference between Floor and Trunc functions.

TypeName(<value>) : <name>

Given a value, returns a string giving the data type of the value.

 

? TypeName(3.1415926)

 

Returns:

 

nvarchar: float64

 

UuidMakeNew() : <uuid>

Returns a UUID value

 

? UuidMakeNew()

 

Returns (always different):

 

uuid: 3681de5a-a39c-4261-aa8a-ccd98e8a15ea

 

ValueCount(<table>) : <number>

Given a table returns the number of records in the table.

ValueSequence(<begin>, <end>, <step>) : <table>

Given a beginning value, an ending value, and steps between values returns a table where each row is a value from beginning to end with the specified step between.

 

? CALL ValueSequence(0, 10, 2)

 

Returns a table:

ValueSequenceRandom(<count>, <seed>) : <table>

Given the number of values to generate and a seed value, return a table of floating-point random values between 0 and 1.   If the seed value is zero or negative, it is ignored and the random generator uses a seed value unique for the session.

 

? CALL ValueSequenceRandom(6, 3.1415)

 

Returns a table:

The random number generator used is xorshift128+.

ValueSequenceRandomInt(<count>, <limit>, <seed>) : <table>

Given the number of values to generate, an integer limit and a seed value, return a table of integer random values between 0 and (limit - 1). If the seed value is zero or negative, it is ignored and the random generator uses a seed value unique for the session.

 

? CALL ValueSequenceRandomInt(6, 100, 0)

 

Returns a table:

The random number generator used is xorshift128+.

 

We can use the ValueSequenceRandomInt function to generate pseudorandom integer numbers.   To generate a single random integer, we use 1 as the <count>, and <seed> is any convenient value that varies by record, with mfd_id often being used.

 

For example:

 

(TABLE CALL ValueSequenceRandomInt(1, 10, [mfd_id])) 

 

Returns a random integer from 0 to 9, inclusive.

 

(TABLE CALL ValueSequenceRandomInt(1, 100, [mfd_id])) 

 

Returns a random integer from 0 to 99, inclusive.

 

We can use the expression as written above within the Expression tab, when creating computed fields, in the Transform pane, or in queries.    If we need more than one random integer in the same expression, instead of using identically the same expression as above, we should vary the limit or the seed to get a different pseudorandom number.

 

For example, if we wanted to generate a list of random times within a datetime field, where each datetime value used a date of 25 May 2020 we could use:

 

DateTimeMakeFull(2020, 5, 25,

   (TABLE CALL ValueSequenceRandomInt(1, 24, [mfd_id])),

   14,

   (TABLE CALL ValueSequenceRandomInt(1, 60, [mfd_id])),

   0)

 

to generate a list of randomized hours and seconds, with all the minute values being 14, but if we used:

 

DateTimeMakeFull(2020, 5, 25,

   17,

   (TABLE CALL ValueSequenceRandomInt(1, 60, [mfd_id])),

   (TABLE CALL ValueSequenceRandomInt(1, 60, [mfd_id])),

   0)

 

That would generate the same random values for minutes and seconds, since in both cases the <limit> argument is 60 and the <seed> is the same mfd_id value.

ValueSequenceTileXY(<valuex4>, <tilesizex2>, <contained>) : <table>

Takes a rect value for the image, a tile size value, and a contained / touching boolean switch, and returns a table of XY indexes for tiles either completely within or with any part within the specified rect. The returned table includes the following fields:

 

  • X - The X coordinate of a tile. Can be negative.
  • Y - The Y coordinate of a tile. Can be negative.
  • Rect - The rect of a tile.

 

The Rect value could be computed by the user from the X and Y values and the tile size, but is provided regardless for convenience.

VectorBgrHcy(<bgr>) : <hcy>

Convert BGR to HCY:  Given a vector value in BGR color space returns a vector value in HCY color space.

VectorBgrHsi(<bgr>) : <hsi>

Convert BGR to HSI:  Given a vector value in BGR color space returns a vector value in HSI color space.

VectorBgrHsl(<bgr>) : <hsl>

Convert BGR to HSL:  Given a vector value in BGR color space returns a vector value in HSL color space.

VectorBgrHsv(<bgr>) : <hsv>

Convert BGR to HSV:  Given a vector value in BGR color space returns a vector value in HSV color space.

VectorCross(<valuex3>, <valuex3>): <valuex3>

Computes a cross product of x3 values.

VectorDot(<valuexN>, <valuexN>): <value>

Computes a dot product of x2, x3, or x4 values.

VectorHcyBgr(<hcy>) : <bgr>

Convert HCY to BGR:  Given a vector value in HCY color space returns a vector value in BGR color space.

VectorHsiBgr(<hsi>) : <bgr>

Convert HSI to BGR:  Given a vector value in HSI color space returns a vector value in BGR color space.

VectorHslBgr(<hsl>) : <bgr>

Convert HSL to BGR:  Given a vector value in HSL color space returns a vector value in BGR color space.

VectorHsvBgr(<hsv>) : <bgr>

Convert HSV to BGR:  Given a vector value in HSV color space returns a vector value in BGR color space.

VectorMakeX2(<value>, <value>) : <valuex2>

Given two numeric values return an x2 vector containing those values as component parts.

VectorMakeX3(<value>, <value>, <value>) : <valuex3>

Given three numeric values return an x3 vector containing those values as component parts.

VectorMakeX4(<value>, <value>, <value>, <value>) : <valuex4>

Given four numeric values return an x4 vector containing those values as component parts.

VectorValue(<valuexN>, <index>) : <value>

Given a vector value and an index (zero based counting) returns the value at that index position in the vector.   For example, if a vector in a field called Color is uint8x3 it is a vector with three uint8 values.   VectorValue([Color], 0)  returns the value of the first of the three uint8 values in the vector and VectorValue([Color], 2) returns the third uint8 value in the vector.

VectorValues(<valuexN>, <valuexN>) : <valuexN>

Rearranges component values of a vector.   Given a vector of values and a vector of indexes, returns a vector of values recomposed according to indexes. Both input vectors have to be of the same size. If the index vector contains a value that is not a valid index, the relevant part of the vector is replaced with 0.

 

Example:

 

VectorValues(VectorMakeX2(X, Y), VectorMakeX2(1, 0))

 

Returns an x2 vector with X and Y values from the original vector swapped.   

 

To explain why that is so:  If the original vector is a two value vector represented by the set {X,Y} and the index is a two value vector represented by the set {1,0}, the index tells the function to return as the first position in the result vector whatever is in the second position of the original vector (to mean "second" in zero-based counting we use the number 1) and to run as the second position in the result vector whatever is in the first position of the original vector (to mean "first" in zero-based counting we use the number 0).  Looking at the original set of {X,Y}, Y is in the second position and X is in the first position.   So if we do as the index set of {1,0} instructs, we will create a result set of {Y,X}.

 

Example:

 

VectorValues(VectorMakeX3(B, G, R), VectorMakeX3(2, 1, 0))

 

Creates an X3 vector result where the component values are in the order R, G and B, that is, the third, second and first parts of the original vector.

Y0(<value>) : <value>

A Bessel function of the second kind: Given a value x returns the result of the Bessel Function Y0(x).  

Y1(<value>) : <value>

A Bessel function of the second kind: Given a value x returns the result of the Bessel Function Y1(x).  

Yn(<order>, <value>) : <value>

The nth Bessel function of the second kind: Given an order n and a value x returns the result of the Bessel Function Yn(x).  

 

Notes

New functions - The list in this topic is intended to be comprehensive but might be not up to date.  New items are often added with updates to Manifold and may appear in the product before they appear in this documentation.   See the list in the query builder tab of the Command Window for an authoritative list of operators, commands and functions.

 

Z values - All functions support Z values.

 

M values - Functions creating geoms from other formats tolerate M values in formats that have them but ignore those M values, discarding them.

 

Curvilinear objects -  Functions for GML, WKB and WKT support curvilinear objects for all types supported by each format:  circular arcs, ellipsoidal arcs, splines for GML and circular arcs for WKB and WKT.

 

Division by zero - returns NULL.

 

All types of values - Operators and functions generally  support all types of values so that, for example, we can use comparison or boolean operators on tiles and not just on scalar values.  

 

Floor and Trunc functions -  The Floor and Trunc functions are similar but different.  Floor, FloorDecs, TileFloor and TileFloorDecs round down towards negative infinity while Trunc, TruncDecs, TileTrunc and TileTruncDecs round towards zero.   For positive values the functions are identical but return different results for negative values.  For example, Floor(-2.3) is -3 while Trunc(-2.3) is -2.

 

Everything Math - For a handy reference to anything in mathematics, see the Wolfram MathWorld site.   Thank you, Wolfram!

 

See Also

Tables

 

Add an Index to a Table

 

Functions

 

Queries

 

Regular Expressions

 

How Matrix Filters Work

 

Command Window

 

Command Window - Query Builder

 

SQL Constants and Literals

 

SQL Statements

 

SQL Operators

 

SQL Functions

 

Aggregate SQL Functions

 

Coord SQL Functions

 

Geom SQL Functions

 

String SQL Functions

 

Tile SQL Functions

 

Temporary Databases

 

EXECUTE

 

Example: Create and Run a Query -  See how the different parts of a command window operate when creating and running SQL queries.   Includes use of the Log tab as well as the ?expression and !fullfetch commands.

 

Example: Transfer DEM Terrain Heights to Areas in a Drawing - Given a map with an image layer that shows terrain heights taken from a DEM, and a drawing layer that contains areas, using a small SQL query we transfer the average terrain height within each area to that area as a Height attribute for the area. Easy!

 

SQL Example: Process Images with 3x3 Filters -  Shows a step-by-step example of developing an SQL query that takes a query written by the Edit Query button and then modifies that query into a general purpose query that can apply any 3x3 filter.   This makes it easy to use matrix filters we find on the web for custom image processing.   We extend the query by using parameters and adding a function, and then show how it can be adapted to use a 5x5 filter.

 

SQL Example: Process Images using Dual 3x3 Filters  - A continuation of the above topic, extending the example query to utilize two filters for processing, as commonly done with Sobel and Prewitt two filter processing.

 

SQL Example: Process RGB Images using Matrix Filters - A continuation of the above two topics, extending the example query to process three channel, RGB images.

 

SQL Example: Miscellaneous SQL Functions - A variety of small examples using SQL functions.

 

SQL Example: GeomOverlayAdjacent Function - Using the GeomOverlayAdjacent function, an example that shows how this function and similar functions such as GeomOverlayContained, GeomOverlayContaining, GeomOverlayIntersecting and GeomOverlayTouching operate.

 

SQL Example: GeomOverlayTopologyUnion Function - A continuation of the SQL Example: GeomOverlayAdjacent Function example, using the GeomOverlayTopologyUnion function, an example that shows how this function and similar functions such as GeomOverlayTopologyIdentity, GeomOverlayTopologyIntersect and GeomOverlayTopologyUpdate operate.

 

Example: Merge : areas (dissolve) - In this example we combine multiple area objects into a single area object by using the Merge template in the Transform pane, using the areas (dissolve) option.  A drawing of French regions shows some regions as more than one area.  We would like each region to be one area so the table of regions has one record per region.

 

SQL Example: Learn SQL from Edit Query - Merging Areas - We learn how to write an SQL query that does a Merge : area (dissolve) operation by cutting and pasting from what the Edit Query button automatically generates.

 

Example: Use a Transform Expression to Create Buffers in a Drawing - Use the Expression tab of the Transform pane to create three different sizes of buffers for different lines in a drawing and then automatically create a query which does the same thing.  Includes examples of using the Add Component button and also the Edit Query button.

 

Example: Clip Areas with a Transform Expression - Use the Expression tab of the Transform pane to clip areas in a drawing to fit within horizontal bounds.   Includes examples of using the Add Component button and also the Edit Query button.

 

Example: Transform Templates, Expressions and Queries - We learn to use a function by clicking on a template in the Transform pane, seeing what it does in a preview, looking at the query Manifold creates and then trying out the function in the Expression tab.

 

SQL Example: List Transformation Grids - In this example we use a few snippets of easy SQL to list NTv2 and NADCON transformation grids that are available within the grids.dat compressed collection of transformation grid files.   Grid transformation files can be used when creating custom base coordinate systems, for NADCON / HARN / HPGN and NTv2 high accuracy transformations that use grid files.