# Measure the distance between two values

Model / similarity

A variant of [Find similar rows](https://meridian.online/operations/find-similar-rows).

Compute a distance, similarity or inner product between two vectors or strings in the same row.

Page: <https://meridian.online/operations/find-similar-rows/measure-distance-between-values>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

measure the distance, similarity and inner product of two vectors or two strings in a row

```sql
CREATE TEMP TABLE vec_pairs AS SELECT * FROM (VALUES
  ('a', [1.0, 2.0, 2.0]::DOUBLE[3], [2.0, 0.0, 1.0]::DOUBLE[3]),
  ('b', [1.0, 0.0, 0.0]::DOUBLE[3], [0.0, 1.0, 0.0]::DOUBLE[3]),
  ('c', [1.0, 1.0, 1.0]::DOUBLE[3], [2.0, 2.0, 2.0]::DOUBLE[3])) t(id, u, v);
CREATE TEMP TABLE vectors AS
SELECT id, array_distance(u, v) AS euclidean, array_cosine_similarity(u, v) AS cosine_similarity, array_cosine_distance(u, v) AS cosine_distance, array_inner_product(u, v) AS inner_product FROM vec_pairs;
CREATE TEMP TABLE str_pairs AS SELECT * FROM (VALUES ('kitten', 'sitting'), ('martha', 'marhta'), ('karolin', 'kathrin'), ('same', 'same')) t(s, t);
CREATE TEMP TABLE strings AS
SELECT s, t, levenshtein(s, t) AS edits, jaro_similarity(s, t) AS jaro, jaro_winkler_similarity(s, t) AS jaro_winkler, CASE WHEN length(s) = length(t) THEN hamming(s, t) END AS hamming FROM str_pairs;
SELECT * FROM vectors ORDER BY id;
SELECT * FROM strings ORDER BY s;
-- the values are worked by hand: (1,2,2) and (2,0,1) are sqrt(6) apart with inner product 4 and cosine 4 / (3 * sqrt(5));
-- kitten to sitting takes 3 edits, and martha and marhta score 0.9444 by Jaro and 0.9611 by Jaro-Winkler (Winkler 1990)
SELECT CASE WHEN ((SELECT abs(euclidean - sqrt(6)) < 1e-9 AND abs(cosine_similarity - 4 / (3 * sqrt(5))) < 1e-9 AND abs(cosine_distance - (1 - 4 / (3 * sqrt(5)))) < 1e-9 AND inner_product = 4 FROM vectors WHERE id = 'a')
  AND (SELECT euclidean = sqrt(2) AND cosine_similarity = 0 AND inner_product = 0 FROM vectors WHERE id = 'b')
  AND (SELECT abs(euclidean - sqrt(3)) < 1e-9 AND abs(cosine_similarity - 1) < 1e-9 AND inner_product = 6 FROM vectors WHERE id = 'c')
  AND (SELECT edits = 3 AND hamming IS NULL FROM strings WHERE s = 'kitten')
  AND (SELECT edits = 2 AND abs(jaro - 0.9444444444) < 1e-9 AND abs(jaro_winkler - 0.9611111111) < 1e-9 FROM strings WHERE s = 'martha')
  AND (SELECT hamming = 3 FROM strings WHERE s = 'karolin')
  AND (SELECT edits = 0 AND jaro = 1 AND hamming = 0 FROM strings WHERE s = 'same')) THEN 'as expected' ELSE error('the vector and string measures should match the values worked by hand for each of the seven pairs') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Ibis: [`Levenshtein`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27)
- mlpack: [`Distances`](https://www.mlpack.org/doc/user/core.html) (read 2026-09-27)
- mlpack: [`Kernels`](https://www.mlpack.org/doc/user/core.html) (read 2026-09-27)
- Apache SeaTunnel: [`Vector Functions`](https://seatunnel.apache.org/docs/2.3.13/transforms/sql-functions) (read 2026-09-27)
- subtoken: [`array_cosine_distance`](https://duckdb.org/docs/current/sql/functions/array.html) (read 2026-09-27)
- subtoken: [`array_cosine_similarity`](https://duckdb.org/docs/current/sql/functions/array.html) (read 2026-09-27)
- subtoken: [`array_distance`](https://duckdb.org/docs/current/sql/functions/array.html) (read 2026-09-27)
- subtoken: [`array_inner_product`](https://duckdb.org/docs/current/sql/functions/array.html) (read 2026-09-27)
- subtoken: [`array_negative_inner_product`](https://duckdb.org/docs/current/sql/functions/array.html) (read 2026-09-27)
- subtoken: [`list_cosine_distance`](https://duckdb.org/docs/current/sql/functions/list.html) (read 2026-09-27)
- subtoken: [`list_cosine_similarity`](https://duckdb.org/docs/current/sql/functions/list.html) (read 2026-09-27)
- subtoken: [`list_distance`](https://duckdb.org/docs/current/sql/functions/list.html) (read 2026-09-27)
- subtoken: [`list_inner_product`](https://duckdb.org/docs/current/sql/functions/list.html) (read 2026-09-27)
- subtoken: [`list_negative_inner_product`](https://duckdb.org/docs/current/sql/functions/list.html) (read 2026-09-27)