> ## 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. 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](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 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.

```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(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:

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

## See also

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


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.