# Geom SQL Functions

This topic covers functions built into the Manifold query engine, called SQL functions or query functions,  that begin with Geom.   Functions that begin with Geom manipulate object geometry and geometric relationships.  For example, the GeomAdjacent function returns true if two objects are adjacent using the given tolerance, with a zero for tolerance specifying automatic tolerance.  GeomBearingGeo provides the geodetic (computed on the surface of the given ellipsoid) bearing of the given geometry.  For both raster and vector data.  Geometry functions can also manipulate geometry, such as the GeomOverlay series of functions, or functions like GeomToShapes.

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 - This topic:  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 - 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.

## Merge Compared to Union

Merge and Union operations are similar in that they combine objects.  The difference is that Merge simply combines coordinate lists as if each object combined is a new branch while Union utilizes the spatial implications of inside/outside the objects had before being merged.   This can be most clearly seen when areas are combined using Merge or Union.

Consider two overlapping area objects, as seen in the left-most illustration above.   A Merge operation simply combines the coordinate lists of the two objects as two branches.   The coordinate list of the second object treated as a branch results in a "hole" in the region of overlap with a touching "island" where the second branch does not overlap the first branch.   This is a consequence of how coordinate lists are used to define branched area objects.

A Union operation considers both original area objects in terms of the spatial definition of what is considered to be "inside" the original area boundary.  That "inside" is retained as if both areas were metal cutouts where the region of overlap between the metal cutouts is welded together in the region of overlap, to form a single "inside" within the new, combined cutout.  Branches are used in Union where area objects do not touch or overlap, so show such a combined, area object as having "islands."

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

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.

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.

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 conversions support curvilinear objects for all types supported by each format:  circular arcs, ellipsoidal arcs, splines for GML and circular arcs for WKB and WKT.  Geom functions in general first convert any curvilinear segments to straight segments, which can create unexpected results.  To avoid such problems, first convert curvilinear segments into equivalent constellations of straight line segments at whatever resolution is desired, using the transform template with the convert curves to lines operation option and the number of linear segments desired to approximate the curve in the Curve limit parameter.   See the Curved Segments discussion in the Drawings topic.

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

String SQL Functions

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