SQL Functions

This topic provides selected notes and discussion on operators and functions that are built into the Manifold query engine.  The Manifold query engine also supports declaring and calling functions, including script functions, functions in external files and compiled functions as .NET assemblies.  For information on declaring and calling functions, please see the Functions topic.

 

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

Some useful constants:

 

CR -  a string constant for a carriage return character.

LF - a string constant for a line feed character.

WHITESPACE - a string constant for use with StringTrimXxx functions:  contains a space character, a tab character, CR and LF.

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

Avg(<value>) : <value>

An aggregate function:  Returns the average value of the value field.  See the Aggregates topic.

BinaryStringBase64(<value>) : <string>

Takes a binary value and converts it to a base64-encoded string.  See Transform Templates - Text for Compose Base64 String

BinaryStringHex(<value>) : <string>

Takes a binary value and converts it to a hex-encoded string.    See Transform Templates - Text for Compose Hex String

BinaryWkbGeom(<wkb>) : <geom>

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

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.

Collate(<name>, <useCase>, <useAccent>, <useSymbols>) : <value>

The Collate function takes the name of a collation and True/False options to use case, accents and symbols, and returns a numeric value that encodes that collation which can be used as the <collation> argument in functions that use collations.

 

? Collate('en-US', True, True, True)

 

The above generates the number 928, which encodes the use of a US English collation sensitive to case, accents and symbols. 

 

? Collate('en-US', False, True, True)

 

The above generates the number 930, which encodes the use of a US English collation where upper or lower case does not matter but which is sensitive to accents and symbols. 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.

ComponentCoordSystem(<component>) : <value>

Takes a component and returns its coordinate system.  For maps and components that can participate in maps as layers (drawing, image, labels).

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.

CoordConvert(<converter>, <geom>) : <geom>

Converts a geom from one coordinate system to another. The function takes a coordinate converter object and a geom value, and returns a new geom value.

CoordConvertPoint(<converter>, <valuex2>) : <valuex2>

Takes a coordinate converter object and an XY coordinate and returns the converted coordinate.

CoordConvertPoint3(<converter>, <valuex3>) : <valuex3>

Takes a coordinate converter object and an XYZ coordinate and returns the converted coordinate.

CoordConvertTile(<converter>, <image>, <valuex4>) : <tile>

Converts coordinate systems of tiles for images similar to how CoordConvert  converts coordinate systems for geoms.   The first argument is a converter from the coordinate system of the original image to the coordinate system of the new image. The second argument is the original image (passing the table with the image data will not work - we must pass the image). The last argument is the rectangle of the tile to produce in the coordinate system of the new image. If the rectangle is denormalized (xmin > xmax or ymin > ymax) or empty (xmin = xmax or ymin = ymax), the function returns NULL. If the rectangle coordinates are fractional, they are rounded to full pixels:  xmin and ymin are rounded down (Floor) and ymin and ymax are rounded up (Ceil).

CoordConvertTrace(<converter>, <valuex3>) : <table>

Takes a coordinate converter object and an XYZ coordinate and reports conversion steps as well as the intermediate conversion result after each step as a table.

CoordConverterIsIdentity(<converter>) : <value>

takes a coordinate converter object and returns true if it represents an identity operation (performs no conversion).   

CoordConverterIsScaleShift(<converter>) : <value>

CoordConverterIsScaleShift returns true if the conversion is limited to scale and shift.

CoordConverterIsScaleShiftSwap(<converter>) : <value>

CoordConverterIsScaleShiftSwap returns true if the conversion is limited to scale, shift and coordinate swap (XY to YX or vice versa).

CoordConverterIsXYZ(<converter>) : <value>

CoordConverterIsXYZ returns true if either target or source coordinate system is cartesian XYZ.

CoordConverterMake(<target>, <source>) : <converter>

Takes the definitions of target and source coordinate systems (in that order) and creates a coordinate converter object. Coordinate converter objects are treated syntactically like a table so we have to write CALL CoordConverterMake(...).

CoordConverterSteps(<converter>) : <table>

Takes a coordinate converter object and reports conversion steps as a table.

CoordDatum(<manifold>) : <value>

Returns the definition of the datum with the specified Manifold ID.

CoordDatumDefault() : <value>

returns the definition of the default datum.

CoordDatumEpsg(<epsg>) : <value>

Returns the definition of the datum with the specified EPSG code.

CoordDatumParameters() : <table>

Returns a list of datum parameters.

 

? CALL CoordDatumParameters()

 

CoordDatums() : <table>

Returns a list of datums in the internal coordinate system database.

 

? CALL CoordDatums()

 

CoordSystem(<manifold>) : <value>

Returns the definition of the coordinate system with the specified Manifold ID.  We can see a list of all coordinate systems with their EPSG IDs and Manifold IDs using CoordSystems().

 

? CoordSystem(612)

 

CoordSystemDefault() : <value>

Returns the definition of the default coordinate system.

 

? CoordSystemDefault()

 

CoordSystemDefaultLatLon() : <value>

Returns the definition of the default lat/lon coordinate system.

 

? CoordSystemDefaultLatLon()

 

 

CoordSystemEpsg(<epsg>) : <value>

Returns the definition of the coordinate system with the specified EPSG code.   

 

? CoordSystemEpsg(4326)

 

CoordSystemOverride(<string>, <json>) : <value>

Takes a coordinate system string and a JSON string with the parameter overrides, and composes a new coordinate system string merging the two together.

 

ALTER Table [WaterwaysTable] (

  ADD PROPERTY 'FieldCoordSystem.Geom'

     CoordSystemOverride('EPSG:4269', '{ "Axes":"XY" }')

);

 

CoordSystemParse(<string>) : <value>

Takes a string (JSON / XML / any of the subformats with prefixes like "EPSG:xxx") and parses it into a coordinate system.  Parsing coordinate system data supports OSGEO:xxx codes (used in TMS), as well as ESRI and OPENLAYERS codes for web Mercator.

CoordSystemParseEpsg(<string>) : <epsg>

Takes a coordinate system string, checks if it specifies an EPSG code, and if so, returns the code.

CoordSystemParseOverrides(<string>) : <json>

Takes a coordinate system string and returns parameter overrides if they exist.

CoordSystemParseSrid(<string>) : <srid>

Takes a coordinate system string, checks if it specifies an SRID code (a coordinate system local to a database), and if so, returns the code.

CoordSystemParseSridSubtype(<string>) : <subtype>

Takes a coordinate system string, checks if it specifies an SRID code, and if so, returns the SRID subtype.  For example, 'SRID:SDE:2230' returns 'SDE'.  The table with the definition of the coordinate system is named 'MFD_SRID_SDE'.

CoordSystems() : <table>

Returns a list of coordinate systems in the internal database.  

 

? CALL CoordSystems()

 

CoordSystemTypeParameters(<system>, <withScalesShifts>) : <table>

Returns a list of parameters for the specified coordinate system type.  Example:

 

? CALL CoordSystemTypeParameters('Lambert Conformal Conic', TRUE)

 

CoordSystemTypes() : <table>

Returns a list of supported coordinate system types.  

 

? CALL CoordSystemTypes()

 

CoordSystemXml(<system>) : <xml>

Takes a coordinate system definition and converts it to Manifold 8 XML. Not all systems can be converted (example: EPSG:5515 can not be converted, because the system type is modified Krovak and Manifold 8 does not support that), but the vast majority can.

 

? CoordSystemXml('EPSG:4200')

 

? CoordSystemXml('{ "System": "Azimuthal Equidistant" }')

 

CoordTransform(<manifold>) : <value>

Returns the definition of the coordinate transform identified by the specified Manifold ID.  Use CoordTransforms() to see a list of IDs for transforms.  

 

? CoordTransform(399)

 

Manifold ID 399 is the same transform as EPSG:1064.

 

CoordTransformEpsg(<epsg>) : <value>

Returns the definition of the coordinate transform identified by the specified EPSG code.  Use CoordTransforms() to see a list of EPSG codes for transforms.

 

? CoordTransformEpsg(1064)

 

EPSG:1064 is the same transform as Manifold ID 399.

 

CoordTransforms() : <table>

Returns a list of coordinate system transforms in the internal database.

 

? CALL CoordTransforms()

 

CoordTransformTypeParameters(<transform>) : <table>

Returns a list of parameters for the specified coordinate system transform type. The returned table contains a field for the parameter name and a field for the default value.

 

? CALL CoordTransformTypeParameters('Complex Polynomial (3)')

 

CoordTransformTypes() : <table>

Returns a list of supported coordinate transform types.

 

? CALL CoordTransformTypes()

 

CoordUnit(<manifold>) : <value>

Returns the definition of the coordinate system unit for the coordinate system unit  identified by the specified Manifold ID.  Use CoordUnits() to see a list of Manifold IDs for units.

 

? CoordUnit(17)

 

The coordinate system unit specified by Manifold ID 17 is the same, Clarke's Foot, as specified by EPSG:9005.

 

CoordUnitByName(<name>) : <value>

Takes a coordinate system unit name and returns the unit definition.  Remember to escape any single quote ' characters used in the name of a unit.

 

? CoordUnitByName('Clarke\'s Foot')

 

CoordUnitDefault() : <value>

Returns the definition of the default / default coordinate system unit.

 

? CoordUnitDefault()

 

CoordUnitDefaultLatLon() : <value>

Returns the definition of the lat/lon coordinate system unit.

 

? CoordUnitDefaultLatLon()

 

CoordUnitEpsg(<epsg>) : <value>

Returns the definition of the coordinate system unit for the coordinate system unit identified by the specified EPSG code.   Use CoordUnits() to see a list of EPSG codes for units.

 

 ? CoordUnitEpsg(9005)

 

The coordinate system unit specified by EPSG:9005 is the same, Clarke's Foot, as specified by Manifold ID 17.

 

CoordUnits() : <table>

Returns a list of coordinate system units. Returned tables contain a field for the Manifold ID, a field for the corresponding EPSG code, which can be NULL, a JSON definition of the described object, and sometimes additional data.

 

? CALL CoordUnits()

 

Corr(<value>, <value>) : <value>

An aggregate function:  Takes two parameters and computes correlation.  See the Aggregates topic.

Cos(<radians>) : <value>

Returns the cosine of the value.

Cosh(<radians>) : <value>

Returns the hyperbolic cosine of the value.

Count(<value>) : <value>

An aggregate function:  Counts the number of non-NULL values in a field or expression or, as Count(*), the total number of records.  See the Aggregates topic.

Covar(<value>, <value>) : <value>

An aggregate function:  Takes two parameters and computes sample covariance.  See the Aggregates topic.

CovarPop(<value>, <value>) : <value>

An aggregate function:  Takes two parameters and computes population covariance.  See the Aggregates topic.

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.

DateTimeDay(<date>) : <value>

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

 

