> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# vsql_cube extension for MySQL

> The vsql_cube extension adds a cube column type to MySQL for storing points and boxes in up to 100 dimensions, with containment, overlap, and distance functions.

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. Where PostgreSQL has a
named function the name matches, and PostgreSQL's operators and array
constructors become the named functions below.

|                                   |                                                                 |
| --------------------------------- | --------------------------------------------------------------- |
| **Maintainer**                    | VillageSQL                                                      |
| **Source and full documentation** | [villagesql/vsql-cube](https://github.com/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:

```sql theme={null}
INSTALL EXTENSION vsql_cube;
```

Confirm it is there:

```sql theme={null}
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](https://github.com/villagesql/vsql-cube).

## What it adds

### The cube type

Declare the column as `` `cube`(N) ``, where `N` is the number of dimensions.
`cube` is a MySQL reserved word, so it needs backticks, and the dimension count
is required.

```sql theme={null}
CREATE TABLE sensors (
    id      INT PRIMARY KEY,
    name    VARCHAR(32),
    reading `cube`(3)
);
```

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

```sql theme={null}
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(coords)`                          | `cube`         | Builds a point from a comma-separated coordinate string                                                  |
| `cube_box_nd(lower, upper)`                      | `cube`         | Builds a box from two corner strings                                                                     |
| `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, dims)`                           | `cube`         | A new value from the listed dimensions                                                                   |
| `cube_add_dim(c, lower, upper)`                  | `cube`         | Appends a dimension                                                                                      |
| `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:

```sql theme={null}
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 |
+-------+-------+
```

```sql theme={null}
SELECT name FROM sensors
WHERE cube_contains(cube_box_nd('10,10,0', '35,45,7'), reading) = 1;
```

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

<Note>
  A `cube` argument gets its dimension count from the column it came from, so
  every call above passes a column value. Feeding one function's result
  straight into another, 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()`.
</Note>

## See also

* [Multi-dimensional range queries in MySQL](/docs/guides/cube-queries) — a longer walkthrough of containment, distance, and nearest-neighbor queries
* [Install extensions](/docs/mysql-9.7/stable/install) — how `INSTALL EXTENSION` works and where the server looks for a bundle
* [Available extensions](/docs/mysql-9.7/stable/extensions) — the full catalog
* [villagesql/vsql-cube](https://github.com/villagesql/vsql-cube) — source, build instructions, and the complete function reference