SQL

This documentation covers SQL as implemented in Manifold's Radian® spatial database engine.  Radian is built into Manifold technology products such as Manifold® Release 9 GIS, the free Manifold Viewer, add-ins for other GIS packages like ESRI's ArcGIS Pro, and various OEM products.   Packages using Manifold technology share a common code base and a common set of documentation topics.  Improvements in Manifold infrastructure automatically provide updated functionality to all packages and products using that technology.

 

Manifold SQL is a very large, fully-articulated SQL, like SQL implementations in major DBMS packages.  Manifold SQL is automatically CPU and GPU parallel and includes hundreds of functions of interest in spatial data work.  Manifold DBMS and SQL technology was designed from the ground up to support the unique nature of GIS operations, which often involve vastly greater data per record than is typical of classic DBMS products like Oracle or SQL Server.   

 

Users familiar with SQL in Oracle, MySQL, SQL Server, PostgreSQL, or DB2 will be immediately productive in Manifold SQL after noting the usual slight syntax variations as with any SQL.  For example, use square brackets [ ] or back ticks ` ` to delimit identifiers like field and table names, and enclose text literals in single quote ' characters:

 

SELECT * FROM [Orders] WHERE [City] = 'London' ;

 

Future Manifold SQL editions will add double quote " characters as allowed identifier delimiters, as used by many DBMS products.  See the Identifiers, Constants and Literals topic.

 

Topics

Queries

Introductory overview

Command Window

The key interface for writing queries and scripts.  Provides a powerful query builder to assist in writing SQL.

Query Builder

A tab within the Command Window that facilities writing SQL with greatly reduced keyboarding.

Data Types

Manifold tables can store data in many data types.

Functions

The Manifold query engine supports declaring and calling functions, including script functions, functions in external files and compiled functions as .NET assemblies.  This topic discusses declaring and calling functions.   For notes on functions built into the Manifold query engine, see the SQL Functions topic.  

Aggregates

An aggregate function or clause takes a group, which is a subset of records from the original table, and from the values in that group computes one or more resulting values for the resulting record.   For example, the aggregate Sum(f) takes all values in the group, sums them and returns the sum.  Manifold provides many aggregate functions.

Identifiers, Constants and Literals

Identifiers are names of database things, like the names of tables, fields (columns) in a table, indexes, or constraints.  Constants provide useful shorthand ways of referring to frequently used data types, like LF for a line feed character, or PI for the number 3.1415...  Literals are specific values, typically enclosed in delimiters.  

SQL Statements

SQL statements are top level key word SQL commands like ALTER, CREATE, DROP, INSERT, SELECT, UPDATE and many others.

SQL Operators

Manifold includes many operators that can be used in queries, ranging from simple arithmetic operators like + and * to more sophisticated mathematical operators on vectors (tuples), to comparison operators like BETWEEN ... AND ..., to  logical operators like CASE and transformative operators like CAST.

SQL Functions

Manifold provides hundreds of functions for use in SQL queries.  

Aggregate SQL Functions

All aggregate functions.

Coord SQL Functions

Coordinate functions 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.

Geom SQL Functions

Geometry functions 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.

String SQL Functions

Manifold provides numerous functions to manipulate strings and string data, including conversions and extractions, all fully supporting collations.  Utility functions provide an extensive array of capabilities for manipulating popular text formats like JSON, and to manipulate strings in sophisticated ways using regular expressions.

Tile SQL Functions

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

Other SQL Functions

Non-aggregate functions that do not begin with Coord, Geom, String or Tile. General purpose functions, including numerous mathematical functions, functions to manipulate components, including virtual components, DateTime functions, forward and reverse geocoding, interactive selection, system functions such as reporting GPU types, table caching, manual thread configuration, value sequences, and vector numeric functions.

Temporary Databases

This topic provides a tutorial introduction to the use of temporary databases in a project.   The Manifold query engine supports temporary databases that live for the duration of the query. This is very useful for complex transforms and for many other things, such as internet map serving.  SQL statements such as CREATE ROOT <root>, USE ROOT <root> , CREATE DATASOURCE <name> AS ROOT, and DROP ROOT <root> work with temporary databases.   

Computed Fields and Constraints

Tables can contain fields defined by expressions that are computed on the fly, called computed fields.  Constraints are related to computed fields in that they also are expressions computed on the fly.  Primarily used for data integrity checks, constraints are  expressions which must evaluate to a boolean value of true for a table record to be inserted.   This topic discusses an SQL approach to computed fields.  For the Schema dialog approach, see the Computed Fields topic.

Directives, Statements, and  Clauses

 

$include$ Directive

Queries can include other queries via the $include$ preprocessor directive.

$manifold$ Directive

The $manifold$ preprocessor directive instructs the system to use the Manifold SQL engine regardless of the context of the query or command window.

BATCH and THREADS

The BATCH and THREADS statements provide the ability to control and to fine-tune the use of multiple threads when multiple CPU cores are available.  They work within SQL statements such as SELECT.   BATCH is used to fine-tune the performance of threads.

COLLATE

Collations are rules that specify how to compare text values in various international languages. The COLLATE command allows us to incorporate such rules into indexes for text values. Collations can be used with both Unicode and ANSI text values.  See the Collations topic to learn how collations are managed by Manifold.

COLLECT

COLLECT is an aggregate that returns a table, often used in conjunction with other aggregates.   In the simplest form,  COLLECT just returns a list of values from a table within a desired field.

 

Modern databases such as Oracle or PostgreSQL allow some control over the values seen by aggregates. For example, a user might be able to write a query that contains several Sum aggregates of the same field with each Sum computing a partial sum guided by a filter specified within the Sum, for example,  SUM(x FILTER ...).   Manifold provides a generic aggregate, COLLECT, that allows doing these and other operations.   

 