? DateTimeDay(#01/21/2017#)

 

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#)

 

DateTimeMillisecond(<date>) : <value>

Given a datetime value returns the millisecond value for the time.

 

? 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#)

 

DateTimeYear(<date>) : <value>

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

 

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

 

Erf(<value>) : <value>

Computes error function.

Erfc(<value>) : <value>

Computes complementary error function.

EXISTS <query>

Returns True (1) if the argument contains at least one record.

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.

First(<value>) : <value>

An aggregate function:  Returns the first value for the specified field.  Does not skip NULLs.  Given that tables should be considered unordered, not a function of the most universal utility.

 

SELECT First([Product Name]) FROM [Products];

 

See the Aggregates topic.

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.   See the Fractional Part template in the Transform Templates - Numeric topic.

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

Returns the decimal fractional portion of the value beyond the specified number of decimal places.   See the Fractional Part from Decimals template in the Transform Templates - Numeric topic.

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 Google geocoding data source, returns the longitude,latitude coordinates for the specified street address in New York state.

 

? GeocodeAddress([Google], '16 Bush Lane Ithaca NY')  

  

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

 

? GeocodeAddress([Google], 'Chicago')

 

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([Google], 'Bush Lane, Ithaca NY')

 

(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.)

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([Google])

 

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([Google], VectorMakeX2(-76.4866069,42.495071) )

 

(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.)

 

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([Google])

 

GeomAdjacent(<geom>, <geom>, <tolerance>) : <value>

Given two geoms returns true if they are adjacent. A tolerance of zero means automatic tolerance.   For the definition of adjacent see the Transform: Overlay topic.

GeomArea(<geom>, <tolerance>) : <value>

Given a geom that contains an area object, returns the area of that area object in square units of measure used by the coordinate system.  A tolerance of zero means automatic tolerance.

 

SELECT GeomArea([Geom], 0) AS [Computed Area] FROM [Mexico];

 

GeomBounds(<geom>) : <geom>

Given a geom, return a geom that in the case of areas is the border of the area and in the case of lines is the two end coordinates of the line as a multipoint.  NULL is returned for points.   See the Bounds transform in the Transform Templates - Geom topic.

GeomBoundsCircle(<geom>, <tolerance>) : <geom>

Given a geom, return a geom for an area object that in the case of areas or lines is the minimal enclosing circle.  NULL is returned for points. A tolerance of zero means automatic tolerance.   See the Enclosing Circle transform in the Transform Templates - Geom topic.

GeomBoundsRect(<geom>) : <valuex4>

Given a geom, return a float64x4 value that gives the location of diagonally opposite corners which define a vertically oriented rectangle that is the minimum enclosing rectangle for areas or lines.  NULL is returned for points.  See the Enclosing Rect transform in the Transform Templates - Geom topic.  

 

Use the GeomMakeRect function to create a rectangular area object from the float64x4 value returned by GeomBoundsRect, as in

 

UPDATE (

  SELECT [mfd_id],  [Geom],

    GeomMakeRect(GeomBoundsRect([Geom])) AS [n_Geom]

  FROM [Table Drawing]

) SET [Geom] = [n_Geom];

 

GeomBoundsRectRotated(<geom>, <tolerance>) : <geom>

Given a geom, return a float64x4 value that gives the location of diagonally opposite corners which define a rectangle of any orientation that is the minimum enclosing rectangle for areas or lines.  NULL is returned for points.  A tolerance of zero means automatic tolerance.  See the Enclosing Rect, Rotation Allowed transform in the Transform Templates - Geom topic.  

GeomBranch(<geom>, <branch>) : <geom>

Takes a geom and a branch number (0 is the first branch) and returns the specified branch as a geom.  See the Branch transform in the Transform Templates - Geom topic.  

GeomBranchCount(<geom>) : <value>

Takes a geom and returns the number of branches in that geom.

GeomBranchFirst(<geom>, <branch>) : <value>

Given a geom and a branch number (0 is the first branch) returns the index of the first coordinate in the specified branch.

GeomBranchLast(<geom>, <branch>) : <value>

Given a geom and a branch number (0 is the first branch) returns the index of the last coordinate in the specified branch.

GeomBuffer(<geom>, <distance>, <tolerance>) : <geom>

Given a geom, a distance and a tolerance, returns a geom for an area object the border of which is the specified distance from any part of the object in the geom.   A tolerance of zero means automatic tolerance.  See the Buffer transform in the Transform Templates - Geom topic.  

GeomCenter(<geom>, <tolerance>) : <valuex2>

Given a geom returns a float64x2 value that specifies the location of the center of the minimum enclosing circle for areas and lines and which for points specifies the location of the point.   A tolerance of zero means automatic tolerance.  See the Center transform in the Transform Templates - Geom topic.  

GeomCenterInner(<geom>, <tolerance>) : <valuex2>

Given a geom returns a float64x2 value that specifies the location of the center of the minimum enclosing circle for areas, with the location adjusted so that it falls within the area object.   NULL is returned for lines and points.  A tolerance of zero means automatic tolerance.  See the Center, Inner transform in the Transform Templates - Geom topic.  

GeomCenterWeight(<geom>, <tolerance>) : <valuex2>

Given a geom returns a float64x2 value that specifies the location of the balance point  for areas   NULL is returned for lines and points.  A tolerance of zero means automatic tolerance.  See the Center, Weight transform in the Transform Templates - Geom topic.  

GeomClip(<geom>, <geom>, <inner>, <tolerance>) : <geom>

Given two geoms, use the geom in the second argument to "cookie cut," that is, to clip, the geom in the first argument, returning the result as a geom.   If the <inner> argument is true, leave the inner portion of the cut.   If the <inner> argument is false, leave the outer portion.   A tolerance of zero means automatic tolerance.   See illustrations and discussion in the Example: Clip Areas with a Transform Dialog Expression topic.

GeomContains(<geom>, <geom>, <tolerance>) : <value>

Given two geoms returns true if the first geom contains the second geom. A tolerance of zero means automatic tolerance.   For the definition of contains see the Transform: Overlay topic.

 

Example: A table contains a geom field called Buffer which are buffer areas that are larger than and were created from objects in the Geom field using the GeomBuffer function.  The Buffer objects contain the Geom objects.  

 

SELECT GeomContains([Buffer], [Geom], 0) FROM [Table];

 

The above returns a results table where all rows have 1 for true because all of the Buffer objects fully contain the corresponding Geom object.

 

SELECT GeomContains([Geom], [Buffer], 0) FROM [Table];

 

The above returns a results table where all rows have 0 for false, because none of the Geom objects contain the corresponding Buffer object.  

GeomConvertToArea(<geom>) : <geom>

Takes a geom of arbitrary type and converts it to an area geom. Branches are auto-closed (if the starting coordinate of the branch does not coincide with the ending coordinate, the starting coordinate is added once again at the end). Curves and Z values are preserved. Points are converted into pathological areas defined by three coordinates that are all at the same location. See the Convert to Area transform in the Transform Templates - Geom topic.  

GeomConvertToLine(<geom>) : <geom>

Takes a geom of arbitrary type and converts it to a line geom. Curves and Z values are preserved. Points are converted into pathological lines defined by two coordinates that are both at the same location. See the Convert to Line transform in the Transform Templates - Geom topic.  

GeomConvertToPoint(<geom>, <keepBranches>) : <geom>

Takes a geom of arbitrary type and converts it to a point geom. Converting objects such as areas, lines or multipoints will result in a multipoint.  The <keepBranches> argument is true or false, controlling whether to keep original branches (true) or join all coordinates into a single branch (false). Z values are preserved.  See the Convert to Line transform in the Transform Templates - Geom topic.  

GeomConvexHull(<geom>, <tolerance>) : <geom>

Given a geom, return an area object geom that is the convex hull enclosing the geom.  NULL is returned for points. A tolerance of zero means automatic tolerance.   See the Convex Hull transform in the Transform Templates - Geom topic.

GeomCoordCount(<geom>) : <value>

Given a geom returns the number of coordinates in the object the geom contains.  A triangular area contains 4 coordinates because the last coordinate is the same as the first to close the area.

GeomCoordXY(<geom>, <coord>) : <valuex2>

Given a geom and the index of a coordinate in that geom (0 based numbering, the first coordinate is the 0 coordinate), returns the value of that coordinate as a float64x2 value, representing the x and the y coordinate numbers.

GeomCoordXYZ(<geom>, <coord>) : <valuex3>

Given a geom and the index of a coordinate in that geom (0 based numbering, the first coordinate is the 0 coordinate), returns the value of that coordinate as a float64x3 value, representing the x, y and z coordinate numbers.

GeomDistance(<geom>, <geom>, <tolerance>) : <value>

Takes two geoms and returns the Euclidean distance between the geoms, reported in whatever units are used for the geoms. The geoms are assumed to be in the same coordinate system.   A tolerance of zero means automatic tolerance.

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

Takes a pair of x2 values and returns the Euclidean distance in the 2D plane between the locations defined by these values.

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

Takes a pair of x3 values and returns the Euclidean distance in 3D space between the locations defined by these values.

GeomGml(<geom>, <system>) : <gml>

Takes a geom and an optional coordinate system (pass an empty string for no optional coordinate system) and returns GML for the geom.

 

SELECT GeomGml([Geom],'') FROM [Table];

 

for no optional coordinate system, and

 

SELECT GeomGml([Geom],'EPSG:4200') FROM [Table];

 

to specify EPSG:4200 as the coordinate system.

GeomHasCurves(<geom>) : <value>

Takes a geom and returns true if the geom has any curvilinear segments.  

GeomHasZ(<geom>) : <value>

Takes a geom and returns true if the geom has a Z value, that is, is 3D.

GeomIntersectLinesPair(<geom>, <geom>, <tolerance>) : <geom>

Takes two lines and returns their intersection points as a geom.  The geom will be one point one intersection and will be a multipoint if there are multiple intersections.      A tolerance of zero means automatic tolerance.

GeomIntersects(<geom>, <geom>, <tolerance>) : <value>

Given two geoms returns true if they intersect. A tolerance of zero means automatic tolerance.   For the definition of intersect see the Transform: Overlay topic.

GeomIsArea(<geom>) : <value>

Returns true if the geom is an area.

GeomIsLine(<geom>) : <value>

Returns true if the geom is a line.

GeomIsNormalized(<geom>) : <value>

Returns true if the geom is normalized.  The corresponding Normalized Geoms template in the Select dialog selects normalized geoms.

 

The system keeps track of geoms that have been normalized.  Normalized geometry values store an explicit normalized flag plus additional normalize data which allows quickly transforming geometry to normalized form required by OGC and the different normalized form required by ESRI.

GeomIsPoint(<geom>) : <value>

Returns true if the geom is a point.

GeomJsonGeo(<geom>, <system>) : <json>

Takes a geom and an optional coordinate system (pass an empty string for no optional coordinate system) and returns GeoJSON for the geom.

 

SELECT GeomJsonGeo([Geom],'') FROM [Table];

 

for no optional coordinate system, and

 

SELECT GeomJsonGeo([Geom],'EPSG:4200') FROM [Table];

 

to specify EPSG:4200 as the coordinate system.

GeomLength(<geom>, <tolerance>) : <value>

Given a geom returns the length of lines and areas.  NULL is returned for points. A tolerance of zero means automatic tolerance.  The length of an area object is the length of its boundary, that is, the perimeter of the area.  The length reported for a branched object is the sum of the lengths of the branches.

 

SELECT GeomLength([Geom], 0) FROM [Table];

 

GeomLinearize(<geom>, <tolerance>, <limit>) : <geom>

Convert curvilinear segments to straight line segments. Takes a geom, linearizes all curvilinear segments and returns the result.   A tolerance of zero means automatic tolerance. The linearization process is guided by the tolerance value and the maximum number of coordinates, 1 or more specified as the <limit> argument, to which a single curvilinear segment will be expanded.  See the Linearize transform in the Transform Templates - Geom topic.  

GeomMakeCircle(<valuex2>, <radius>) : <geom>

Takes an x2 value that defines the center and a radius value and returns a geom for a circular area object centered on that location with that radius.  See the Compose Circle transform in the Transform Templates - Geom topic.  

GeomMakePoint(<valuex2>) : <geom>

Takes an x2 value that defines a location and returns a geom for a point object at that location.  See the Compose Point transform in the Transform Templates - Geom topic.  

GeomMakePoint3(<valuex3>) : <geom>

Takes an x3 value that defines a 3D location (X, Y and Z) and returns a geom for a 3D point object at that location.  See the Compose Point with Z transform in the Transform Templates - Geom topic.  

GeomMakeRect(<valuex4>) : <geom>

Takes an x4 value that defines diagonally opposite corners of a rectangle and returns a geom for a rectangular area object defined by those corner locations. See the Compose Rectangle transform in the Transform Templates - Geom topic.  

GeomMakeRectDiagonal(<valuex2>, <valuex2>) : <geom>

Takes a pair of x2 values that define diagonally opposite corners of a rectangle and returns a geom for a rectangular area object defined by those corner locations.

GeomMakeSegment(<valuex2>, <valuex2>) : <geom>

Takes a pair of x2 values and returns a line geom consisting of a straight line segment between those two locations. See the Compose Segment transform in the Transform Templates - Geom topic.  

GeomMakeSegment3(<valuex3>, <valuex3>) : <geom>

Takes a pair of x3 values and returns a 3D line geom consisting of a straight line segment between those two 3D locations.

GeomMakeTriangle(<valuex2>, <valuex2>, <valuex2>) : <geom>

Takes three x2 values that define the corners of a triangle and returns a geom for a triangular area object defined by those corner locations. See the Compose Triangle transform in the Transform Templates - Geom topic.  

GeomMakeTriangle3(<valuex3>, <valuex3>, <valuex3>) : <geom>

Takes three x3 values that define the corners of a triangle in 3D and returns a geom for a 3D triangular area object defined by those corner locations.

GeomMergeAreas(<geom>) : <geom>

An aggregate function:  Takes a set of areas and returns an area geom that contains branches.  Curves and Z values are removed.  See the discussion in the Aggregates topic.

GeomMergeAreasPair(<geom>, <geom>) : <geom>

The non-aggregate version of GeomMergeAreas.  Takes two areas and returns an area geom that contains branches.  Curves and Z values are removed.  See the discussion in the Aggregates topic.

GeomMergeLines(<geom>) : <geom>

An aggregate function:  Takes a set of lines and returns a line geom that contains branches.  Curves and Z values are removed.  See the discussion in the Aggregates topic.

GeomMergeLinesPair(<geom>, <geom>) : <geom>

The non-aggregate version of GeomMergeLines.  Takes two lines and returns a line geom that contains branches.  Curves and Z values are removed.  See the discussion in the Aggregates topic.

GeomMergePoints(<geom>) : <geom>

An aggregate function:  Takes a set of points and returns a multipoint geom that contains branches.  Z values are removed.  See the discussion in the Aggregates topic.

GeomMergePointsPair(<geom>, <geom>) : <geom>

The non-aggregate version of GeomMergePoints.  Takes two points and returns a multipoint geom that contains branches.  Z values are removed.  See the discussion in the Aggregates topic.

GeomNormalize(<geom>, <tolerance>) : <geom>

Normalize object metrics by removing redundant coordinates and note the geom has been normalized.   A tolerance of zero means automatic tolerance. See the Normalize transform in the Transform Templates - Geom topic.  

GeomNormalizeTopology(<drawing>, <tolerance>) : <table>

Takes a drawing and returns a table with the drawing's geoms normalized, providing a table with the same fields but with the values in the geom field altered.   A tolerance of zero means automatic tolerance. See the Normalize transform in the Transform Templates - Geom topic.  

GeomOverlayAdjacent(<drawing>, <overlay>, <tolerance>) : <table>

Combines fields between a source drawing and an overlay drawing, finding cases where objects in the overlay are adjacent to objects in the source.  Given a drawing and an overlay drawing, returns a table with the following characteristics:

 

  • Columns: The returned table will contain all fields from the source drawing and all fields from the overlay drawing.  Fields from the source drawing will have their names prefixed with s_ for source.  Fields from the overlay drawing will have their names prefixed with o_ for overlay.  

 

  • Rows: Every object in the source drawing that is not adjacent to an object in the overlay drawing will appear as a row in the table with values for the o_ fields set to NULL.   Every object in the source drawing that is adjacent to one or more objects in the overlay drawing will appear in one or more rows in the table with values for the o_ fields populated from the corresponding object to which it is adjacent.

 

A tolerance of zero means automatic tolerance. The table supports all indexes of the source drawing. The overlay is performed in the coordinate system of the source drawing; objects in the overlay drawing are automatically converted to that coordinate system as necessary.   We can use the table created by this function as desired in subsequent operations, for example, creating a drawing via a query that uses aggregates.

 

See the Overlay Adjacent discussion in the Overlays section of the Transform Templates - Drawings topic.  See the SQL Example: GeomOverlayAdjacent Function topic.

GeomOverlayAdjacentPar(<drawing>, <overlay>, <tolerance>, <config>) : <table>

A parallelized form of the GeomOverlayAdjacent function that takes a parameter for the suggested number of threads as encoded in the JSON <config> string as "threads".  If the number of threads is less than or equal to 1 the GeomOverlayAdjacent function will be executed.  Use ThreadConfig to generate a JSON string with the desired number of threads.

GeomOverlayContained(<drawing>, <overlay>, <tolerance>) : <table>

Combines fields between a source drawing and an overlay drawing, finding cases where objects in the overlay are contained by objects in the source. Given a drawing and an overlay drawing, returns a table with the following characteristics:

 

  • Columns: The returned table will contain all fields from the source drawing and all fields from the overlay drawing.  Fields from the source drawing will have their names prefixed with s_ for source.  Fields from the overlay drawing will have their names prefixed with o_ for overlay.  

 

  • Rows: Every object in the source drawing that does not contain an object in the overlay drawing will appear as a row in the table with values for the o_ fields set to NULL.   Every object in the source drawing that does contain one or more objects in the overlay drawing will appear in one or more rows in the table with values for the o_ fields populated from the corresponding object to which it is adjacent.

 

A tolerance of zero means automatic tolerance. The table supports all indexes of the source drawing. The overlay is performed in the coordinate system of the source drawing; objects in the overlay drawing are automatically converted to that coordinate system as necessary.   We can use the table created by this function as desired in subsequent operations, for example, creating a drawing via a query that uses aggregates.

 

See the Overlay Contained discussion in the Overlays section of the Transform Templates - Drawings topic.  See the SQL Example: GeomOverlayAdjacent Function topic for an example of how functions like this are used.

GeomOverlayContainedPar(<drawing>, <overlay>, <tolerance>, <config>) : <table>

A parallelized form of the GeomOverlayContained function that takes a parameter for the suggested number of threads as encoded in the JSON <config> string as "threads".  If the number of threads is less than or equal to 1 the GeomOverlayContained function will be executed. Use ThreadConfig to generate a JSON string with the desired number of threads.

GeomOverlayContaining(<drawing>, <overlay>, <tolerance>) : <table>

Combines fields between a source drawing and an overlay drawing, finding cases where objects in the overlay are containing objects in the source.  Given a drawing and an overlay drawing, returns a table with the following characteristics:

 

  • Columns: The returned table will contain all fields from the source drawing and all fields from the overlay drawing.  Fields from the source drawing will have their names prefixed with s_ for source.  Fields from the overlay drawing will have their names prefixed with o_ for overlay.  

 

  • Rows: Every object in the source drawing that is not contained by an object in the overlay drawing will appear as a row in the table with values for the o_ fields set to NULL.   Every object in the source drawing that is contained by one or more objects in the overlay drawing will appear in one or more rows in the table with values for the o_ fields populated from the corresponding object to which it is adjacent.

 

A tolerance of zero means automatic tolerance. The table supports all indexes of the source drawing. The overlay is performed in the coordinate system of the source drawing; objects in the overlay drawing are automatically converted to that coordinate system as necessary.   We can use the table created by this function as desired in subsequent operations, for example, creating a drawing via a query that uses aggregates.

 

See the Overlay Containing discussion in the Overlays section of the Transform Templates - Drawings topic.  See the SQL Example: GeomOverlayAdjacent Function topic for an example of how functions like this are used.

GeomOverlayContainingPar(<drawing>, <overlay>, <tolerance>, <config>) : <table>

A parallelized form of the GeomOverlayContaining function that takes a parameter for the suggested number of threads as encoded in the JSON <config> string as "threads".  If the number of threads is less than or equal to 1 the GeomOverlayContaining function will be executed. Use ThreadConfig to generate a JSON string with the desired number of threads.

GeomOverlayIntersecting(<drawing>, <overlay>, <tolerance>) : <table>

Combines fields between a source drawing and an overlay drawing, finding cases where objects in the overlay are intersecting objects in the source.  Given a drawing and an overlay drawing, returns a table with the following characteristics:

 

  • Columns: The returned table will contain all fields from the source drawing and all fields from the overlay drawing.  Fields from the source drawing will have their names prefixed with s_ for source.  Fields from the overlay drawing will have their names prefixed with o_ for overlay.  

 

  • Rows: Every object in the source drawing that is not intersected by an object in the overlay drawing will appear as a row in the table with values for the o_ fields set to NULL.   Every object in the source drawing that is intersected by one or more objects in the overlay drawing will appear in one or more rows in the table with values for the o_ fields populated from the corresponding object to which it is adjacent.

 

A tolerance of zero means automatic tolerance. The table supports all indexes of the source drawing. The overlay is performed in the coordinate system of the source drawing; objects in the overlay drawing are automatically converted to that coordinate system as necessary.   We can use the table created by this function as desired in subsequent operations, for example, creating a drawing via a query that uses aggregates.

 

See the Overlay Intersecting discussion in the Overlays section of the Transform Templates - Drawings topic.  See the SQL Example: GeomOverlayAdjacent Function topic for an example of how functions like this are used.

GeomOverlayIntersectingPar(<drawing>, <overlay>, <tolerance>, <config>) : <table>

A parallelized form of the GeomOverlayIntersecting function that takes a parameter for the suggested number of threads as encoded in the JSON <config> string as "threads".  If the number of threads is less than or equal to 1 the GeomOverlayIntersecting function will be executed.  Use ThreadConfig to generate a JSON string with the desired number of threads.

GeomSplitToConvex(<area>, <tolerance>) : <areas>

Takes an area geom and decomposes it into convex parts. A tolerance of zero means automatic tolerance. See the discussion and illustration for Decompose to Convex Parts in the Transform Templates - Drawings topic.

GeomSplitToConvexPar(<area>, <tolerance>, <config>) : <areas>

A parallelized form of the GeomSplitToConvex function that takes a parameter for the suggested number of threads as encoded in the JSON <config> string as "threads".  If the number of threads is less than or equal to 1 the GeomSplitToConvex function will be executed.  Use ThreadConfig to generate a JSON string with the desired number of threads.

GeomOverlayTopologyIdentity(<drawing>, <overlay>, <tolerance>) : <table>

Using the area boundaries of objects in the overlay drawing, slice all objects in the source drawing into regions that are overlapped by objects in the overlay drawing and that are not overlapped by objects in the overlay drawing.  Save all the pieces thus created as geoms into the result table.  See the discussion and illustrations for Topology Overlay, Identity in the Transform: Overlay Topology topic.   Given a drawing and an overlay drawing, returns a table with the following characteristics:

 

  • Columns: The returned table will contain all fields from the source drawing and all fields from the overlay drawing.  Fields from the source drawing will have their names prefixed with s_ for source.  Fields from the overlay drawing will have their names prefixed with o_ for overlay.  

 

  • Rows for non-intersecting objects: Every region of an object in the source drawing that is not intersected by an object in the overlay drawing will appear as a row in the table with values for the o_ fields set to NULL.  

 

  • Rows for intersecting objects: Every region of an object in the source drawing that is intersected by one or more objects in the overlay drawing will appear as a single row in the table with s_ fields and o_ fields populated by the respective objects from the source and overlay drawings that intersected.

 

A tolerance of zero means automatic tolerance. The table supports all indexes of the source drawing. The overlay is performed in the coordinate system of the source drawing; objects in the overlay drawing are automatically converted to that coordinate system as necessary.   We can use the table created by this function as desired in subsequent operations, for example, creating a drawing via a query that uses aggregates.

 

See the SQL Example: GeomOverlayTopologyUnion Function topic for an example of how functions like this are used.

GeomOverlayTopologyIdentityPar(<drawing>, <overlay>, <tolerance>, <config>) : <table>

A parallelized form of the GeomOverlayTopologyIdentity function that takes a parameter for the suggested number of threads as encoded in the JSON <config> string as "threads".  If the number of threads is less than or equal to 1 the GeomOverlayTopologyIdentity function will be executed. Use ThreadConfig to generate a JSON string with the desired number of threads.

GeomOverlayTopologyIntersect(<drawing>, <overlay>, <tolerance>) : <table>

Locate regions of intersection and save them into the results table.  Slices all objects in each drawing with the area boundaries of objects in the other drawing and then places geoms for all regions of intersection, discarding duplicates, into the result table.  See the discussion and illustrations for Topology Overlay, Intersect in the Transform: Overlay Topology topic. Given a drawing and an overlay drawing, returns a table with the following characteristics:

 

  • Columns: The returned table will contain all fields from the source drawing and all fields from the overlay drawing.  Fields from the source drawing will have their names prefixed with s_ for source.  Fields from the overlay drawing will have their names prefixed with o_ for overlay.  

 

  • Rows:  Each region of intersection will appear as a row in the table with s_ fields and o_ fields populated by the respective objects from the source and overlay drawings that intersected.

 

A tolerance of zero means automatic tolerance. The table supports all indexes of the source drawing. The overlay is performed in the coordinate system of the source drawing; objects in the overlay drawing are automatically converted to that coordinate system as necessary.   We can use the table created by this function as desired in subsequent operations, for example, creating a drawing via a query that uses aggregates.  

 

See the SQL Example: GeomOverlayTopologyUnion Function topic for an example of how functions like this are used.

GeomOverlayTopologyIntersectPar(<drawing>, <overlay>, <tolerance>, <config>) : <table>

A parallelized form of the GeomOverlayTopologyIntersect function that takes a parameter for the suggested number of threads as encoded in the JSON <config> string as "threads".  If the number of threads is less than or equal to 1 the GeomOverlayTopologyIntersect function will be executed. Use ThreadConfig to generate a JSON string with the desired number of threads.

GeomOverlayTopologyUnion(<drawing>, <overlay>, <tolerance>) : <table>

Slices all objects in each drawing with the area boundaries of objects in the other drawing and then places geoms for all pieces, discarding duplicates, into the result table.   See the discussion and illustrations for Topology Overlay, Union in the Transform: Overlay Topology topic.   Given a drawing and an overlay drawing, returns a table with the following characteristics:

 

  • Columns: The returned table will contain all fields from the source drawing and all fields from the overlay drawing.  Fields from the source drawing will have their names prefixed with s_ for source.  Fields from the overlay drawing will have their names prefixed with o_ for overlay.  

 

  • Rows for non-intersecting objects: Every object in the source drawing that is not intersected by an object in the overlay drawing will appear as a row in the table with values for the o_ fields set to NULL.  Every object in the overlay drawing that is not intersected by an object in the source drawing will appear as a row in the table with values for the s_ fields set to NULL.   

 

  • Rows for intersecting objects: Every object in the source drawing that is intersected by one or more objects in the overlay drawing will appear as a single row in the table with values for the o_ fields set to NULL but will have the geom altered to remove all regions of intersection from the object.  Each region of intersection will appear as a row in the table with s_ fields and o_ fields populated by the respective objects from the source and overlay drawings that intersected.

 

A tolerance of zero means automatic tolerance. The table supports all indexes of the source drawing. The overlay is performed in the coordinate system of the source drawing; objects in the overlay drawing are automatically converted to that coordinate system as necessary.   We can use the table created by this function as desired in subsequent operations, for example, creating a drawing via a query that uses aggregates.

 

See the SQL Example: GeomOverlayTopologyUnion Function topic for an example of how functions like this are used.

GeomOverlayTopologyUnionPar(<drawing>, <overlay>, <tolerance>, <config>) : <table>

A parallelized form of the GeomOverlayTopologyUnion function that takes a parameter for the suggested number of threads as encoded in the JSON <config> string as "threads".  If the number of threads is less than or equal to 1 the GeomOverlayTopologyUnion function will be executed. Use ThreadConfig to generate a JSON string with the desired number of threads.

GeomOverlayTopologyUpdate(<drawing>, <overlay>, <tolerance>) : <table>

Keep all objects in the overlay drawing.  In the source drawing cut out and discard any portions of objects in the source drawing that intersect with objects in the overlay drawing and leave the remaining portions.  Place geoms for all objects in the overlay drawing plus non-intersecting portions of objects in the source drawing into the result table.  See the discussion and illustrations for Topology Overlay, Update in the Transform: Overlay Topology topic.   Given a drawing and an overlay drawing, returns a table with the following characteristics:

 

  • Columns: The returned table will contain all fields from the source drawing and all fields from the overlay drawing.  Fields from the source drawing will have their names prefixed with s_ for source.  Fields from the overlay drawing will have their names prefixed with o_ for overlay.  

 

  • Rows for non-intersecting objects: Every object in the overlay drawing will appear as a row in the table with values for the s_ fields set to NULL.   Every object in the source drawing that is not intersected by an object in the overlay drawing will appear as a row in the table with values for the o_ fields set to NULL.  

 

  • Rows for intersecting objects: Every object in the source drawing that is intersected by one or more objects in the overlay drawing will appear as a single row in the table with values for the o_ fields set to NULL but will have the geom altered to remove all regions of intersection from the object.

 

A tolerance of zero means automatic tolerance. The table supports all indexes of the source drawing. The overlay is performed in the coordinate system of the source drawing; objects in the overlay drawing are automatically converted to that coordinate system as necessary.   We can use the table created by this function as desired in subsequent operations, for example, creating a drawing via a query that uses aggregates.

 

See the SQL Example: GeomOverlayTopologyUnion Function topic for an example of how functions like this are used.

GeomOverlayTopologyUpdatePar(<drawing>, <overlay>, <tolerance>, <config>) : <table>

A parallelized form of the GeomOverlayTopologyUpdate function that takes a parameter for the suggested number of threads as encoded in the JSON <config> string as "threads".  If the number of threads is less than or equal to 1 the GeomOverlayTopologyUpdate function will be executed. Use ThreadConfig to generate a JSON string with the desired number of threads.

GeomOverlayTouching(<drawing>, <overlay>, <tolerance>) : <table>

Combines fields between a source drawing and an overlay drawing, finding cases where objects in the overlay are touching objects in the source.  Given a drawing and an overlay drawing, returns a table with the following characteristics:

 

  • Columns: The returned table will contain all fields from the source drawing and all fields from the overlay drawing.  Fields from the source drawing will have their names prefixed with s_ for source.  Fields from the overlay drawing will have their names prefixed with o_ for overlay.  

 

  • Rows: Every object in the source drawing that is not touched by an object in the overlay drawing will appear as a row in the table with values for the o_ fields set to NULL.   Every object in the source drawing that is touched by one or more objects in the overlay drawing will appear in one or more rows in the table with values for the o_ fields populated from the corresponding object to which it is adjacent.

 

A tolerance of zero means automatic tolerance. The table supports all indexes of the source drawing. The overlay is performed in the coordinate system of the source drawing; objects in the overlay drawing are automatically converted to that coordinate system as necessary.   We can use the table created by this function as desired in subsequent operations, for example, creating a drawing via a query that uses aggregates.

 

See the Overlay Touching discussion in the Overlays section of the Transform Templates - Drawings topic.  See the SQL Example: GeomOverlayAdjacent Function topic for an example of how functions like this are used.

GeomOverlayTouchingPar(<drawing>, <overlay>, <tolerance>, <config>) : <table>

A parallelized form of the GeomOverlayTouching function that takes a parameter for the suggested number of threads as encoded in the JSON <config> string as "threads".  If the number of threads is less than or equal to 1 the GeomOverlayTouching function will be executed. Use ThreadConfig to generate a JSON string with the desired number of threads.

GeomRemoveCurves(<geom>) : <geom>

Returns a geom where each curved segment of an area or line object in the source geom has been replaced with a single straight segment.   Geoms without curved segments are unchanged.

GeomRemoveZ(<geom>) : <geom>

Takes a geom and returns the same geom without Z values. Geoms without Z values are left unchanged. For geoms with Z values curves are replaced with straight line segments, because 3D curves can not be used with a 2D geom.

GeomRotate(<geom>, <centerx2>, <angle>) : <geom>

Takes a geom, a rotation center as an x2 vector value, a rotation angle in degrees, and returns a rotated geom. The rotation direction is counterclockwise. Z values are preserved but curves are replaced with straight line segments.

GeomScaleShift(<geom>, <scalex2>, <shiftx2>) : <geom>

Takes a geom, an x2 vector for scale factor values in X and Y direction, an x2 vector for shift values in X and Y direction in the units of the coordinate system used, and returns a geom with those scale and shift values applied.   Z values are preserved but curves are replaced with straight line segments.

GeomScaleShiftZ(<geom>, <scale>, <shift>) : <geom>

Takes a geom, a scale factor value in Z direction, a shift value in Z direction in the units of the coordinate system used, and returns a geom with those scale and shift values applied to the Z values. Geoms with no Z values are left unchanged. For geoms with Z values curves are replaced with straight line segments.

GeomScaleToSystem(<geom>, <system>, <unit>) : <geom>

Takes the same parameters as GeomScaleToUnits, and scales the geom back to the coordinate system.

GeomScaleToSystemCff(<geom>, <system>, <unit>, <scalex2>) : <geom>

Takes the same parameters as GeomScaleToUnitsCff, and scales the geom back to the coordinate system.

GeomScaleToUnits(<geom>, <system>, <unit>) : <geom>

A useful function for doing computations in specific units: Takes a geom, the coordinate system of the geom (as returned by ComponentCoordSystem), a unit name, and returns the geom scaled to the specified units.

GeomScaleToUnitsCff(<geom>, <system>, <unit>, <scalex2>) : <geom>

Takes a geom, the coordinate system of the geom (as returned by ComponentCoordSystem), a unit name, an x2 vector for scale factor values in X and Y direction, and returns the geom scaled to the specified units with the scale factors applied.

GeomSegmentize(<geom>, <length>) : <geom>

Takes a geom and a length value and returns a geom where each segment is split to parts which are the same length or shorter than the specified length. Point geoms are left unchanged.  Z values are preserved but curves are replaced with straight line segments.

GeomSetZ(<geom>, <z>) : <geom>

Takes a geom and a Z value, and returns the same geom with Z values for all coordinates set to the specified value. Geoms without Z values become 3D. Curves are replaced with straight line segments.

GeomSmooth(<geom>, <length>) : <geom>

Takes a geom and a length value and returns a geom that removes detail shorter than the specified length by reducing the number of coordinates. Point geoms are left unchanged. Z values are eliminated. Curves are replaced with straight line segments.  See the Smooth transform in the Transform Templates - Geom topic.  

GeomSnapToGrid(<geom>, <stepsx2>) : <geom>

Takes a geom and  an x2 vector for distance values in X and Y direction and returns a geom with coordinates rounded to a rectangular grid with the specified steps. Curves are replaced with straight line segments.

GeomToBranches(<geom>) : <table>

Convert branched objects into a table of branches. Takes a geom and returns a table where each record contains a row for each branch of the object in the geom, with fields as follows:

 

  • Branch - index of the branch

  • Value - geom for that branch.

 

Example:

 

SELECT * FROM (

    SELECT SPLIT

      CALL GeomToBranches([Geom])

    FROM [Table]);

 

See the Decompose to Branches transform in the Transform Templates - Drawings topic.

GeomToCoords(<geom>) : <table>

Converts objects into their coordinates. Takes a geom and returns a table where each record contains a row for each coordinate of the object in the geom, with fields as follows:  

 

  • Branch - index of current branch.
  • Coord - index of current coordinate from the beginning of geom.
  • CoordInBranch - index of current coordinate within current branch.
  • XY - X, Y coordinates as a float64x2 vector.
  • XYZ - X, Y, Z coordinates as a float64x3 vector (NULL if there is no Z).

 

Example:

 

SELECT * FROM (

    SELECT SPLIT

      CALL GeomToCoords([Geom])

    FROM [Table]);

 

See the Decompose to Coordinates transform in the Transform Templates - Drawings topic.

GeomToSegments(<geom>) : <table>

Converts objects into their segments. Takes a geom and returns a table where each record contains a row for each segment of the object in the geom, with fields as follows:

 

  • Branch - index of current branch.
  • Coord - index of starting coordinate of the segment from the beginning of geom.
  • CoordInBranch - index of starting coordinate of the segmment within current branch.
  • XY - starting coordinate value of the segment in X, Y coordinates as a float64x2 vector.
  • XYNext - ending coordinate value of the segment in X, Y coordinates as a float64x2 vector.  
  • XYZ - starting coordinate value of the segment in X, Y, Z coordinates as a float64x3 vector  (NULL if there is no Z).  
  • XYZNext - ending coordinate value of the segment in X, Y, Z coordinates as a float64x3 vector  (NULL if there is no Z).
  • Type - segment type: 0 = straight line segment, 1 = circular arc, 2 = ellipsoidal arc, 3 = non-uniform b-spline.

 

Example:

 

SELECT * FROM (

    SELECT SPLIT

      CALL GeomToCoords([Geom])

    FROM [Table]);

 

See the Decompose to Segments transform in the Transform Templates - Drawings topic.

GeomToShapes(<geom>) : <table>

A shape is a branch of an area that forms an island.  It is also referred to as an individual polygon within a multipolygon object. This function splits area objects into their constituent shapes using OGC rules for doing so.  Use GeomToShapesEsri to split an area into shapes using ESRI rules.  Takes a geom and returns a table where each record contains a row for each shape, with fields as follows:

 

  • Shape - index of the shape within the original multipolygon.  
  • Branch - index of the original starting branch for this shape.
  • BranchCount - number of branches in this shape.
  • Value - the geom.

 

Points and lines are unchanged except that any curved segments in lines are replaced by straight segments.

 

Consider a table that has one object, a branched area that has islands as seen in the drawing below.

 

il_geomtoshapes_01.png

 

That area overall has five branches which make up three shapes.   Three of those branches are used to create the larger shape (one branch for the outer boundary and two branches for the inner holes) and two of the branches are used to create the islands.

 

SELECT * INTO [3_shapes]

  FROM (

    SELECT SPLIT

      CALL GeomToShapes([Geom])

    FROM [1_area]);

 

We can run the query above to use the GeomToShapes function to decompose the single area into three objects, one for each constituent shape.

 

il_geomtoshapes_02.png

The resulting table contains three rows, one for each of the objects that was created by splitting the original area into its constituent three shapes.

 

The first row is for the larger shape that has two holes.   It is the first shape so the Shape value is 0.   Branch is also 0 because this shape was started by the first branch in the original area.    BranchCount is 3 because this shape contains three branches, one branch for the outer boundary and two branches for the inner holes.

 

Shape number 1, the second shape, was Branch number 3 of the original area.  The BranchCount value for this shape is 1 because this shape contains only one branch.

 

Shape number 3, the third shape, was Branch number 4 of the original area.  The BranchCount value for this shape also is 1 because this shape contains only one branch.

 

See the Decompose to Shapes transform in the Transform Templates - Drawings topic.

GeomToShapesEsri(<geom>) : <table>

Exactly like GeomToShapes  except that it decomposes geoms into shapes based on ESRI rules.  To decompose geoms into shapes using OGC rules use GeomToShapes.

GeomTouches(<geom>, <geom>, <tolerance>) : <value>

Given two geoms returns true if they touch. A tolerance of zero means automatic tolerance.   For the definition of touch see the Transform: Overlay topic.

GeomTriangulate(<geom>, <tolerance>) : <areas>

Triangulates objects.  Given lines, areas or multipoints, performs a Delaunay triangulation of the geom coordinates of an object to create triangles as area objects.   The borders of the triangles will be constrained by geom segments, that is, they will not cross segments within the geom.

GeomTriangulatePar(<geom>, <tolerance>. <config>) : <areas>

A parallelized form of the GeomTriangulate function that takes a parameter for the suggested number of threads as encoded in the JSON <config> string as "threads" and the minimum number of coordinates to allocate for a thread as "batch."  The threads default is 1 and the batch default is 1000. Use ThreadConfig to generate a JSON string with the desired number of threads to see how values such as "threads" and "batch" are encoded in the JSON string used for the <config> value.

GeomTriangulateLines(<geom>, <tolerance>) : <lines>

Triangulates objects.  Given lines, areas or multipoints, performs a Delaunay triangulation of the geom coordinates of an object to create triangles as line objects.   The borders of the triangles will be constrained by geom segments, that is, they will not cross segments within the geom.

GeomTriangulateLinesPar(<geom>, <tolerance>, <config>) : <lines>

A parallelized form of the GeomTriangulateLines function that takes a parameter for the suggested number of threads as encoded in the JSON <config> string as "threads" and the minimum number of coordinates to allocate for a thread as "batch."  The threads default is 1 and the batch default is 1000.  Use ThreadConfig to generate a JSON string with the desired number of threads to see how values such as "threads" and "batch" are encoded in the JSON string used for the <config> value.

GeomType(<geom>) : <value>

Returns the object type of a geom as a number:   3 - area ; 2 - line; 1 - point

GeomUnionAreas(<geom>) : <geom>

An aggregate function: Given a set of geoms that contain areas returns a geom giving their union as a single area.  Discards lines and points.   Any touching or overlapping areas are unioned into a single branch of the area while areas that do not touch or overlap will be separate branches.

 

See the Aggregates topic and also the Union Areas transform in the Transform Templates - Drawings topic.

GeomUnionAreasPair(<geom>, <geom>, <tolerance>) : <geom>

A pairwise version of GeomUnionAreas: given two geoms with areas returns a geom giving their union as a single area.   Discards lines and points.     Any touching or overlapping areas are unioned into a single branch of the area while areas that do not touch or overlap will be separate branches.   A tolerance of zero means automatic tolerance.   

 

See the Union Areas transform in the Transform Templates - Drawings topic.

GeomUnionRects(<valuex4>) : <valuex4>

An aggregate function: Given a set of x4 values where each x4 value defines diagonally opposite corners of a rectangle, return a single x4 value that gives the union of those rectangles, that is the common bounding box of those rectangles.   See the Aggregates topic.

GeomUnionRectsPair(<valuex4>, <valuex4>) : <valuex4>

A pairwise version of GeomUnionRects: given two x4 values where each x4 value defines diagonally opposite corners of a rectangle, return a single x4 value that gives the union of those rectangles, that is the common bounding box of those rectangles.

GeomVoronoi(<points>, <inflatex2>, <tolerance>) : <areas>

Given a set of points create a Voronoi diagram and return a geom with the Voronoi cells as area objects.  A tolerance of zero means automatic tolerance.    The x2 value for inflate gives the X, Y factors by which to increase the bounding box used for clipping infinite regions.   See the discussion in the Transform: Voronoi Diagrams topic.

GeomVoronoiPar(<points>, <inflatex2>, <tolerance>, <config>) : <lines>

A parallelized form of the GeomVoronoi function that takes a parameter for the suggested number of threads as encoded in the JSON <config> string as "threads" and the minimum number of coordinates to allocate for a thread as "batch."  The threads default is 1 and the batch default is 1000.  Use ThreadConfig to generate a JSON string with the desired number of threads to see how values such as "threads" and "batch" are encoded in the JSON string used for the <config> value.

GeomVoronoiLines(<points>, <inflatex2>, <tolerance>) : <lines>

Given a set of points create a Voronoi diagram and return a geom with the borders of Voronoi cells as line objects.  A tolerance of zero means automatic tolerance.    The x2 value for inflate gives the X, Y factors by which to increase the bounding box used for clipping infinite regions.   See the discussion in the Transform: Voronoi Diagrams topic.

GeomVoronoiLinesPar(<points>, <inflatex2>, <tolerance>, <config>) : <lines>

A parallelized form of the GeomVoronoiLines function that takes a parameter for the suggested number of threads as encoded in the JSON <config> string as "threads" and the minimum number of coordinates to allocate for a thread as "batch."  The threads default is 1 and the batch default is 1000.  Use ThreadConfig to generate a JSON string with the desired number of threads to see how values such as "threads" and "batch" are encoded in the JSON string used for the <config> value.

GeomVoronoiPoints(<points>, <inflatex2>, <tolerance>) : <points>

Given a set of points create a Voronoi diagram and return a geom with points at the intersections of borders of Voronoi cells as line objects.  A tolerance of zero means automatic tolerance.    The x2 value for inflate gives the X, Y factors by which to increase the bounding box used for clipping infinite regions.   See the discussion in the Transform: Voronoi Diagrams topic.

GeomVoronoiPointsPar(<points>, <inflatex2>, <tolerance>, <config>) : <points>

A parallelized form of the GeomVoronoiPoints function that takes a parameter for the suggested number of threads as encoded in the JSON <config> string as "threads" and the minimum number of coordinates to allocate for a thread as "batch."  The threads default is 1 and the batch default is 1000.  Use ThreadConfig to generate a JSON string with the desired number of threads to see how values such as "threads" and "batch" are encoded in the JSON string used for the <config> value.

GeomWithin(<geom>, <geom>, <distance>, <tolerance>) : <value>

Given two geoms and a distance value returns True if the geoms are closer to each other than the specified distance.  A tolerance of zero means automatic tolerance.  

GeomWkb(<geom>) : <wkb>

Provides a way to extract geometry from a Manifold geometry type, a geom, into a well known binary representation of the geometry, WKB.  Given a geom return a WKB binary representation for that object's geometry.

GeomWkt(<geom>) : <wkt>

Provides a way to extract geometry from a Manifold geometry type, a geom, into a well known binary representation of the geometry, WKT.  Given a geom return a WKT text representation for that object's geometry.

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

The hypotenuse function: Given x and y returns sqrt(x2 +y2).    See the Hypotenuse template in the Transform Templates - Numeric topic.

J0(<value>) : <value>

A Bessel function of the first kind: given a value x returns the result of the Bessel function J0(x).   See the Bessel J0 template in the Transform Templates - Numeric topic.

J1(<value>) : <value>

A Bessel function of the first kind: given a value x returns the result of the Bessel function J1(x).   See the Bessel J1 template in the Transform Templates - Numeric topic.

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.   See the Bessel Jn template in the Transform Templates - Numeric topic.

JoinAnd(<boolean>) : <boolean>

An aggregate function: Combine boolean values using the And operator and return a boolean value.

JoinBitAnd(<value>) : <value>

An aggregate function: Combine numeric values using the BitAnd operator and return a boolean value.

JoinBitOr(<value>) : <value>

An aggregate function: Combine numeric values using the BitOr operator and return a boolean value.

JoinBitXor(<value>) : <value>

An aggregate function: Combine numeric values using the BitXor operator and return a boolean value.

JoinOr(<boolean>) : <boolean>

An aggregate function: Combine boolean values using the Or operator and return a boolean value.

JoinXor(<boolean>) : <boolean>

An aggregate function: Combine boolean values using the Xor operator and return a boolean value.

Last(<value>) : <value>

An aggregate function: Returns the last value for the specified field.  Does not skip NULLs. Given that tables should be considered unordered, not a function of the most universal utility.   

 

SELECT Last([Product Name]) FROM [Products];

 

See the Aggregates topic.

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.

 

See the Log-gamma Function template in the Transform Templates - Numeric topic.

Log(<value>) : <value>

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

 

See the Logarithm Function template in the Transform Templates - Numeric topic.

Log10(<value>) : <value>

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

 

See the Base 10 Logarithm Function template in the Transform Templates - Numeric topic.

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.

 

See the Logarithm Function template in the Transform Templates - Numeric topic.

Log2(<value>) : <value>

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

 

See the Base 2 Logarithm Function template in the Transform Templates - Numeric topic.

Max(<value>) : <value>

An aggregate function:  Returns the maximum value for the specified field.  

 

SELECT Max([Population]) FROM [States Table];

 

See the Aggregates topic.

Median(<value>) : <value>

An aggregate function:  Returns the median value for the specified field.  

 

SELECT Median([Population]) FROM [States Table];

 

See the Aggregates topic.

Min(<value>) : <value>

An aggregate function:  Returns the minimum value for the specified field.  

 

SELECT Min([Population]) FROM [States Table];

 

See the Aggregates topic.

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.  See the Reciprocal Cube Root template in the Transform Templates - Numeric topic.

Round(<value>) : <value>

Returns the input value rounded up or down to the nearest integer.   See the Round template in the Transform Templates - Numeric topic.

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

Returns the input value rounded up or down to the specified number of decimal positions.   See the Round to Decimals template in the Transform Templates - Numeric topic.

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.  See the Reciprocal Square Root template in the Transform Templates - Numeric topic.

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

Takes a table, a selection set for that 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).  The selection set for a table can be seen in the Info pane.

 

