vsql_cube extension for MySQL

Use vsql_cube when several numeric columns belong together and you want to query them as a single value. A chain of AND conditions can test whether a row falls inside a range, but it cannot rank rows by distance. The cube type does both. It is a port of PostgreSQL’s cube extension. Most PostgreSQL function names carry over; the cube() constructors, the operators and their support functions (cube_contained, cube_overlap, distance_taxicab, distance_chebyshev) become the named functions below.


Maintainer	VillageSQL
Source and full documentation	villagesql/vsql-cube
License	GPL-2.0

Install

vsql_cube.veb is already in the server’s lib/veb/ directory if you installed VillageSQL with the install script, the Docker image, or a release tarball. Install it into the server with one statement:

INSTALL EXTENSION vsql_cube;

Confirm it is there:

SELECT EXTENSION_NAME
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'vsql_cube';

+----------------+
| EXTENSION_NAME |
+----------------+
| vsql_cube      |
+----------------+

To build it yourself, follow the build instructions in the repository.

What it adds

The cube type

Declare the column as `cube`(N), where N is the largest number of dimensions a value can have, from 1 to 100. cube is a MySQL reserved word, so it needs backticks, and the dimension count is required.

CREATE TABLE sensors (
    id      INT PRIMARY KEY,
    name    VARCHAR(32),
    reading `cube`(3)
);

Write values as a coordinate string. The column parses it:

INSERT INTO sensors VALUES
    (1, 'north', '12.5,40.1,3.0'),
    (2, 'south', '48.0,12.7,9.5'),
    (3, 'east',  '30.2,25.0,6.1');

Functions

Function	Returns	What it does
`cube_point_nd(x, y, ...)`	`cube`	Builds a point from its coordinates
`cube_box_nd(lower..., upper...)`	`cube`	Builds a box from the lower corner followed by the upper one
`cube_point(x)`, `cube_box(x, y)`	`cube`	Builds a one-dimensional point or box
`cube_from_string(s)`, `cube_to_string(c)`	`cube`, string	Converts between text and `cube`
`cube_dim(c)`	int	Number of dimensions
`cube_coord(c, n)`	real	Coordinate `n`, counting through the lower-left corner and then the upper-right. Out of range gives NULL
`cube_ll_coord(c, n)`, `cube_ur_coord(c, n)`	real	Coordinate `n` of the lower-left or upper-right corner
`cube_is_point(c)`	int	1 when the value is a point, 0 when it is a box
`cube_contains(a, b)`, `cube_contained_by(a, b)`	int	Whether one value encloses the other
`cube_overlaps(a, b)`	int	Whether two values share any space
`cube_distance(a, b)`	real	Euclidean distance
`cube_taxicab_distance(a, b)`	real	Manhattan distance
`cube_chebyshev_distance(a, b)`	real	Chebyshev distance
`cube_union(a, b)`, `cube_inter(a, b)`	`cube`	Smallest box holding both; their intersection
`cube_enlarge(c, r, n)`	`cube`	Grows a value by `r` in the first `n` dimensions
`cube_subset(c, d1, d2, ...)`	`cube`	A new value from the listed dimensions, counting from 1
`cube_add_dim(c, lower, upper)`	`cube`	Appends a dimension, as long as the result stays within the dimension count of the column `c` came from
`cube_agg(c)`	`cube`	Aggregate: the bounding box of every value in the group
`cube_scalar_agg(x)`	`cube`	Aggregate: the bounding range of a numeric column

Example

Find the two sensors nearest a target reading, then the sensors inside a region:

SELECT name, ROUND(cube_distance(reading, cube_point_nd(30, 25, 6)), 2) AS dist
FROM sensors
ORDER BY dist
LIMIT 2;

+-------+-------+
| name  | dist  |
+-------+-------+
| east  |  0.22 |
| south | 22.08 |
+-------+-------+

SELECT name FROM sensors
WHERE cube_contains(cube_box_nd(10, 10, 0, 35, 45, 7), reading) = 1;

+-------+
| name  |
+-------+
| north |
| east  |
+-------+

A cube argument gets its dimension count from a column, so a call works when one of its cube arguments is a column value or the result of a function that keeps a column’s dimension count, such as cube_union or cube_enlarge. A call with no such argument, as in cube_dim(cube_point_nd(1, 2)), fails with

ERROR 3219 (HY000): Cannot initialize function 'cube_dim': cannot determine     type parameters for vsql_cube.cube in argument 1

. For the same reason, insert a plain coordinate string rather than wrapping it in cube_point_nd().Each argument to cube_point_nd, cube_box_nd and cube_subset may also be a comma-separated string, and the lists join end to end. So cube_point_nd('1,2,3') and cube_point_nd(1, 2, 3) build the same point. Pass a string when the coordinates live in a column, which is the one case separate arguments cannot express.

Getting Started

Server Development

Using Extensions

Creating Extensions

Install

What it adds

The cube type

Functions

Example

See also

​Install

​What it adds

​The cube type

​Functions

​Example

​See also

Install

What it adds

The cube type

Functions

Example

See also