Following are a variety of simple **SQL function** examples.

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.

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

)

)

;

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

)

;

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.

We have created a point, a line and a rectangular area.

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

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.

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.

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 vector**s 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.

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.

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.

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;

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