? CALL Selection([Table], 3, True)

 

Expert Commentary:

 

We liked the easiness with which we could not only read, but change the selected drawing objects in Manifold System 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:

 

--SQL

UPDATE CALL Selection([t],

  SelectionForWindow('t', ''), TRUE) SET [f]=[f]+1;

 

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

EXECUTE CALL Selection([t], SelectionForWindow('t', ''), 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], ...).

SelectionForWindow(<windowName>, <layerName>) : <selectionSet>

Takes the name of a component window and layer and returns the selection set for that window.  The layer name should be an empty string for windows other than map windows.   

 

In the example below the Provinces drawing is open in a window and also participates as a layer in an open Mexico Map window.   The selections, of course, can be different for different windows.

 

? SelectionForWindow('Provinces', '')

 

and

 

? SelectionForWindow('Mexico Map', 'Provinces')

 

SelectionIsEmpty(<selectionSet>) : <value>

Given a selection set returns True if it does not contain any selection keys.

SelectionIsInverted(<selectionSet>) : <value>

Given a selection set returns True if it lists selected records and False if it lists unselected records.

SelectionKeys(<table>, <selectionSet>) : <table>

Given a table and a selection set returns a table of key field values for selected records.

 

? CALL SelectionKeys([Provinces Table], 7)

 

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.   See the Sine template in the Transform Templates - Numeric topic.

