Quick tip: Dot Product, Euclidean Distance and Cosine Similarity in SingleStoreDB
Tags: singlestoredb, vectordatabase
Published: 2023-12-29 23:31:55

## Abstract
SingleStoreDB has supported [vector functions](https://docs.singlestore.com/cloud/reference/sql-reference/vector-functions/) since 2017. In this article, we'll see examples of using Dot Product, Euclidean Distance and Cosine Similarity in SingleStoreDB to assess vector relationships. New vector capabilities will be available in the next major release of SingleStoreDB - stay tuned.

## Introduction

### Dot Product
The Dot Product is a mathematical procedure that combines two lists of numbers into a single value. This is achieved by multiplying corresponding numbers from each list and then adding up the products. This operation is useful for tasks like measuring the commonality between two sets of values or assessing the influence of one set on another.

### Euclidean Distance
Euclidean Distance is a mathematical metric that measures the straight-line distance between two points in a Cartesian coordinate system. To compute it, square the differences between the x and y coordinates of the two points, sum these squared differences, and then take the square root of the result. The final output is a single numerical representation of the spatial separation between the points, like the direct walking distance between them on a graph.

### Cosine Similarity
Cosine Similarity is a mathematical measure that gauges the similarity between two sets, such as two lists of words. Instead of focusing on the specific words, it considers the angle between the sets, irrespective of their sizes. Visualise these sets as vectors in a multi-dimensional space. Cosine Similarity essentially assesses how well the directions of these vectors align. High similarity arises when the vectors point in the same direction, while low similarity occurs when they are perpendicular. This method is commonly employed in comparing documents or texts, providing insights into whether they discuss similar topics, regardless of the specific wording.

### SingleStoreDB
SingleStoreDB provides direct support for Dot Product and Euclidean Distance using the vector functions `DOT_PRODUCT` and `EUCLIDEAN_DISTANCE`, respectively. Cosine Similarity is supported by combining the `DOT_PRODUCT` and `SQRT` functions. In this article, we'll see some simple examples of each function.

For further details, see:

- [DOT_PRODUCT](https://docs.singlestore.com/cloud/reference/sql-reference/vector-functions/dot-product/)
- [EUCLIDEAN_DISTANCE](https://docs.singlestore.com/cloud/reference/sql-reference/vector-functions/euclidean-distance/)
- [Cosine Similarity and Cosine Distance](https://docs.singlestore.com/cloud/reference/sql-reference/vector-functions/cosine-similarity-and-cosine-distance/)

## Create a SingleStoreDB Cloud account
A [previous article](https://dev.to/singlestore/quick-tip-using-dbt-with-singlestoredb-161g) showed the steps to create a free SingleStoreDB Cloud account. We'll use the following settings:

- **Workspace Group Name:** Iris Demo Group
- **Cloud Provider:** AWS
- **Region:** US East 1 (N. Virginia)
- **Workspace Name:** iris-demo
- **Size:** S-00

## Create a Database and Table
In our SingleStore Cloud account, let's use the **SQL Editor** to create a new database. Call this `iris_db`, as follows:

```sql
CREATE DATABASE IF NOT EXISTS iris_db;
```

We'll also create the `iris` table, as follows:

```sql
USE iris_db;

CREATE TABLE IF NOT EXISTS iris (
     vector BLOB,
     species VARCHAR(20)
);
```

The Iris data set contains 150 rows of data for three different species of flowers. Each flower has four columns of data: `sepal_length`, `sepal_width`, `petal_length` and `petal_width`. We can store these four column values together using `JSON_ARRAY_PACK` in a BLOB format, as follows:

```sql
INSERT INTO iris VALUES
(JSON_ARRAY_PACK('[5.1,3.5,1.4,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.9,3,1.4,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.7,3.2,1.3,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[4.6,3.1,1.5,0.2]'),'Iris-setosa'),
(JSON_ARRAY_PACK('[5,3.6,1.4,0.2]'),'Iris-setosa'),
...
(JSON_ARRAY_PACK('[6.7,3,5.2,2.3]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.3,2.5,5,1.9]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.5,3,5.2,2]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[6.2,3.4,5.4,2.3]'),'Iris-virginica'),
(JSON_ARRAY_PACK('[5.9,3,5.1,1.8]'),'Iris-virginica');
```

Only the first five and last five rows are shown above. The complete `INSERT` code listing is available in a [GitHub Gist](https://gist.github.com/VeryFatBoy/be7d0391eeeb4874a523cac613db3867).

## Query 1
First, let's try a query where we want to find the name of the flower species using an exact match for the `sepal_length`, `sepal_width`, `petal_length` and `petal_width`. We'll use the values `[5.9,3,5.1,1.8]` from the last row of the `iris` table, shown above.

### Dot Product

```sql
SELECT species
FROM iris
WHERE DOT_PRODUCT(vector, JSON_ARRAY_PACK('[5.9,3,5.1,1.8]')) = DOT_PRODUCT(vector, vector);
```

This SQL query retrieves the `species` from the `iris` table for rows where the Dot Product of the vector in the `vector` column with the specified vector `[5.9,3,5.1,1.8]` is equal to the Dot Product of the `vector` column with itself. Since there's only one row with the specified values in the `vector` column, the query essentially identifies the `species` for that particular set of values in the `iris` table.

### Euclidean Distance

```sql
SELECT species
FROM iris
WHERE EUCLIDEAN_DISTANCE(vector, JSON_ARRAY_PACK('[5.9,3,5.1,1.8]')) = 0;
```

This SQL query retrieves the `species` from the `iris` table for rows where the Euclidean Distance between the vector in the `vector` column and the specified vector `[5.9,3,5.1,1.8]` is equal to 0. In other words, they are one and the same.

### Cosine Similarity

```sql
SELECT species
FROM iris
WHERE DOT_PRODUCT(vector, JSON_ARRAY_PACK('[5.9,3,5.1,1.8]')) / SQRT(DOT_PRODUCT(vector, vector) * DOT_PRODUCT(JSON_ARRAY_PACK('[5.9,3,5.1,1.8]'), JSON_ARRAY_PACK('[5.9,3,5.1,1.8]'))) = 1;
```

This SQL query retrieves the `species` values from the `iris` table for rows where the normalised Dot Product of the vector in the `vector` column with the specified vector `[5.9,3,5.1,1.8]` equals 1. The condition checks for parallel vectors, indicating a high similarity or identical direction between the two vectors.

The result in each case should be:

```
+----------------+
| species        |
+----------------+
| Iris-virginica |
+----------------+
```

## Query 2
Now, let's use some fictitious data values `[5.2,3.6,1.5,0.3]` to make a prediction.

### Dot Product

```sql
SELECT species
FROM iris
ORDER BY DOT_PRODUCT(vector, JSON_ARRAY_PACK('[5.2,3.6,1.5,0.3]')) DESC
LIMIT 1;
```

This SQL query retrieves the `species` from the `iris` table and orders the results in descending order based on the Dot Product between the vectors in the `vector` column and the specified vector `[5.2,3.6,1.5,0.3]`. The LIMIT 1 ensures that only the top result is returned.

The result should be:

```
+----------------+
| species        |
+----------------+
| Iris-virginica |
+----------------+
```

### Euclidean Distance

```sql
SELECT species
FROM iris
ORDER BY EUCLIDEAN_DISTANCE(vector, JSON_ARRAY_PACK('[5.2,3.6,1.5,0.3]'))
LIMIT 1;
```

This SQL query retrieves the `species` from the `iris` table and orders the results based on the Euclidean Distance between the vectors in the `vector` column and the specified vector `[5.2,3.6,1.5,0.3]`. The LIMIT 1 ensures that only the top result, closest in Euclidean Distance, is returned.

The result should be:

```
+-------------+
| species     |
+-------------+
| Iris-setosa |
+-------------+
```

### Cosine Similarity
If we want to use `DOT_PRODUCT` and achieve a similar result to `EUCLIDEAN_DISTANCE`, we can use the following approach. The `DOT_PRODUCT` doesn't directly represent distance, so we need to modify the query. One possible adjustment is to consider the angle between vectors.

```sql
SELECT species
FROM iris
ORDER BY DOT_PRODUCT(vector, JSON_ARRAY_PACK('[5.2,3.6,1.5,0.3]')) / SQRT(DOT_PRODUCT(vector, vector) * DOT_PRODUCT(JSON_ARRAY_PACK('[5.2,3.6,1.5,0.3]'), JSON_ARRAY_PACK('[5.2,3.6,1.5,0.3]'))) DESC
LIMIT 1;
```

This SQL query normalises the `DOT_PRODUCT` by dividing it by the product of the magnitudes of the vectors. This adjustment helps to account for differences in vector magnitudes and makes the `DOT_PRODUCT` more comparable to a Cosine Similarity, which considers the angle between vectors. The ordering is done in descending order to prioritise higher similarity.

The result should be:

```
+-------------+
| species     |
+-------------+
| Iris-setosa |
+-------------+
```

## Summary
In this short article, we've seen several examples of how to use SingleStoreDB's built-in `DOT_PRODUCT` and `EUCLIDEAN_DISTANCE` vector functions. We've also seen how we can easily represent Cosine Similarity by combining the `DOT_PRODUCT` and `SQRT` functions.

We've used the functions to find an exact match and made predictions based on new, previously unknown, values.