# Coord SQL Functions

This topic covers functions built into the Manifold query engine, called SQL functions or query functions,  that begin with Coord.   Functions that begin with Coord provide numerous utility functions for manipulating coordinates and coordinate systems, for example, converting geometry from one coordinate system into another, or for georeferencing.  For both raster and vector data.

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.

• Coord SQL Functions - This topic:  Functions that begin with Coord and manipulate coordinate systems.

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

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

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.

Performance of query functions when projecting images - For the standard projection method there are two ways to perform the projection with multiple threads:  either use CoordConvertTile in a SELECT with THREADS, or use CoordConvertTileSetPar.

• CoordConvertTileSetPar usually will be able to arbitrate threads better. If the result of the projection will be used as a temporary table in a join, the use of CoordConvertTileSetPar is thus preferable.
• However, if the result of the projection will simply be used to populate a persistent table, it is better to use CoordConvertTile because this will avoid creating a copy of the projected image in memory.

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

Division by zero - returns NULL.

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

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

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