Sinh(<radians>) : <value>

Given a value in radians return the hyperbolic sine of   the value.   See the Hyperbolic Sine template in the Transform Templates - Numeric topic.

Sqrt(<value>) : <value>

Given a value returns the square root of the value.  See the Square Root template in the Transform Templates - Numeric topic.

StDev(<value>) : <value>

An aggregate function:  Returns the standard deviation of values found in this field in aggregated records, that is, the square root of the sample variance.

 

SELECT StDev([Population])

  FROM [Provinces Table];

 

See the Aggregates topic and the Transform Options topic.

StDevPop(<value>) : <value>

An aggregate function:  Returns the standard deviation of values found in this field in the entire table, that is, the square root of the population variance.   The method name is a mnemonic for the standard deviation for an entire population.

 

SELECT StDevPop([Population])

  FROM [Provinces Table];

 

See the Aggregates topic and the Transform Options topic.

StringBinaryBase64(<string) : <value>

Given a base64-encoded string that encodes a binary value, returns the binary value.   See the Parse Base64 String template in the Transform Templates - Binary topic.

StringBinaryHex(<string>) : <value>

Given a hexadecimal-encoded string that encodes a binary value, returns the binary value.   See the Parse Hex String template in the Transform Templates - Binary topic.

StringCompareCollate(<string>, <string>, <collation>) : <value>

Same as Compare but compares two strings using the specified collation.  

 

Returns -1 if the first string is less than the second string, 1 if the first string is greater than the second string and 0 if the first and second strings are the same.   

 

In the examples that follow 928 is the collation code for en-US (US English) that is case sensitive.   930 is the collation code for en-US where case does not matter.

 

? StringCompareCollate('John','john', 928)

 

returns 1, meaning the first string is greater than the second string.  

 

? StringCompareCollate('John','john', 930)

 

returns 0, meaning the two strings are the same (as they are if case does not matter).

 

Collations are rules that specify how to compare text values.  The Collate function generates a number for a specific rule that can be used as the <collation> argument in functions that use collations.  See the COLLATE topic.

StringCompareKeyCollate(<string>, <collation>) : <value>

Given a string and a specified collation returns a binary representation of the text using the specified collation.  The produced key can be compared to keys for other strings with the same collation, and such comparisons will yield the same result as if the comparison was done using StringCompareCollate(). However, comparing binary keys is usually much faster than comparing strings using a given collation.

 

Collations are rules that specify how to compare text values.  The Collate function generates a number for a specific rule that can be used as the <collation> argument in functions that use collations.  See the COLLATE topic.

