EUCLIDEAN_DISTANCE

Returns the scalar Euclidean distance between two vector input values.

Syntax

EUCLIDEAN_DISTANCE(vector_expression, vector_expression)

vector_expression

An expression that evaluates to a vector. The vector must be encoded as a blob containing packed single-precision floating-point numbers in little-endian byte order. A vector can be of any length, but both input vectors must be the same length and the blob lengths must be divisible by 4 bytes.

Remarks

To execute this function, the host processor must support AVX2 instruction set extensions. If AVX2 is not supported, an error will occur during execution.

If the result of EUCLIDEAN_DISTANCE() is infinity, negative infinity, or not a number (NaN), NULL will be returned instead.

EUCLIDEAN_DISTANCE(v1, v2) is computationally equivalent to SQRT(DOT_PRODUCT(VECTOR_SUB(v1, v2), VECTOR_SUB(v1, v2))). However, the EUCLIDEAN_DISTANCE() function is more efficient than the latter.

Examples

Example: SELECT Using EUCLIDEAN_DISTANCE() on Existing Rows

The following example executes EUCLIDEAN_DISTANCE() on two rows containing vectors. The HEX() built-in function is also used to return a readable form of the binary output.

Create a table with two BLOB-typed columns:

memsql> CREATE TABLE t (a BLOB, b BLOB);
Query OK, 0 rows affected (0.26 sec)

Using the JSON_ARRAY_PACK() built-in function to easily insert properly formatted vectors, insert a row with each vector in a different column:

memsql> INSERT INTO t VALUES (JSON_ARRAY_PACK('[0.7, 0.2, 1.7]'), JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'));
Query OK, 1 row affected (0.22 sec)

To demonstrate the contents of the table, use the HEX() built-in function to return a readable form of the binary data:

memsql> SELECT HEX(t.a), HEX(t.b) FROM t;
+--------------------------+--------------------------+
| HEX(t.a)                 | HEX(t.b)                 |
+--------------------------+--------------------------+
| 3333333FCDCC4C3E9A99D93F | 0000803F0000003F00000040 |
+--------------------------+--------------------------+
1 row in set (0.15 sec)

Query the table using the EUCLIDEAN_DISTANCE() function in a SELECT statement:

memsql> SELECT EUCLIDEAN_DISTANCE(t.a, t.b) FROM t;
+------------------------------+
| EUCLIDEAN_DISTANCE(t.a, t.b) |
+------------------------------+
|           0.5196152239171921 |
+------------------------------+
1 row in set (0.16 sec)

Example: EUCLIDEAN_DISTANCE() with JSON_ARRAY_PACK()

The following example uses JSON_ARRAY_PACK() as input parameters to the EUCLIDEAN_DISTANCE() built-in function:

memsql> SELECT EUCLIDEAN_DISTANCE(JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'), JSON_ARRAY_PACK('[0.7, 0.2, 1.7]'));
+--------------------------------------------------------------------------------------------+
| EUCLIDEAN_DISTANCE(JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'), JSON_ARRAY_PACK('[0.7, 0.2, 1.7]')) |
+--------------------------------------------------------------------------------------------+
|                                                                         0.5196152239171921 |
+--------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)

Related Topics

Was this article useful?