The COLLECT aggregate returns a table of values collected from a subgroup, typically guided by GROUP BY.  The results table usually is then fed into a function or other clause. This essentially allows creating custom aggregates and provides control over how values are used in other aggregates.

DISTINCT

A qualifier that filters SELECT results to omit records that contain duplicate data in the selected fields, leaving only one of each instance.   

EXECUTE

The EXECUTE and the EXECUTE WITH statements allows queries to call other queries.   

FETCH

The FETCH clause filters SELECT or COLLECT results to only the indicated number of records.   Also applies to EXECUTE and VALUES.

GROUP BY

The GROUP BY  clause can be used within SELECT statements that contain an aggregate function.   The GROUP BY clause creates subsets of records to feed into the aggregate function.  GROUP BY  is not some sort of sorting mechanism for results.  Instead, it is a helper clause that bundles and feeds groups of records into some other aggregate function.  Attempting to use GROUP BY without an aggregate function is an error.

JOIN Statements

A JOIN statement is a SELECT that combines records from two tables into a results table based on some condition, called a join condition.   The condition tested might be whether two field values are the same, so that records having the same values in that essential field can be combined from the two tables into one table.    Joins can be nested to combine records from more than two tables into the results table.  This topic discusses joins using SQL.  Join statements include use of JOIN, INNER JOIN, and outer joins like LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN, which can be named LEFT JOIN, RIGHT JOIN and FULL JOIN for short, CROSS JOIN, CROSS JOIN with a WHERE clause and self joins (a CROSS JOIN between a table and itself).   Joins can be much more sophisticated than simple joins typically illustrated.  A join condition can be almost any expression that does not contain a subquery.  It does not need to use the same field in both tables nor is it even required to reference the left or right tables.   To take advantage of the power of joins without using SQL we can launch the point and click Edit - Join dialog.

ORDER BY

The ORDER BY clause can be used within the EXECUTE, SELECT and VALUES statements to list records within a table within some desired order.   ORDER BY is an important clause because records in tables should never be assumed to be stored in some specific order: if we want to see them in some specific order we must always explicitly specify that ordering using the ORDER BY clause.

PRAGMA

PRAGMA passes options to the Manifold query engine to alter the operation of the query engine from default.  The word pragma is computer terminology for a directive, comment or message within the text code given to a compiler or other program that guides the operation of that program, for example, by instructing the program to operate differently from default settings.  In Manifold, PRAGMA is used mostly for debugging as well as for guiding the operation of the query engine.

SCRIPT

The SCRIPT statement allows SQL queries to include an inline script using any of the many scripting engines supported by Manifold.  An inline script can do anything.  

SPLIT Clause

This topic covers the SPLIT clause used in SQL queries.  For the Split interactive editing command used to split line and area objects with a cutting line, see the Split topic.

 

A SPLIT clause contains a query, called a split query.   The SPLIT clause works within a SELECT statement to create rows that are permutations between the results of the SPLIT query and the results of the main SELECT query without the SPLIT.  Adding permutations increases the number of rows in the results table of the SELECT as compared to how many rows there would be without the SPLIT clause.

 

The name split can be confusing because split means to break apart into pieces, which is not what SPLIT does.  SPLIT does not break apart anything.  Instead, it multiplies the number of rows by combining results from the split query in permutations with results from the SELECT.   A clearer, albeit clunkier, name might have been PERMUTE or FOR_EACH

TABLE

TABLE takes a table or a query expression and returns a table.  TABLE CALL executes a query function that is, a CALL to a function that returns a table.

UNION / EXCEPT / INTERSECT

UNION, EXCEPT, and INTERSECT are operators that operate similarly and are used between two queries to form Boolean combinations between the results of the two queries.    Given queries A and B, UNION returns all records returned by either A or B.   EXCEPT returns all records in A but not in B.  INTERSECT returns all records returned by both A and also by B.   Tables returned by UNION, EXCEPT and INTERSECT by default will have duplicate records eliminated.  Use ALL to retain duplicates in the results table.

VALUE

The VALUE statement declares global values.  It exists in two forms: the full form used to declare global values using an expression, and a short form that omits the expression.    Using VALUE to declare global values can dramatically simplify query text and make SQL way more legible.  VALUE is a great way to keep queries, even short queries, well organized, clear and succinct.

VALUES

VALUES returns a table populated with the specified values, which could be expressions.   Because VALUES is an SQL statement that can stand alone to form a query, it is useful as a general purpose way to evaluate <value> expressions, to do work in the Command window or within larger queries.

LIKE Operator

Checks if a string value matches a given pattern.  LIKE is easier than using regular expressions for simple pattern matching.  Use the various regular expression SQL functions listed in the Other SQL Functions topic for regular expression pattern matching.

Other SQL Clauses

This topic provides notes on various SQL clauses, typically those used with SELECT, EXECUTE and VALUES.  Clauses discussed include EXCEPT, HAVING, INTERSECT, OFFSET, ORDER, and UNION.

Editable Results Tables

Results tables produced by an SQL query in most database systems are usually read only, but in Manifold results tables from an SQL query often can be interactively edited, with changes made to values in the results table propagating back to the tables used to create the results table.  They can even have columns managed using the Layers Pane with Filters applied and other Manifold facilities working as well.

 

 

Trademarks - Radian, Manifold, and Polygon are registered trademarks of Manifold Software Limited.    流形,  多边形, and 弧度 (Manifold, Polygon, and Radian in Chinese) are trademarks of Manifold Software Limited.