StringContains(<string>, <substring>) : <boolean>

Given a string and a substring returns True if the string contains the substring using case-sensitive comparison.

 

? StringContains('Long John Silver', 'john')

 

returns 0 for False,  because when case matters john does not occur in the first string.

 

? StringContains('Long John Silver', 'John')

 

returns 1 for True.

StringContainsCollate(<string>, <substring>, <collation>) : <boolean>

Given a string, a substring and a collation returns True if the string contains the substring using the rules of the specified collation.   

 

In the examples that follow 928 is the collation code for en-US (US English) that is case sensitive.   930 is the collation code for en-US where case does not matter.

 

? StringContainsCollate('Long John Silver', 'john', 928)

 

returns 0 for False,  because if case matters john does not occur in the first string.

 

? StringContainsCollate('Long John Silver', 'john', 930)

 

returns 1 for True,  because if case does not matter john occurs as the second word in the first string.

 

Collations are rules that specify how to compare text values.  The Collate function generates a number for a specific rule that can be used as the <collation> argument in functions that use collations.  See the COLLATE topic.

StringDecrypt(<encrypted>, <key>) : <string>

Given an encrypted string produced by StringEncrypt and the key used for encryption,  returns the decrypted original string.  See the Decrypt template in the Transform Templates - Text topic.

StringEncrypt(<string>, <key>) : <encrypted>

Given a string and a second string to use as an encryption key, utilizes an AES256 encryption mechanism to return an encrypted string.    The encrypted string may later be decrypted using StringEncrypt.   See the Encrypt template in the Transform Templates - Text topic.

StringEndsWith(<string>, <substring>) : <boolean>

Given a string and a substring returns True if the string ends with the substring using case-sensitive comparison. See the StringContains function for similar examples.

 

See the Text Ends with  template in the Transform Templates - Boolean topic.

StringEndsWithCollate(<string>, <substring>, <collation>) : <boolean>

Given a string, a substring and a collation returns True if the string ends with the substring using the rules of the specified collation.    See the StringContainsCollate function for similar examples.

 

See the Text Ends with, Intl  template in the Transform Templates - Boolean topic.

 

Collations are rules that specify how to compare text values.  The Collate function generates a number for a specific rule that can be used as the <collation> argument in functions that use collations.  See the COLLATE topic.

StringEscape(<string>, <escape>) : <string>

Used to create strings that require character escaping to get around syntax rules for how some characters have special meaning in the syntax of functions or queries.   Given a first string and a second, escape string that specifies how a designated escape character is to be used to escape other characters, returns a string where the characters in the first string have been escaped as specified by the escape string.

 

? StringEscape('abc \'def\' ghi', '\\\'')

 

Returns:

 

abc \'def\' ghi

 

See the discussion in the Transform: Escape Templates topic.  See the Escape template in the Transform Templates - Text topic.

StringEscapeEncode(<string>, <escape>, <escapeUnprintable>) : <string>

 

StringEscapeJson(<string>) : <string>

 

StringFind(<string>, <substring>) : <position>

Given a string and a substring returns the position, beginning with 0,  in the string of the first character of the substring if the string contains the substring using case-sensitive comparison.   If the string does not contain the substring return -1.

 

See the Text Position template in the Transform Templates - Numeric topic.

StringFindCollate(<string>, <substring>, <collation>) : <position>

Given a string, a substring and a collation returns the position, beginning with 0,  in the string of the first character of the substring if the string contains the substring using the rules of the specified collation.   If the string does not contain the substring return -1.

 

In the examples that follow 928 is the collation code for en-US (US English) that is case sensitive.   930 is the collation code for en-US where case does not matter.

 

? StringFindCollate('Long John Silver','john', 928)

 

returns -1 because if case matters john does not occur in the first string.

 

? StringFindCollate('Long John Silver','john', 930)

 

returns 5 because if case does not matter the substring john begins at the sixth character in the first string.  See the Text Position, Intl template in the Transform Templates - Numeric topic.

 

Collations are rules that specify how to compare text values.  The Collate function generates a number for a specific rule that can be used as the <collation> argument in functions that use collations.  See the COLLATE topic.  

StringFindNth(<string>, <substring>, <occurrence>) : <position>

Given a string, a substring and an <occurrence> number, n, (zero based counting, with the first <occurrence> being 0, the second being 1 and so on), returns the number of the character position in the text, also zero based, where the substring matches the nth time using case-sensitive comparison.   If not found at the specified nth position return -1.

 

See the Text Position, Nth template in the Transform Templates - Numeric topic.

StringFindNthCollate(<string>, <substring>, <occurrence>, <collation>) : <position>

Given a string, a substring and an <occurrence> number, n, (zero based counting, with the first <occurrence> being 0, the second being 1 and so on) and a collation, returns the number of the character position in the text, also zero based, where the substring matches the nth time using the rules of the specified collation.   If not found at the specified nth position return -1.

 

In the examples that follow 930 is the collation code for en-US (US English) that is not case sensitive.

 

? StringFindNthCollate('Long John Silver','L', 1, 930)

 

returns 12 because if case does not matter the second instance (indicated by 1 in zero based counting) of the substring 'L' occurs at the thirteenth character (indicated by 12 in zero based counting).

 

? StringFindNthCollate('Long John Silver','L', 2, 930)

 

returns -1 because there is no third instance of the substring 'L'.   

 

See the Text Position, Nth template in the Transform Templates - Numeric topic.

 

Collations are rules that specify how to compare text values.  The Collate function generates a number for a specific rule that can be used as the <collation> argument in functions that use collations.  See the COLLATE topic.

StringGmlCoordSystem(<gml>) : <system>

 

Given a GML string return a string reporting the coordinate system specified within that GML string.  See the Parse GML Coordinate System template in the Transform Templates - Text topic.

StringGmlGeom(<gml>) : <geom>

Convert GML geometry into a Manifold geom:  Given a GML string return a geom that contains the geometry specified in the GML string.

StringJoinTokens(<value>, <separator>) : <value>

An aggregate function:  Given string values and a separator string, returns a single string where the values are separated by the separator

 

SELECT StringJoinTokens([First Name], ' -x- ')

  FROM [Employees];

 

Using the Nwind sample database, the above query results in a single record with a result field of:

 

Nancy -x- Andrew -x- Janet -x- Margaret -x- Steven -x- Michael -x- Robert -x- Laura -x- Anne -x- Albert -x- Tim -x- Caroline -x- Justin -x- Xavier -x- Laurent

 

See the Aggregates topic and the Transform Options topic.

StringJsonBoolean(<json>, <name>, <strict>) : <value>

Extract a boolean value from a named JSON boolean entity.  Given a JSON string find the specified named entity if it exists and if it has a boolean value return that value.  If the named entity does not exist or if it has not been assigned a boolean value, return NULL.   The <strict> option when True requires a boolean value and rejects string representations such as "true".   When False, any other representations for the boolean value, such as string representations, are accepted.  

 

In the following examples a JSON string has two named entities that are intended as booleans: roads and canals.  In the case of the roads entity the boolean value of true  is specified using a boolean, that is just the word true or false not enclosed in quotes.   In the case of the canals entity the boolean value of false is specified using a string, that is, text enclosed in double quote " characters.

 

? StringJsonBoolean('{ "roads": true, "canals": "false" }', 'roads', true)

 

When the <strict> option is true, the above expression returns:

 

boolean: 1

 

? StringJsonBoolean('{ "roads": true, "canals": "false" }', 'canals', true)

 

When the <strict> option is true, the above expression returns:

 

boolean: <NULL>

 

The NULL was returned because a strict requirement that rejects any non-boolean representations leaves the canals entity with no value.   The function only returns a boolean value if the named entity exists and also has a boolean value.

 

? StringJsonBoolean('{ "roads": true, "canals": "false" }', 'roads', true)

 

When the <strict> option is false, the above expression returns:

 

boolean: 1

 

? StringJsonBoolean('{ "roads": true, "canals": "false" }', 'canals', true)

 

When the <strict> option is false, the above expression returns:

 

boolean: 0

 

See the JSON Boolean template in the Transform Templates - Boolean topic.

StringJsonGeoCoordSystem(<json>) : <system>

Extract the coordinate system from a GeoJSON string. Given a GeoJSON  string return a string reporting the coordinate system specified within that GeoJSON  string.  

 

See the Parse GeoJSON Coordinate System template in the Transform Templates - Text topic.

StringJsonGeoGeom(<json>) : <geom>

Convert GeoJSON geometry into a Manifold geom:  Given a GeoJSON string return a geom that contains the geometry specified in the GeoJSON string.

StringJsonNumber(<json>, <name>, <strict>) : <value>

Extract a numeric value from a named JSON boolean entity.  Given a JSON string find the specified named entity if it exists and if it has a numeric value return that value.  If the named entity does not exist or if it has not been assigned a numeric value, return NULL.   The <strict> option when True requires a numeric value and rejects string representations such as "true".   When False, any other representations for the numeric value, such as string representations, are accepted.  

 

See the examples for the StringJsonBoolean function for use of <strict> to require a specific type.

 

See the JSON Number template in the Transform Templates - Numeric topic.

StringJsonObject(<json>, <name>) : <value>

Extract an object as a string value from a named JSON boolean entity.  Given a JSON string find the specified named entity if it exists and if it has an object as a string value return that value.  If the named entity does not exist or if it has not been assigned a string value, return NULL.   

 

See the JSON Object template in the Transform Templates - Text topic.

StringJsonValue(<json>, <name>, <strict>) : <value>

Extract a string value from a named JSON boolean entity.  Given a JSON string find the specified named entity if it exists and if it has a string value return that value.  If the named entity does not exist or if it has not been assigned a string value, return NULL.   The <strict> option when True requires a string value and rejects any other representations.  When False, any other representations for the string value are accepted.  

 

See the examples for the StringJsonBoolean function for use of <strict> to require a specific type.

 

See the JSON String template in the Transform Templates - Text topic.

StringJsonArray(<json>, <name>) : <value>

Extract an array element as a string value from a named JSON boolean entity.  Given a JSON string find the specified named entity if it exists and if it has an array element return that array as a string value.  If the named entity does not exist or if it has not been assigned an array element, return NULL.  

 

The string returned could be parsed with the StringToJsonArrayValues function.

 

See the JSON Array template in the Transform Templates - Text topic.

StringLength(<string>) : <length>

Given a string returns the number of characters in the string.

 

? StringLength('Manifold')

 

StringPadEnd(<string>, <length>, <pad>) : <string>

Given a string, a desired length in number of characters and a string to use as padding, return a string truncated or padded at the end to the desired length as follows:

 

  • If the desired length is less than the length of the original string, truncate the original string (cutting characters off the end) to the desired length and return.
  • If the desired length is greater than the length of the original string, add characters from the padding string to the end of the original string, repeating the padding string as necessary, to fill out the desired length.

 

? StringPadEnd('Sonora', 4, 'Radian')

 

Returns:

 

Sono

 

Example:

 

? StringPadEnd('Sonora', 20, 'Radian')

 

Returns:

 

SonoraRadianRadianRa

 

Example:

 

? StringPadEnd('Sonora', 20, '.')

 

Returns:

 

Sonora..............

 

See the Pad End template in the Transform Templates - Text topic.

StringPadStart(<string>, <length>, <pad>) : <string>

Given a string, a desired length in number of characters and a string to use as padding, return a string truncated or padded at the beginning to the desired length as follows:

 

  • If the desired length is less than the length of the original string, truncate the original string (cutting characters off the end) to the desired length and return.
  • If the desired length is greater than the length of the original string, add characters from the padding string to the beginning of the original string, repeating the padding string as necessary, to fill out the desired length.

 

? StringPadStart('Sonora', 4, 'Radian')

 

Returns:

 

Sono

 

Example:

 

? StringPadStart('Sonora', 20, 'Radian')

 

Returns:

 

RadianRadianRaSonora

 

Example:

 

? StringPadStart('Sonora', 20, '.')

 

Returns:

 

..............Sonora

 

See the Pad Start template in the Transform Templates - Text topic.

StringRegexpCount(<string>, <regexp>, <flags>) : <matches>

Given a string, a  regular expression pattern and a flag to use case or to not use case, returns the number of matches to that regular expression found within the string.  The <flag> is not optional and must be either 'i' to ignore case or 'c' to use case.  

 

? StringRegexpCount('Netherlands', 'n', 'c')

 

Returns 1 for the single lower case n character in the string.

 

? StringRegexpCount('Netherlands', 'n', 'i')

 

Returns 2 since if case is ignored both the upper case N and the lower case n match the regular expression 'n'.

 

See the Number of Regexp Matches template in the Transform Templates - Numeric topic.

StringRegexpFind(<string>, <regexp>, <flags>) : <position>

Given a string, a  regular expression pattern and a flag to use case or to not use case, returns the index (zero based counting) to the position of the first occurrence of that regular expression found within the string and returns -1 if no match is found.  The <flag> is not optional and must be either 'i' to ignore case or 'c' to use case.  

 

? StringRegexpFind('Netherlands', 'n', 'c')

 

Returns 8 (meaning the 9th character) for the single lower case n character in the string which occurs as the ninth character.  

 

? StringRegexpFind('Netherlands', 'n', 'i')

 

Returns 0 (meaning the 1st character) since if case is ignored the upper case N at the first character position matches the regular expression 'n'.

 

See the Regexp Match Position template in the Transform Templates - Numeric topic.

StringRegexpFindNth(<string>, <regexp>, <occurrence>, <flags>) : <position>

Given a string, a  regular expression pattern, an occurrence number n (zero based counting) and a flag to use case or to not use case, returns the index (zero based counting) to the position of the nth occurrence of that regular expression found within the string and returns -1 if no match is found.  The <flag> is not optional and must be either 'i' to ignore case or 'c' to use case.  

 

? StringRegexpFindNth('Netherlands', 'n', 1, 'i')

 

Returns 8 (meaning the 9th character) since if case is ignored the lower case n at the ninth character position is the second occurrence (in zero based counting 1 means the 2nd occurrence) of a match to the regular expression 'n'.

 

See the Regexp Match Position, Nth template in the Transform Templates - Numeric topic.

StringRegexpMatches(<string>, <regexp>, <flags>) : <boolean>

Given a string, a  regular expression pattern and a flag to use case or to not use case, returns True if the string exactly matches the regular expression and 0 for False otherwise.  The <flag> is not optional and must be either 'i' to ignore case or 'c' to use case.   This function is used instead of "LIKEX" query operators found in some query engines.

 

? StringRegexpMatches('Netherlands', 'n.*', 'c')

 

Returns 0 (meaning False) since the string does not begin with a lower case n character.

 

? StringRegexpMatches('Netherlands', 'n.*', 'i')

 

Returns 1 (meaning True) since if case is ignored the upper case N at the first character position matches the regular expression 'n*', that is, text which begins with the letter n or N and then has zero or more of any characters following.

 

See the Text Matches Regexp  template in the Transform Templates - Boolean topic.

StringRegexpReplace(<string>, <regexp>, <target>, <flags>) : <string>

Given a string, a  regular expression pattern, a target replacement string and a flag to use case or to not use case, returns a string where all substrings of characters that match the regular expression have been replaced by the target replacement string.  If no matches are found returns the original string.  The <flag> is not optional and must be either 'i' to ignore case or 'c' to use case.  

 

? StringRegexpReplace('Netherlands', 'n', 'y', 'i')

 

Returns:

 

yetherlayds

 

Example:

 

? StringRegexpReplace('Netherlands', 'n', 'y', 'c')

 

Returns:

 

Netherlayds

 

Example:

 

? StringRegexpReplace('Netherlands', 'n.th.r', 'Upper', 'i')

 

Returns:

 

Upperlands

 

See the Replace Regexp, All  template in the Transform Templates - Text topic.

StringRegexpReplaceNth(<string>, <regexp>, <target>, <occurrence>, <flags>) : <string>

