# Tile SQL Functions

This topic covers functions built into the Manifold query engine, called SQL functions or query functions,  that begin with Tile.   Functions that begin with Tile manipulate tiles and pixels within tiles, as used within raster images.  Tile functions provide a huge range of functions for working with raster data.  Tile functions do mathematics on tiles, provide comparisons, or create vector objects from tiles, for example, TileContourLines, or rasters from vectors, for example, TileInterpolateKriging.

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 functions in queries, please see the Queries topic.

• For information on declaring and calling functions, please see the Functions topic.

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

• SQL Functions - Introduction and list of topics by function category.

• Geom SQL Functions - Functions that begin with Geom and manipulate geometry, as used within drawings.

• String SQL Functions - Functions that begin with String and manipulate strings (text) within tables, including attributes of drawings and labels.

• Tile SQL Functions - This topic:  Functions that begin with Tile and manipulate tiles and pixels within tiles, as used within images (rasters).

• Other SQL Functions - Non-aggregate functions that do not begin with Coord, Geom, String or Tile.

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

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

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

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

## Constants

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

## Functions

Numeric functions on tiles operate on the first channel in the tile, ignoring all other channels, in the case of images that have more than one channel.

): Given a radius and center, produce a filter definition matrix to pass to TileFilter.    Applies a circle-shaped linear filter of specified radius.  The argument sets the number of pixels from the center pixel to the edge:  A radius of 1 results in a 3x3 pixel array. The
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. TileFilterDefCross(,
) : Given a radius and center, produce a filter definition matrix to pass to TileFilter.    Applies a cross-shaped linear filter of specified radius.  The argument sets the number of pixels from the center pixel to the edge:  A radius of 1 results in a 3x3 pixel array. The
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. TileFilterDefDiamond(,
: Given a radius and center, produce a filter definition matrix to pass to TileFilter.    Applies a diamond-shaped (mid-way between cross and circle) linear filter of specified radius.  The argument sets the number of pixels from the center pixel to the edge:  A radius of 1 results in a 3x3 pixel array. The
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. TileFilterDefDirection(,
, ) : Given a radius, center and angle in radians, produce a filter definition matrix to pass to TileFilter.  Produces a blur effect in the direction specified.  The argument sets the number of pixels from the center pixel to the edge:  A radius of 1 results in a 3x3 pixel array. The
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. TileFilterDefEdges(,
) : Given a radius and center, produce a filter definition matrix to pass to TileFilter.    Detects edges in an image using a Laplacian of Gaussian filter shape, normed to the center value.   The argument sets the number of pixels from the center pixel to the edge:  A radius of 1 results in a 3x3 pixel array. The
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. TileFilterDefEdgesDirection(,
, ) : Given a radius and center, produce a filter definition matrix to pass to TileFilter.   Detects edges in an image using a Laplacian of Gaussian filter shape, normed to the center value.   The argument sets the number of pixels from the center pixel to the edge:  A radius of 1 results in a 3x3 pixel array. The
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. TileFilterDefGaussian(,
) : Given a radius and center, produce a filter definition matrix to pass to TileFilter.   Produces a blur effect using an algorithm that corrects for circular radius and using a decaying exponent in the filter shape.   The argument sets the number of pixels from the center pixel to the edge:  A radius of 1 results in a 3x3 pixel array. The
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. TileFilterDefSharpen(,
) : Given a radius and center, produce a filter definition matrix to pass to TileFilter.  Sharpens an image by convolution with a moving, square matrix to detect changes in pixel values that indicate transitions in visual appearance,  returning a tile where transitions have been enhanced with greater contrast.   The argument sets the number of pixels from the center pixel to the edge:  A radius of 1 results in a 3x3 pixel array. The
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. TileFilterDefSquare(,
) : Given the radius and center, produce a filter definition matrix to pass to TileFilter.  Produces a blur effect.  The argument sets the number of pixels from the center pixel to the edge:  A radius of 1 results in a 3x3 pixel array. The

, ) : 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 pane 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(

## Notes

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

Operators - Many SQL Operators also work with tiles.   For example, the NOT, AND, OR, and XOR Boolean operators work with numeric tiles, where the tile contains values of 0 or any other number.  0 is interpreted as FALSE and any other value is TRUE.   The result is a numeric tile with 0 values for FALSE and 1 values for TRUE.  Comparison operators (<>, =, >=, >, <=, and <) also work with tiles, and with tiles and a number, producing a numeric tile with values of 0 or 1.  The BETWEEN operator also works with tiles and produces a numeric tile with values of 0 or 1.  See the SQL Operators  topic.

TileGeom functions in Release 9 compared to Release 8 -   Release 9 provides an upgrade for geometry (vector) to raster operations compared to Release 8.  Most of the considerations that follow involve fine details down to the level of individual pixels, to cover cases where very precise vector objects touch only part of a pixel:

• Each pixel overlaid by a vector object is only counted once, even for multipoints which fall onto the same pixel.  This is consistent with not counting pixels multiple times for lines and areas even if they have multiple coordinates which fall onto the same pixel.  This is the same as in 8 (save for multipoints which 8 does not support).

• As with 8, pixels overlaid by lines do not necessarily include pixels for line ends if point were placed at the line end.  Sometimes a neighboring pixel is a better match.

• As with 8, pixels overlaid by areas do not necessarily include pixels for the area boundary, if it would be converted to a line.  Horizontal shapes thinner than 1 pixel might be ignored.

• Pixels for areas are computed more accurately than in 8.

• Ultimately, as with 8, pixel shapes covered by a geom in 9 are an approximation that aims to be reasonably good without being slow.

• 9 runs faster than 8.  Quick performance numbers for TileGeomAvg in 8 and 9:

• Transferring heights from 10k x 10k pixels to 8142 small areas: 8 = 37.800 sec, 9 forced to use single thread = 7.835 sec, 9 with 8 threads = 1.769 sec, max difference in the result between 8 and 9: 9.3e-11 with a height range of 100-1500 (9 counts slightly more accurately).

• Transferring heights from 10k x 10k pixels to 1050 big areas: 8 = 74.050 sec, 9 forced to use single thread = 7.256 sec, 9 with 8 threads = 1.680 sec, max difference in the result between 8 and 9: 4.7e-11 with a height range of 100-1500.

TileFillMissingNearest in Release 9 compared to Interpolation functions in Release 8 - The TileFillMissingNearest function is similar to surface interpolation functions in 8, but there are two important differences:

• First, 9 uses a limited radius, not an unlimited radius as could be done with 8.  For example, Release 8 allowed unlimited radius only because surfaces in 8 were far smaller than the much bigger images 9 can handle.  9 also takes a more precise view of data and seeks to avoid questionable results:  if we want to fill in a missing data pixel in New York, but the nearest pixel exists in Africa, can we just use the value of a pixel in Africa because that happens to be the closest? Usually, searching for a closest known value has some common sense limits beyond which it is better to give up and take a different approach, such as perhaps using a constant or some average value if we absolutely must have missing pixel data filled in with something.

• Second, when there are multiple pixels at the same distance to the missing pixel, TileFillMissingNearest does not attempt to average them like 8 did and instead simply uses the first found value. This has the benefit of not introducing new values in cases where pixels stores some sort of unique codes, unlike heights, that cannot be averaged, or in cases where pixels store xN values where channels are connected to each other and per-channel averaging is undesirable.  Medians are an alternative, but computing a median is significantly more expensive  than computing an average or picking a random value, so a median is not provided by default.   If the community desires a median, that could be added, as could other options, such as returning all pixels closest to a user-defined function, or allowing specification of a metric that controls what is "closest," or other options.   For tips on recommending such options, see the Suggestions page.

Functions retaining the original tile type - Functions such as TileFill and several other functions retain the original tile type. What does this mean? Consider a simple example:

? TileJson(TileFill(TileMake(3, 3, CAST(1 AS UINT8)), 5))

The above creates a 3x3 tile of type UINT8 filled with the value 1 in each pixel.  It then refills the tile with the value 5 in each pixel.  Finally, it outputs the result as a JSON string. The result is what we would expect: [ 5, 5, 5, ... ]

Consider a variation:

? TileJson(TileFill(TileMake(3, 3, CAST(1 AS UINT8)), -1))

This creates the same tile as before but then tries to refill it with a value of -1 in each pixel.  The result is: [ 255, 255, 255 ... ].

What happened? Because TileFill keeps the original tile type, in this case an unsigned eight bit number, the value of -1 was converted to UINT8 and this produced the value 255, the unsigned eight bit equivalent of the number -1.

Other frequent conversions to keep in mind are from doubles to ints, where values are cut, and from numbers to xN values or vice versa, where values get padded or are cut.

Filters - For information on functions that use a <filter> argument or which produce a filter definition matrix, see the How Matrix Filters Work topic.

What are the built-in filters? -  Functions that are named beginning with TileFilterDef define a filter tile definition for subsequent use by TileFilter.  To see the filter matrix those functions generate we can use TileJson.  For example, to see what TileFilterDefSquare(1,1) generates using arguments that specify a radius of 1 to get a 3x3 matrix, and a center of 1, we can launch the Command Window and enter:

? TileJson(TileFilterDefSquare(1, 1))

Press the ! Run button and the Log pane of the Command Window reports:

> ? TileJson(TileFilterDefSquare(1, 1))

nvarchar: [

1, 1, 1,

1, 1, 1,

1, 1, 1

]

Some of the built-in filter definition functions generate much more complex filter matrix definitions than we would want to keyboard manually.  For example:

? TileJson(TileFilterDefEdges(1, 1))

Reports:

> ? TileJson(TileFilterDefEdges(1, 1))

nvarchar: [

-0.0009872784326934365, -0.03888148788384807, -0.0009872784326934365,

-0.03888148788384807, 1, -0.03888148788384807,

-0.0009872784326934365, -0.03888148788384807, -0.0009872784326934365

]

Most filter matrices published on various web sites use simpler forms, or provide fractions in the matrix filter cells instead of long decimal fractions.

GPGPU - Manifold automatically uses GPU parallelism (see the GPGPU topic) in functions where it makes sense to do so and when workflow is such that it is worth it to dispatch to GPU instead of simply using CPU parallelism.  In many cases both CPU parallelism and GPU parallelism will be used.  For example, all Kriging implementations (standard, median polish, and regression Kriging) use GPU, if available, to compute model parameters together with CPU parallelism in other parts of the function's operation.  GPU cards are so cheap that it doesn't make sense to try to guess when it pays to use GPGPU: simply install a GPU card, at least a cheapo GPU card.  Always.  Do not overthink it.  Just install an NVIDIA GPU card.

Why a GPU parallelism limit to radius/border of 8 or less? - Filter functions using a <radius> argument to set the size of the filter matrix applied typically are limited to a radius of 8 or less for GPGPU parallelism, switching to CPU parallelism at a radius of 9 or greater.   Calls to tile functions which use borders of 9 or more pixels likewise automatically dispatch to CPU parallelism.  In both cases, calls dispatched to CPU parallelism can freely intermix with calls dispatched to GPU parallelism.

Those limitations allows more flexible use of a greater range of GPU cards.  Some cards, perhaps older cards or lower cost cards, may have limited memory or earlier generation GPU chips, but they nonetheless can provide very many cores for GPGPU parallel computation.  Manifold makes it possible to use such cards effectively for GPGPU parallelism.

A radius of 8 implies a 17x17, 289-cell matrix, an absurdly huge and almost always an excessively large choice for matrix size.  A radius of 9 or above may indicate a conceptual error or workflow error.   At the same time, use of a radius of 9 or above requires so much GPU-accessible memory that such tasks fit into fewer and fewer cards, even if performance-reducing methods are adopted.   Given a choice between assuring a wide range of GPU cards will always work well, or restricting GPU choice to allow practices that are probably wrong in any event, Manifold chooses to support a wider range of GPU cards, placing the current switch to CPU parallelism at a radius or border of 9.  That may change as CUDA editions evolve.

Invisible pixels and filters - A difference in handling invisible pixels between Release 8 and Release 9 emerges when using functions such as TileFilterMax,  TileFilterMajor and similar matrix filter functions:  The surface evaluator tool in 8 was produces a visible pixel if at least one pixel in the vicinity is visible, which could make some invisible pixels visible.  In Release 9 invisible pixels stay invisible and visible pixels generally stay visible.  Visible pixels only turn invisible if non-zero filter coefficients all fall onto invisible pixels.  If the center value for a filter is non-zero, then visible pixels will always stay visible.

TileTrace functions - TileTrace functions track the number of different pixel values they encounter, and thus the number of different "bins" for which areas should be created, and stop if that number gets too big.  Currently, the limit is 20,000. Having too many different pixel values usually means that the image has to be preprocessed or that the function has to be called with a higher quantization factor to make the results usable.

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.

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

Tables

Add an Index to a Table

Functions

Queries

Regular Expressions

How Matrix Filters Work

Command Window

Command Window - Query Builder

Identifiers, Constants and Literals

SQL Statements

SQL Operators

SQL Functions

Aggregate SQL Functions

Coord SQL Functions

Geom SQL Functions

String SQL Functions

Other SQL Functions

Temporary Databases

EXECUTE

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

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

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

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

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

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

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

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

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

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

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

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

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

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