SQL Example: Miscellaneous SQL Functions

Following are a variety of simple SQL function examples.

Determine the CPU and GPGPU Resources in our System

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.

 

Playing with a 3-4-5 (right angled) Triangle

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

        )

    )

;

 

Packing and Unpacking

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

    )

;

 

Create a Drawing and Table and Insert Geoms

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.

 

eg_operfunc01_01.png

 

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

 

Rectangles and x4 values

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.    

 

eg_operfunc01_02.png

 

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.

 

eg_operfunc01_03.png

 

 

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.

 

eg_operfunc01_04.png

 

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 Values

Geoms and x4 vectors 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.   

 

 

eg_operfunc02_01.png

 

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.

 

Query Optimization using R-tree Index

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.

 

Queries for UUID and Binary Values

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;

 

 

See Also

Tables

 

Indexes

 

Add an Index to a Table

 

Command Window

 

Queries

 

Schema

 

SQL Functions

 

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.