Given a string, a  regular expression pattern, an occurrence number n (zero based counting) and a flag to use case or to not use case,returns a string where the nth  substring of characters that match the regular expression has been replaced by the target replacement string.  If no matches are found returns the original string.  The <flag> is not optional and must be either 'i' to ignore case or 'c' to use case.  

 

? StringRegexpReplaceNth('Mississippi', 's', 't', 0, 'c')

 

Returns:

 

Mitsissippi

 

Example:

 

? StringRegexpReplaceNth('Mississippi', 'S', 't', 3, 'i')

 

Returns:

 

Missistippi

 

Example:

 

? StringRegexpReplaceNth('Mississippi', 'ss', 'tt', 1, 'c')

 

Returns:

 

Missittippi

 

See the Replace Regexp, Nth  template in the Transform Templates - Text topic.

StringRegexpSubstring(<string>, <regexp>, <flags>) : <string>

Given a string, a  regular expression pattern and a flag  to use case or to not use case, returns the first substring found that matches the regular expression within the string and returns an empty string if no match is found.  The <flag> is not optional and must be either 'i' to ignore case or 'c' to use case.  

 

? StringRegexpSubstring('Netherlands', 'n.*l', 'i')

 

Returns:

 

Netherl

 

See the Regexp Match template in the Transform Templates - Text topic.

StringRegexpSubstringNth(<string>, <regexp>, <occurrence>, <flags>) : <string>

Given a string, a  regular expression pattern, an occurrence number n (zero based counting) and a flag  to use case or to not use case, returns the nth substring found that matches the regular expression within the string and returns an empty string if no match is found.  The <flag> is not optional and must be either 'i' to ignore case or 'c' to use case.  

 

? StringRegexpSubstringNth('Netherlands', 'n..', 1, 'i')

 

Returns:

 

nds

 

Since the <occurrence> argument of 1 means the second occurrence (zero based counting) of the character n followed by two other characters, case insensitive.

 

See the Regexp Match, Nth template in the Transform Templates - Text topic.

StringReplace(<string>, <source>, <target>) : <string>

Given a string, a source string and a target string, return a modified string with all occurrences of the source string, using case-sensitive comparison, replaced by the target string.   If no instances of the source string are found, returns the original string.   

 

? StringReplace('Long John Silver','Long', 'Short')

 

See the Replace Text, All template in the Transform Templates - Text topic.

StringReplaceCollate(<string>, <source>, <target>, <collation>) : <string>

Given a string, a source string, a target string and a collation return a modified string with all occurrences of the source string, using the rules of the specified collation,  replaced by the target string.   If no instances of the source string are found, returns the original string.   

 

In the example that follows 930 is the collation code for en-US (US English) that is not case sensitive.

 

? StringReplaceCollate('Long John Silver','long', 'Short', 930)

 

The above expression returns:

 

nvarchar: Short John Silver

 

See the Replace Text, All, Intl template in the Transform Templates - Text topic.

 

Collations are rules that specify how to compare text values.  The Collate function generates a number for a specific rule that can be used as the <collation> argument in functions that use collations.  See the COLLATE topic.

StringReplaceNth(<string>, <source>, <target>, <occurrence>) : <string>

Given a string, a source string, a target string, and an <occurrence> number n (zero based counting, with the first <occurrence> being 0, the second being 1 and so on), return a modified string with the nth occurrence of the source string replaced by the target string.  If the source string is not found at the specified nth position returns the original string.

 

See the Replace Text, Nth template in the Transform Templates - Text topic.

StringReplaceNthCollate(<string>, <source>, <target>, <occurrence>, <collation>) : <string>

Given a string, a source string, a target string, an <occurrence> number, n, (zero based counting, with the first <occurrence> being 0, the second being 1 and so on) and a collation, return a modified string with the nth occurrence of the source string, using the rules of the specified collation,  replaced by the target string.  If the source string is not found at the specified nth position returns the original string.

 

In the examples that follow 930 is the collation code for en-US (US English) that is not case sensitive.

 

? StringReplaceNthCollate('Long John Silver','L', 'X', 1, 930)

 

The above expression returns:

 

nvarchar: Long John SiXver

 

? StringReplaceNthCollate('Long John Silver','L', 'X', 2, 930)

 

The above expression returns the original string since there is no third occurrence of the source string 'L'.

 

See the Replace Text, Nth, Intl template in the Transform Templates - Text topic.

 

Collations are rules that specify how to compare text values.  The Collate function generates a number for a specific rule that can be used as the <collation> argument in functions that use collations.  See the COLLATE topic.

StringReverse(<string>) : <string>

Given a string returns a string with the characters in reverse order.

 

? StringReverse('Netherlands')

 

Returns:

 

sdnalrehteN

 

See the Reverse Text template in the Transform Templates - Text topic.

StringSoundex(<string>) : <string>

Given a string returns the Soundex code for that string.  The Soundex algorithm generates short strings of alphanumeric codes based on how an English word sounds.   English words that are pronounced with similar sounds have the same Soundex codes.   

 

? StringSoundex('Sonora')

 

Returns:

 

S560

 

Example:

 

? StringSoundex('Sanara')

 

Returns:

 

S560

 

Soundex codes may be used to find matches to words entered by users who use inexact spelling.   For example, if we have a table of provinces in Mexico and a user enters Sanara we could use a query to find possible matches:

 

SELECT [NAME] FROM [Mexico Table]

  WHERE StringSoundex('Sanara') = StringSoundex([NAME]);

 

Returns a results table with one record, Sonora, because both of the words Sanara and Sonora have the same Soundex code as far as the English-based Soundex algorithm is concerned.

StringStartsWith(<string>, <substring>) : <boolean>

Given a string and a substring returns True if the string starts with the substring using case-sensitive comparison. See the StringContains function for similar examples.

 

See the Text Starts with  template in the Transform Templates - Boolean topic.

StringStartsWithCollate(<string>, <substring>, <collation>) : <boolean>

Given a string, a substring and a collation returns True if the string starts with the substring using the rules of the specified collation.    See the StringContainsCollate function for similar examples.

 

See the Text Starts with, Intl  template in the Transform Templates - Boolean topic.

 

Collations are rules that specify how to compare text values.  The Collate function generates a number for a specific rule that can be used as the <collation> argument in functions that use collations.  See the COLLATE topic.

StringSubstring(<string>, <start>) : <string>

Given a string and a start position (zero based counting) returns the substring from  the start position to the end of the original string.

 

? StringSubstring('Netherlands', 3)

 

Returns:

 

herlands

 

See the Text at End template in the Transform Templates - Text topic.

StringSubstringLen(<string>, <start>, <length>) : <string>

Given a string, start position (zero based counting), and a lengths (zero based counting) returns the substring of the specified length beginning at the start position  of the original string.

 

? StringSubstringLen('Netherlands', 3, 4)

 

Returns:

 

herl

 

To get the same effect as the Text at Start template in the Transform Templates - Text topic we specify a start position of 0, the first character, as well as the length of the desired substring:

 

? StringSubstringLen('Netherlands', 0, 4)

 

Returns:

 

Neth

 

StringToCharacters(<string>) : <table>

Split a string into a table of characters.  Given a string returns a table where each row contains a character in the string.

 

? Call StringToCharacters('Netherlands')

 

Returns:

 

il_stringtocharacters.png

 

StringToJsonObjectValues(<json>) : <table>

Split a JSON string into objects.   Given a JSON string returns a table where each row contains an object string for an object contained in that JSON string.

StringToJsonArrayValues(<json>) : <table>

Split a JSON string into array values.   Given a JSON string returns a table where each row contains an array value for an array contained in that JSON string.

StringToLowerCase(<string>) : <string>

Given a string returns the same string all in lower case.

 

See the Lower Case template in the Transform Templates - Text topic.

StringToLowerCaseCollate(<string>, <collation>) : <string>

Given a string and a collation returns the same string all in lower case using the rules of the specified collation.  Ignores <useCase> option in the encoded collation since the result is all lower case.

 

See the Lower Case, Intl template in the Transform Templates - Text topic.

 

Collations are rules that specify how to compare text values.  The Collate function generates a number for a specific rule that can be used as the <collation> argument in functions that use collations.  See the COLLATE topic.

StringToRegexpMatches(<string>, <regexp>, <flags>) : <table>

Given a string, a  regular expression pattern and a flag  to use case or to not use case, returns a table where each row contains a substring found that matches the regular expression within the string.

 

? CALL StringToRegexpMatches('Mississippi', 'i.', 'c')

 

Returns

 

il_stringtoregexpmatches.png

 

 

StringToTitleCase(<string>) : <string>

Requires Windows 7 or more recent Windows edition.  Given a string returns the same string in title case, converting the initial letter of each word into upper case and converting other characters into lower case, except that words which are already all upper case are left all upper case.

 

? StringToTitleCase('a walk in the park')

 

Returns:  A Walk In The Park

 

? StringToTitleCase('a WALK in the park')

 

Returns:  A WALK In The Park

 

? StringToTitleCase('a waLK iN tHe pARK')

 

Returns:  A Walk In The Park

 

? StringToTitleCase('A WALK IN THE PARK')

 

Returns:  A WALK IN THE PARK

 

To apply title case Manifold uses a Windows system facility, which reckons that a word which is in all capital letters is already in title case.  Strings that consist of all upper case characters therefore will not be modified. To transform such strings into a form where each word is lower case except for an initial capital letter, first  use the StringToLowerCase  function to transform the string into all lower case and then use the StringToTitleCase function.  

 

Contrary to most literary styles used in English, Windows also will capitalize articles, conjunctions and prepositions such as "of" and "the," so this function is best understood as Windows-style title case and not title case as understood in literary circles.

 

See the Title Case template in the Transform Templates - Text topic.

StringToTitleCaseCollate(<string>, <collation>) : <string>

Requires Windows 7 or more recent Windows edition.  Given a string and a collation returns the same string in title case using the rules of the specified collation.  Ignores <useCase> option in the encoded collation since the result is title case.  See the StringToTitleCase function for what is reckoned to be Windows-style title case.

 

See the Title Case, Intl template in the Transform Templates - Text topic.

 

Collations are rules that specify how to compare text values.  The Collate function generates a number for a specific rule that can be used as the <collation> argument in functions that use collations.  See the COLLATE topic.  

StringToTokens(<string>, <separator>) : <table>

Split a string into substrings bounded by a given separator character.   Given a string and a separator string containing one character (such as a space character), returns a table where each row contains the substrings of the original string that were bounded by the separator.  

 

? CALL StringToTokens('Long John Silver', ' ')

 

Returns:

 

il_stringtotokens.png

 

StringToUpperCase(<string>) : <string>

Given a string returns the same string all in lower case.

 

See the Upper Case template in the Transform Templates - Text topic.

StringToUpperCaseCollate(<string>, <collation>) : <string>

Given a string and a collation returns the same string all in upper case using the rules of the specified collation.  Ignores <useCase> option in the encoded collation since the result is all upper case.

 

See the Upper Case, Intl template in the Transform Templates - Text topic.

 

Collations are rules that specify how to compare text values.  The Collate function generates a number for a specific rule that can be used as the <collation> argument in functions that use collations.  See the COLLATE topic.

StringTranslate(<string>, <source>, <target>) : <string>

Given a string, a source string and a target string, returns a string where characters in the original string that are found in the  source string have been replaced on a character-by-character basis by the corresponding character in the target string.

 

? StringTranslate('Netherlands', 'abcde', 'xyz12')

 

Returns:

 

N2th2rlxn1s

 

The e characters were replaced by 2, the a character was replaced by x and the d character was replaced by 1.

 

See the Translate Characters template in the Transform Templates - Text topic.

StringTrim(<string>, <trim>) : <string>

Given a string and a trim string, removes all occurrences, including repeated occurrences, of the trim string at the beginning or the end of the string and returns the trimmed string.  Often used with a trim string of just one character, a ' ' space character, to trim extra spaces from the beginning and end of a string.  Often used to trim other text as well.

 

? StringTrim('"/info/products.shtml"', '"')

 

Returns:

 

/info/products.shtml

 

See the Trim template in the Transform Templates - Text topic.

StringTrimEnd(<string>, <trim>) : <string>

Given a string and a trim string, removes all occurrences, including repeated occurrences, of the trim string at the end of the string and returns the trimmed string.  Often used with a trim string of just one character, a ' ' space character, to trim extra spaces from the end of a string.  Often used to trim other text as well.

 

? StringTrimEnd('<a href="/info/products.shtml">', '">')

 

Returns:

 

<a href="/info/products.shtml

 

See the Trim End template in the Transform Templates - Text topic.

StringTrimStart(<string>, <trim>) : <string>

Given a string and a trim string, removes all occurrences, including repeated occurrences, of the trim string at the beginning of the string and returns the trimmed string.  Often used with a trim string of just one character, a ' ' space character, to trim extra spaces from the beginning of a string.  Often used to trim other text as well.

 

? StringTrimStart('<a href="/info/products.shtml', '<a href="')

 

Returns:

 

/info/products.shtml

 

Example, combining the StringTrimStart and StringTrimEnd functions (written as a single line in the Command Window):

 

? StringTrimStart(StringTrimEnd('<a href="/info/products.shtml">', '">'), '<a href="')

 

Returns:

 

/info/products.shtml

 

See the Trim Start template in the Transform Templates - Text topic.

StringUnescape(<string>, <escape>) : <string>

Remove escaping sequence from strings that use character escaping.   Given a first string and a second, escape string that specifies how a designated escape character sequence is to be used to escape other characters, returns a string where the characters in the first string have had character escaping,  escaped as specified by the escape string, removed.

 

? StringUnescape('abc \'def\' ghi', '\\\'')

 

Returns:

 

abc 'def' ghi

 

See the discussion in the Transform: Escape Templates topic.  See the Unescape template in the Transform Templates - Text topic.

StringUnescapeDecode(<string>, <escape>, <escapeUnprintable>) : <string>

 

StringUnescapeJson(<string>) : <string>

 

StringWktGeom(<wkt>) : <geom>

Convert WKT geometry into a Manifold geom:  Given a WKT string return a geom that contains the geometry specified in the WKT string.

 

? StringWktGeom('POINT(10.687 59.8876)')

 

The expression above returns a geom that contains a point.

Sum(<value>) : <value>

An aggregate function:  Given a value field returns the sum of the values in that field.  

 

SELECT Sum([Population]) FROM [Provinces Table];

 

See the Aggregates topic and the Transform Options topic.

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 dialog, 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()

 

Tan(<radians>) : <value>

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

 

See the Tangent template in the Transform Templates - Numeric topic.

Tanh(<radians>) : <value>

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

 

See the Hyperbolic Tangent template in the Transform Templates - Numeric topic.

Tgamma(<value>) : <value>

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

 

See the Gamma Function template in the Transform Templates - Numeric topic.

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.

 

? ThreadConfig(14)

 

Returns an nvarchar JSON string of

 

{ "threads": "14" }

 

TileAbs(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the absolute value of the input tile pixel values.   Absolute value leaves positive numbers unchanged and converts negative numbers into their positive equivalents.  

TileAcos(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the arc cosine (inverse cosine) of the input tile pixel values.

TileAcosh(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the hyperbolic arc cosine of the input tile pixel values.

TileAsin(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the arc sine (inverse sine) ofthe input tile pixel values.

TileAsinh(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the hyperbolic arc sine of the input tile pixel values.

TileAspect(<tile>, <radius>) : <tile>

Given a tile value and a radius returns a tile with pixel values set to the aspect in degrees of the surface implied by treating input tile pixel values as heights.   See the Aspect transform in the Transform Templates - Images topic.

TileAtan(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the arc tangent (inverse tangent) of the input tile pixel values.

TileAtan2(<y>, <x>) : <tile>

Given a tile value returns a tile with pixel values set to the arc tangent (inverse tangent) of the ratio between the DY field and the DX field specified.

TileAtanh(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the hyperbolic arc tangent of  the input tile pixel values.

TileBgrHcy(<tilebgr>) : <tilehcy>

Given a tile value with pixels in BGR color space returns a tile with pixel values in HCY color space.

TileBgrHsi(<tilebgr>) : <tilehsi>

Given a tile value with pixels in BGR color space returns a tile with pixel values in HSI color space.

TileBgrHsl(<tilebgr>) : <tilehsl>

Given a tile value with pixels in BGR color space returns a tile with pixel values in HSL color space.

TileBgrHsv(<tilebgr>) : <tilehsv>

Given a tile value with pixels in BGR color space returns a tile with pixel values in HSV color space.

TileBlur(<tile>, <radius>, <power>) : <tile>

Given a tile value, radius and power returns a tile with pixel values blurred over the radius and power specified, averaging pixel values over a square array of pixels.     The <radius> argument sets the number of pixels from the center pixel to the edge:  A radius of 1 results in a 3x3 pixel array. The <power> argument is the weight to give the center pixel: a weight of 1 means not to emphasize it.  A weight of 5 means to give the center pixel's value five times the value in averaging compared to other pixels in the array.   See the Blur transform in the Transform Templates - Images topic.

TileBlurDirection(<tile>, <radius>, <power>, <radians>) : <tile>

Given a tile value, radius, power and angle in radians returns a tile with pixel values blurred over the radius and power specified, averaging image values over a square array of pixels.   See the Direction Blur transform in the Transform Templates - Images topic.

TileBlurGaussian(<tile>, <radius>, <power>) : <tile>

Given a tile value, radius and power returns a tile with pixel values blurred over the radius and power specified using an algorithm that corrects for circular radius.     See the Gaussian Blur transform in the Transform Templates - Images topic.

TileCbrt(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to cubic root of the input tile pixel values.

TileCeil(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the rounded up integer value of the input tile pixel values.

TileCeilDecs(<tile>, <decimals>) : <tile>

Given a tile value and the desired number of decimals returns a tile with pixel values set to the integer value rounded up to the specified number of decimals of the input tile pixel values.

TileChannel(<tile>, <channel>) : <tile>

Given a tile value returns a tile with pixel values set to the values in the designated channel of the input tile.

TileChannelCount(<tile>) : <value>

Given a tile value returns the number of channels in the tile.  For example, a tile containing uint8x3 pixel values will have three channels.

TileChannels(<tile>, <value/valuexN>) : <tile>

Rearrange the component parts of a pixel value vector within a tile as directed by an index vector, similar to how the VectorValues function rearranges component parts of a vector value.  The VectorValues function rearranges the component parts of a single vector value.   The TileChannels function rearranges the component parts of all pixel value vectors in the tile.  

 

For example, if a tile is composed of uint8x3 vector values for each pixel where the three parts of the uint8x3 vector represent B, G and R channel values, applying TileChannels function to that tile using an index vector set of {2,1,0} will reshuffle the values so the uint8x3 vector will contain R, G and B channel values.   See the discussion for the VectorValues function.

TileChannelsConcat(<tile>, <tileConcat>) : <tile>

Given a tile and a second tile of the same dimensions, concatenates the second tile's channels onto the first tile, returning a tile with concatenated channels up to a total of four channels.  

 

For example, if the first tile called [BG Tile] has uint8x2 values, that is, a vector value with two parts, that represent B and G values for an image, and a second tile called [R Tile] has a single uint8 value for each pixel that represents R values, then:

 

TileChannelsConcat([BG Tile], [R Tile])

 

Returns a tile with pixel values as uint8x3 three part vectors that represent  B, G and R values.

TileCombine(<tile>, <tileBack>) : <tile>

Do not use: This is a service function subject to change that is exposed in the query builder as a placeholder for infrastructure in progress for future updates.  Given a tile and a background tile, returns a tile with pixels from the first tile where invisible pixels in the first tile have been replaced by corresponding pixels from the second tile.

TileCompare(<tile>, <tile>) : <tile>

TileCompare compares two tiles pixel by pixel and returns a tile of numeric values as follow: -1 where the pixel of the first tile is less than the pixel of the second tile, 0 where pixels are equal, and 1 where the pixel of the first tile is greater than the pixel of the second tile. The resulting tile can then be multiplied, used as a mask, or for other operations.

TileContrast(<tile>, <center>, <power>) : <tile>

Given a tile with single channel values, a center value and an exponent value adjust the contrast of the image.  Tones below the center value will be darkened exponentially and those above will be lightened exponentially using the power argument, to produce values for the returned tile that will give it altered contrast.

TileCos(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to  the input tile pixel values.

TileCosh(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to  the input tile pixel values.

TileCut(<image>, <valuex2>) : <tile>

Given an image and X and Y values as an x2 vector, returns the tile at the specified X and Y location in the image.

TileCutBorder(<image>, <valuex2>, <border>) : <tile>

Given an image,  X and Y values as an x2 vector, and a border size, returns the tile at the specified X and Y location in the image with the specified border.  The border size can be negative.

TileCutRect(<image>, <valuex4>) : <tile>

Given an image and the two X,Y corners of a rectangle (Xmin, Ymin and Xmax, Ymax) in the image as an x4 vector, returns the tile at the specified rectangle in the image.

TileEdges(<tile>, <radius>, <power>) : <tile>

Detect edges in an image by applying a moving, square matrix of pixels to detect changes in pixel values that indicate an edge, returning a tile where edges have higher pixel values.   The <radius> argument sets the number of pixels from the center pixel to the edge:  A radius of 1 results in a 3x3 pixel array. The <power> argument is the weight to give the center pixel: a weight of 1 means not to emphasize it.  A weight of 5 means to give the center pixel's value five times the value compared to other pixels in the array.   

TileEdgesDirection(<tile>, <radius>, <power>, <radians>) : <tile>

Detect edges aligned to specified direction (in radians) in an image by applying a moving, square matrix of pixels to detect changes in pixel values that indicate an edge, returning a tile where edges have higher pixel values.   The <radius> argument sets the number of pixels from the center pixel to the edge:  A radius of 1 results in a 3x3 pixel array. The <power> argument is the weight to give the center pixel: a weight of 1 means not to emphasize it.  A weight of 5 means to give the center pixel's value five times the value compared to other pixels in the array.   

TileErf(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the error function (also called erf(z) ) of the input tile pixel values.

TileErfc(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the complementary error function (also called erfc(z) ) of the input tile pixel values.

TileExp(<tile>) : <tile>

The exponential function: Given a tile value returns a tile with pixel values set to e to the power of the input tile pixel values.   See the Exponent template in the Transform Templates - Images topic.

TileExp10(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to 10 to the power of the input tile pixel values. See the Power of 10 template in the Transform Templates - Images topic.

TileExp2(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to 2 to the power of the input tile pixel values. See the Power of 2 template in the Transform Templates - Images topic.

TileExpm1(<tile>) : <tile>

Given a tile value with pixel values of x, returns a tile with pixel values set to e^x-1.

TileFill(<tile>, <value>) : <tile>

Given a tile value and a pixel value returns a tile with all pixel values set to the specified pixel value.

TileFloor(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the rounded down integer value of the input tile pixel values.   See the note below on the difference between Floor and Trunc functions.

TileFloorDecs(<tile>, <decimals>) : <tile>

Given a tile value and the desired number of decimals returns a tile with pixel values set to the integer value rounded down to the specified number of decimals of the input tile pixel values.    See the note below on the difference between Floor and Trunc functions.

TileFract(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the decimal fractional part of the input tile pixel values.

TileFractDecs(<tile>, <decimals>) : <tile>

Given a tile value and the desired number of decimals returns a tile with pixel values set to the decimal fractional part to the specified number of decimals of the input tile pixel values.

TileHcyBgr(<tilehcy>) : <tilebgr>

Given a tile value with pixels in HCY color space returns a tile with pixel values in BGR color space.

TileHsiBgr(<tilehsi>) : <tilebgr>

Given a tile value with pixels in HSI color space returns a tile with pixel values in BGR color space.

TileHslBgr(<tilehsl>) : <tilebgr>

Given a tile value with pixels in HSL color space returns a tile with pixel values in BGR color space.

TileHsvBgr(<tilehsv>) : <tilebgr>

Given a tile value with pixels in HSV color space returns a tile with pixel values in BGR color space.

TileHypot(<x>, <y>) : <tile>

The great and ancient Pythagorean theorem: The square root of the sum of the squares of the two sides of a right triangle.   Given two tile values or a tile value and a number, returns a tile with pixel values set to  the hypotenuse taking two input values  as DX and DY.

TileJ0(<tile>) : <tile>

A Bessel function of the first kind: Given a tile value returns a tile with pixel values set to the result of the Bessel function J0(x) using the input tile pixel values.  

 

See the Bessel J0 template in the Transform Templates - Numeric topic.

TileJ1(<tile>) : <tile>

A Bessel function of the first kind: Given a tile value returns a tile with pixel values set to the result of the Bessel function J1(x) using the input tile pixel values.  

 

See the Bessel J1 template in the Transform Templates - Numeric topic.

TileJn(<order>, <tile>) : <tile>

The nth Bessel function of the first kind: Given an order and a tile value returns a tile with pixel values set to the result of the Bessel function Jn(x) for order n using the input tile pixel values.  

 

See the Bessel Jn template in the Transform Templates - Numeric topic.

TileLgamma(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the log-gamma function, taking the natural logarithm of the gamma function, for the input tile pixel values.

TileLog(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the base e logarithm (also called the natural logarithm) of the input tile pixel values.

 

See the Logarithm Function template in the Transform Templates - Numeric topic.

TileLog10(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the base 10 logarithm (also called the common logarithm) of the input tile pixel values.

 

See the Base 10 Logarithm Function template in the Transform Templates - Numeric topic.

TileLog1p(<tile>) : <tile>

Given a tile value with pixel values x, returns a tile with pixel values set to Log(x+1) where Log is the base e logarithm (also called the natural logarithm) of the value.

 

See the Logarithm Function template in the Transform Templates - Numeric topic.

TileLog2(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the base 2 logarithm (also called the binary logarithm) of the input tile pixel values.

 

See the Base 2 Logarithm Function template in the Transform Templates - Numeric topic.

TileMakeNew(<cx>, <cy>, <value>) : <tile>

Given cx and xy extents and a value (a number, an xN vector or a boolean) returns a new tile.

TileMaskExtract(<tile>) : <mask>

Do not use: This is a service function subject to change that is exposed in the query builder as a placeholder for infrastructure in progress for future updates. Takes an arbitrary tile and creates a tile of boolean values, with true for visible pixels, false for invisible pixels.

TileMaskReplace(<tile>, <mask>) : <tile>

Do not use: This is a service function subject to change that is exposed in the query builder as a placeholder for infrastructure in progress for future updates. Takes an arbitrary tile and a tile of boolean values, and makes the pixels in the first tile visible or invisible according to the booleans. Whenever an invisible pixel is turned into visible, it gets a value of zero.

TileMax(<p>, <q>) : <tile>

Takes a tile and a value and returns the greater (maximum) value of the two for each pixel position.  

 

For example,  TileMax([Tile], -10) compares the value in the tile for each pixel to -10 and chooses which is the greater.  Used in a Transform dialog Expression that would have the effect of setting all pixel values less than -10 to -10.  

 

For example, if a tile consists of a single channel with values from 0 to 255 in each pixel, then TileMax([Tile], 150) will return a tile where each pixel has the value of the corresponding pixel in [Tile] except that if that value is less than 150 then the pixel will have a value of 150.

 

The value could be another tile, so that TileMax([Tile1], [Tile2]) for each pixel returns whichever is the greater value of Tile1 or Tile2.

TileMin(<p>, <q>) : <tile>

Takes a tile and a value and returns the lesser (minimum) value of the two for each pixel position.  

 

For example,  TileMin([Tile], -10) compares the value in the tile for each pixel to -10 and chooses which is the lesser.  Used in a Transform dialog Expression that would have the effect of setting all pixel values greater than -10 to -10.  

 

For example, if a tile consists of a single channel with values from 0 to 255 in each pixel, then TileMin([Tile], 150) will return a tile where each pixel has the value of the corresponding pixel in [Tile] except that if that value is more than 150 then the pixel will have a value of 150.

 

The value could be another tile, so that TileMin([Tile1], [Tile2]) for each pixel returns whichever is the lesser value of Tile1 or Tile2.

TileNoise(<tile>, <range>) : <tile>

Given a tile value and a range number, returns a tile with pixel values containing random noise values over the given range.

TilePow(<base>, <power>) : <tile>

Given a tile value returns a tile with pixel values set to the base (could be a tile or a numeric value) raised to the specified power value.   For example,  A power of 2 is the base value squared while a power of 3 is the base value cubed.  

 

See the Power template in the Transform Templates - Numeric topic.

TileRcbrt(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to reciprocal cube root of the input tile pixel values.   The reciprocal cube root is also known as the inverse cube root:  1 divided by the cube root of the input.

 

See the Reciprocal Cube Root template in the Transform Templates - Numeric topic.

TileRound(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the fractional part rounded up or down to the nearest integer of the input tile pixel values.

 

See the Round template in the Transform Templates - Numeric topic.

TileRoundDecs(<tile>, <decimals>) : <tile>

Given a tile value returns a tile with pixel values set to the fractional part, rounded up or down to the number of decimals specified, of the input tile pixel values.

 

See the Round to Decimals template in the Transform Templates - Numeric topic.

TileRsqrt(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to reciprocal square root of the input tile pixel values.   The reciprocal cube root is also known as the inverse square root:  1 divided by the square root of the input.

 

See the Reciprocal Square Root template in the Transform Templates - Numeric topic.

TileSharpen(<tile>, <radius>, <power>) : <tile>

Sharpen an image by applying a moving, square matrix of pixels to detect changes in pixel values that indicate transitions in visual appearance,  returning a tile where transitions have been enhanced with greater contrast.   The <radius> argument sets the number of pixels from the center pixel to the edge:  A radius of 1 results in a 3x3 pixel array. The <power> argument is the weight to give the center pixel: a weight of 1 means not to emphasize it.  A weight of 5 means to give the center pixel's value five times the value compared to other pixels in the array.   

TileSign(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to 1 or -1 when the input tile pixel values are positive or negative, respectively.

 

See the Sign template in the Transform Templates - Numeric topic.

TileSin(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the sine of the input tile pixel values.

 

See the Sine template in the Transform Templates - Numeric topic.

TileSinh(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the hyperbolic sine of the input tile pixel values.

 

See the Hyperbolic Sine template in the Transform Templates - Numeric topic.

TileSlope(<tile>, <radius>) : <tile>

Given a tile value and a radius returns a tile with pixel values set to the slope in degrees of the surface implied by treating input tile pixel values as heights.   

 

See the Slope transform in the Transform Templates - Images topic.

TileSqrt(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the square root of the input tile pixel values.

 

See the Square Root template in the Transform Templates - Numeric topic.

TileTan(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the tangent of the input tile pixel values.

 

See the Tangent template in the Transform Templates - Numeric topic.

TileTanh(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the hyperbolic tangent of the input tile pixel values.

 

See the Hyperbolic Tangent template in the Transform Templates - Numeric topic.

TileTgamma(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the gamma function of the input tile pixel values.

 

See the Gamma Function template in the Transform Templates - Numeric topic.

TileToValues(<tile>) : <table>

Given tile returns a table where each row is a pixel with fields x, y, and value. The returned value field is used for numbers or booleans.  Using TileToValues together with aggregates provides a more general way of getting average, count, max, min and sums for tiles than providing a specific function for each such operation.

TileToValuesX2(<tile>) : <table>

A variation of TileToValues for tiles containing x2 vector values.   Given tile returns a table where each row is a pixel with fields x, y, value, the value field being a float64x2 vector.

TileToValuesX3(<tile>) : <table>

A variation of TileToValues for tiles containing x3 vector values.   Given tile returns a table where each row is a pixel with fields x, y, value, the value field being a float64x3 vector.

TileToValuesX4(<tile>) : <table>

A variation of TileToValues for tiles containing x4 vector values.   Given tile returns a table where each row is a pixel with fields x, y, value, the value field being a float64x4 vector.

TileTrunc(<tile>) : <tile>

Given a tile value returns a tile with pixel values set to the truncated integer value of the input tile pixel values.     See the note below on the difference between Floor and Trunc functions.

TileTruncDecs(<tile>, <decimals>) : <tile>

Given a tile value and a number of decimal positions returns a tile with pixel values set to the truncated value, to the specified number of decimals, of the input tile pixel values.    See the note below on the difference between Floor and Trunc functions.

TileUpdatePyramids(<image>) : <table>

Updates intermediate levels for an image.  The image must use an rtree index on x-y-tile fields.   Given an image the function updates intermediate levels and returns the number of update tiles.

TileY0(<tile>) : <tile>

A Bessel function of the second kind: Given a tile value returns a tile with pixel values set to the result of the Bessel Function Y0(x) using the input tile pixel values.  

 

See the Bessel Y0 template in the Transform Templates - Numeric topic.

TileY1(<tile>) : <tile>

A Bessel function of the second kind: Given a tile value returns a tile with pixel values set to the result of the Bessel Function Y1(x) using the input tile pixel values.  

 

See the Bessel Y1 template in the Transform Templates - Numeric topic.

TileYn(<order>, <tile>) : <tile>

The nth Bessel function of the second kind: Given an order and a tile value returns a tile with pixel values set to the result of the Bessel Function Yn(x) for order n using the input tile pixel values.  

 

See the Bessel Yn template in the Transform Templates - Numeric topic.

Trunc(<value>) : <value>

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

 

? Trunc(3.1415926536)

 

Returns 3.

 

See the Truncate template in the Transform Templates - Numeric topic.     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 Truncate to Decimals template in the Transform Templates - Numeric topic.    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

 

See the Example: Add a UUID-based Index to a Table topic.

ValueCount(<table>) : <number>

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

ValueMax(<value>, <value>) : <value>

Can use any data type. Returns the maximum value of the two arguments.

ValueMin(<value>, <value>) : <value>

Can use any data type. Returns the minimum value of the two arguments.

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:

il_valuesequence.png

Var(<value>) : <value>

An aggregate function:  Given a value field returns sample variance.  

 

SELECT Var([Population]) FROM [States Table];

 

See the Aggregates topic and the Transform Options topic.

VarPop(<value>) : <value>

An aggregate function:  Given a value field returns population variance.  

 

SELECT VarPop([Population]) FROM [States Table];

 

See the Aggregates topic and the Transform Options topic.

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.

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

 

See the Bessel Y0 template in the Transform Templates - Numeric topic.

Y1(<value>) : <value>

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

 

See the Bessel Y1 template in the Transform Templates - Numeric topic.

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

 

See the Bessel Yn template in the Transform Templates - Numeric topic.

 

Notes

New functions - 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.

 

Normalization - GeomMakeRect is happy to accept the x1,y1 and x2, y2 in non-normalized order, that is, it doesn't matter to the function if we give it the lower left corner followed by the upper right corner so that x1 is less than x2 and y1 is less than y2.   But when GeomBox reports the x4 value for a bounding box it always reports the x4 value in a standard way, in normalized form so that x1 is less than or equal to x2 and y1 is less than or equal to y2.

 

GeomClip - If two geoms touch, their intersection is mathematically non-empty, but might have a lower dimension than the geoms and so be empty in terms of geom type; however, GeomClip must return a geom of the same type as its first argument. For example, two areas that share a corner touch, but intersecting them produces a point, which is not a valid area, and so GeomClip for that case will return NULL.

 

"Par" versions of functions such as GeomOverlayAdjacentPar will be deprecated -  Some functions, such as GeomOverlayAdjacent for example, exist both in single-threaded versions that do not provide an argument for the number of threads to use and also in a fully-parallel version, GeomOverlayAdjacentPar, that provides a <threads> argument to enable specifying how many threads to use.    Such "Par" versions are a temporary measure to get around Manifold's current inability to provide such functions with optional arguments.  Either the function has a <threads> argument or it does not, so two versions of the function are provide to allow one to not offer a <threads> specification and the other one to offer a <threads> specification.   The "Par" versions of functions will be deprecated when optional arguments for functions will appear in Manifold, allowing functions such as GeomOverlayAdjacent to have an optional <threads> argument to control parallel execution.

 

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.

 

String functions using regular expressions:   See the Regular Expressions topic for allowed syntax for the <regexp> argument.   The <flags> argument must be provided and is either 'i' to ignore case or is 'c' for case matters.

 

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!

 

Function Examples

Determine the CPU and GPGPU Resources in our System

In the Command Window we can enter the following commands:

 

? SystemCpuCount()

 

...will report the number of CPU cores in the system, reporting a value of 8 given hyper-threading on a four-core processor.

 

? SystemGpgpuCount()

 

...will report the number of GPGPU-capable devices in the system.   Note that this is not the total number of stream processing cores in those devices but just the number of devices.  

 

? CALL SystemGpgpus()

 

...will report a table with a record for each device giving the device index number and the name with CUDA level capability.  

 

Note the use of CALL in the third expression above and that none of the ? expressions above are terminated with a ; semicolon character.

 

Playing with a 3-4-5 (right angled) Triangle

SELECT

    [a], [b], [c],

    [point a], [point b], [point c],

    [line ab], GeomLength([line ab], 0) AS [length ab],

    [triangle abc], GeomArea([triangle abc], 0) AS [area abc]

FROM

    (SELECT

        [a], [b], [c],

        GeomMakePoint([a]) AS [point a],

        GeomMakePoint([b]) AS [point b],

        GeomMakePoint([c]) AS [point c],

        GeomMakeSegment([a], [b]) AS [line ab],

        GeomMakeTriangle([a], [b], [c]) AS [triangle abc]

    FROM

        (VALUES (

            VectorMakeX2(0.0, 0.0),

            VectorMakeX2(3.0, 4.0),

            VectorMakeX2(3.0, 0.0)

            )

            AS ([a], [b], [c])

        )

    )

;

 

Packing and Unpacking

SELECT

    [point a], [point b], [point c],

--    GeomMakeSegment([point a], [point b]) AS [line ab] -- not overloaded to take points

    GeomMakeSegment(GeomCenter([point a], 0), GeomCenter([point b], 0)) AS [line ab],

    GeomMakeTriangle(GeomCenter([point a], 0), GeomCenter([point b], 0), GeomCenter([point c], 0)) AS [triangle abc]

FROM

    (VALUES (

        GeomMakePoint(VectorMakeX2(0.0, 0.0)),

        GeomMakePoint(VectorMakeX2(3.0, 4.0)),

        GeomMakePoint(VectorMakeX2(3.0, 0.0))

        )

        AS ([point a], [point b], [point c])

    )

;

 

Create a Drawing and Table and Insert Geoms

Starting with a blank project we launch the Command Window for queries.  We first a table named t with a geom field named Geom and then we create a drawing named d from that table:

 

CREATE TABLE [t] (

  [Geom] GEOM,

  INDEX [Geom-x] RTREE ([Geom])

);

CREATE DRAWING [d] (

  PROPERTY 'FieldGeom' 'Geom',

  PROPERTY 'Table' '[t]'

);

 

Now we insert some geoms into t:

 

INSERT INTO [t] ([Geom]) VALUES

 (GeomMakePoint(VectorMakeX2(1, 1))),

 (GeomMakeSegment(VectorMakeX2(3, 2), VectorMakeX2(2, 3))),

 (GeomMakeRect(VectorMakeX4(5, 5, 8, 8)));

 

After we run the queries above we can open the drawing d to see the results.

 

eg_operfunc01_01.png

 

We have created a point, a line and a rectangular area.

 

Rectangles and x4 values

The GeomMakeRect function takes an x4 value and interprets the vector of four numeric values in order as X and Y coordinate values that define the X,Y locations of the two diagonally opposite corners of the rectangle, taking the four components of the x4 value in order as meaning the values of the as x1, y1 corner location and then the x2, y2 corner location.   Only two corners need be specified since in a rectangle the other two corners are given by reordering the given coordinates: x1, y2 and x2, y1.

 

Consider the rectangular area created by GeomMakeRect(VectorMakeX4(5, 5, 8, 8)) in the example above.    The VectorMakeX4 function creates an x4 vector with the components, in order, of 5, 5, 8 and 8.    

 

eg_operfunc01_02.png

 

The GeomMakeRect functions interprets these components, in order, as the coordinates specifying the locations of the x1, y1 and x2, y2 corners of the rectangle.

 

eg_operfunc01_03.png

 

 

The rectangular area is drawn with its lower left corner at the 5,5 XY location and the upper right corner at the 8,8 XY location.

 

eg_operfunc01_04.png

 

There is no need for us to explicitly specify the coordinates for the other two corners since those in a rectangle are obviously just permutations of the coordinates for the two diagonal corners we used to define the rectangle.

 

To dive further into the geometry, the GeomMakeRect function takes an x4 vector to be interpreted as a rectangle and creates an area with one branch and five coordinate locations in the corners of the rectangle.   There are five coordinate locations instead of only four to define the rectangular area because the last coordinate of the boundary of an area coincides with the first coordinate that starts that boundary.  

 

Geoms and x4 Values

Geoms and x4 vectors that are used or returned by functions that create geoms are closely related in that one can generate the implied other when processed by a function but they are different things.   A geom is a geometric specification of an object such as a rectangular area object.  An x4 value is a vector of four numbers.    Consider that the GeomBoundsRect function takes a geom and returns its bounding box as an x4 value. If we  create an x4 value, use GeomMakeRect to convert it to a geom, that is, a geom which is a rectangular area, and then we use GeomBoundsRect to compute the bounding box of that geom, we will get the same x4 value back that we started with, except that it will be normalized in that x1 will be less or equal than x2 and y1 will be less than or equal to y2.

 

Consider the following:

 

CREATE TABLE [t] (

  [Geom] GEOM,

  INDEX [Geom-x] RTREE ([Geom])

);

CREATE DRAWING [d] (

  PROPERTY 'FieldGeom' 'Geom',

  PROPERTY 'Table' '[t]'

);

 

We have first created a table and a drawing.  

 

INSERT INTO [t] ([Geom])

  VALUES (GeomMakeRect(VectorMakeX4(5, 6, 7, 8)));

 

And then we created one record in the table with a geom for a rectangular area in it.  If we open the drawing we see that indeed there is a single rectangular area in the drawing.

 

SELECT GeomBoundsRect([Geom])

  FROM [t];

 

And then we selected the bounding box of the geom in the table.   

 

 

eg_operfunc02_01.png

 

The Results tab in the command window reports the component values for an x4 vector as the result, not a geom.   That's the correct result since GeomBoundsRect returns an x4 vector and not a geom.   Note that the x4 vector is indeed the component values with which we started.

 

 

Query Optimization using R-tree Index

The Manifold query engine optimizes joins that use GeomWithin if one of the geom parameters to GeomWithin is a field with an associated r-tree index. GeomContains and similar functions also are optimized in this way.  Let's consider a sequence of queries written in the Command Window:

 

We first create a new table with a geom field:

 

CREATE TABLE a (geom GEOM);

 

We then insert some points in the form of a rectangular, 300 x 300 grid:

 

INSERT INTO a (geom)

SELECT GeomMakePoint(VectorMakeX2(x.value, y.value)) FROM

  CALL ValueSequence(0, 299, 1) AS x,

  CALL ValueSequence(0, 299, 1) AS y;

 

Next, we select points within fixed distance (15) of a fixed location (101, 102) and we note the time it takes to run the query in the Log window.

 

SELECT geom INTO t1 FROM a

  WHERE GeomWithin(geom, GeomMakePoint(VectorMakeX2(101, 102)), 15, 0);

 

Because there is no index the query above was run without optimization.   To run with optimization we add an r-tree index:

 

ALTER TABLE a (ADD INDEX [geom-x] RTREE (geom));

 

We now repeat the former query, selecting points within the same distance of the same fixed location, and once again we note the time it takes to run the query now that Manifold can use the r-tree index to optimize:

 

SELECT geom INTO t2 FROM a

  WHERE GeomWithin(geom, GeomMakePoint(VectorMakeX2(101, 102)), 15, 0);

 

With optimization using the r-tree index the query can run dramatically faster.  Optimization is so much faster that even on an older, underpowered, desktop machine the optimized run with an r-tree index takes essentially no time (about 7 milliseconds, that is, 0.007 seconds) whether or not the grid is 300 x 300 for 90000 points or 1000 x 1000 for 1000000 points, while the unoptimized query without an r-tree index can take half a second for the smaller array and five seconds for the larger array.  It takes time to add an r-tree index but if more than one such computation will be performed it is well worth it to add the r-tree index.

 

Queries for UUID and Binary Values

Create a new table and fill it with UUID values:

 

CREATE TABLE t (u UUID);

INSERT INTO t (u)

VALUES (NULL), (UuidNew()), (UuidNew()), (UuidNew());

 

Compare UUID values:

 

FUNCTION comparisons(p UUID, q UUID) TABLE AS

   (VALUES (p < q, p <= q, p = q, p <> q, p >= q, p > q)

   AS (lt, le, eq, neq, ge, gt)) END;

SELECT a.u, b.u, SPLIT CALL comparisons(a.u, b.u)

FROM t AS a, t AS b;

 

Compare the same values as both UUID and VARBINARY (note the differences):

 

FUNCTION comparisons(p UUID, q UUID) TABLE AS

  (VALUES (p < q, p <= q, p = q, p <> q, p >= q, p > q)

   AS (lt, le, eq, neq, ge, gt)) END;

FUNCTION comparisonsbin(p VARBINARY, q VARBINARY) TABLE AS

  (VALUES (p < q, p <= q, p = q, p <> q, p >= q, p > q)

   AS (blt, ble, beq, bneq, bge, bgt)) END;

SELECT a.u, b.u, SPLIT CALL comparisons(a.u, b.u),

  SPLIT CALL comparisonsbin(a.u, b.u) FROM t AS a, t AS b;

 

 

See Also

Tables

 

Add an Index to a Table

 

Functions

 

Queries

 

Regular Expressions

 

Command Window

 

Command Window - Query Builder

 

SQL Statements

 

SQL Operators

 

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.

 

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.

 

Example: Overlay Contained -  A frequent use of overlays is to sum the values of many points that fall within an area and to transfer that sum to a new field for an area.  In this example we take a drawing that has cities in the US with a population value for each city.  We use Overlay Contained  to sum the population of each city within a state and to transfer that sum to a total population for the state.

 

Example: Overlay Containing - One of the most common uses of overlays is to transfer fields from areas to points that are contained in those areas.    Tasks such as transferring a census block group number or zip code number from a drawing of areas to points that fall within each area are extremely common.   In this example we transfer the name of a French region  to the points that represent cities which fall within each region.

 

Example: Overlay Topology Intersect - In this example we use the Overlay Topology, Intersect template in the Transform dialog to trim a drawing of points so that all points which do not fall within areas in a second drawing are deleted.   The drawing of points we trim will become the US cities drawing that is used in the Example: Overlay Contained topic.

 

Example: Transfer Options and Merge Areas - Using the Merge Areas Transform dialog template, an exploration of the difference between using Copy and Sum for transfer options.

 

Example: Union Areas - Combine multiple area objects into a single area.   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: Learning to Union Areas in SQL from Edit Query - We learn how to write an SQL query that does a custom Union Areas operation by cutting and pasting from what the Edit Query button automatically generates.

 

Example: Use a Transform Dialog Expression to Create Buffers in a Drawing - Use the Expression tab of the Transform Dialog 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 Dialog Expression - Use the Expression tab of the Transform dialog 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: Smooth Lines with a Transform Dialog Expression - Use the Expression tab of the Transform dialog to make lines smoother in a drawing so that longer lines are smoothed more.  Includes examples of using the Edit Query button to show how different queries are created automatically depending on if we want to update a field or to add a new component.

 

Example: Transfer Options and Merge Areas - Using the Merge Areas Transform dialog template, an exploration of the difference between using Copy and Sum for transfer options.

 

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