# Compute pairwise distances

Model / similarity

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

Compute the distance or similarity between every pair of rows, as a matrix or a table of pairs.

Page: <https://meridian.online/operations/find-similar-rows/compute-pairwise-distances>

## Status

Runs as a SQL step.

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

## The DuckDB step

compute the distance and similarity between every pair of rows, as pairs and as a matrix

```sql
CREATE TEMP TABLE points AS SELECT * FROM (VALUES ('A', [1.0, 0.0]), ('B', [0.0, 1.0]), ('C', [1.0, 1.0]), ('D', [2.0, 2.0])) t(id, v);
-- a table of pairs: each unordered pair of rows once
CREATE TEMP TABLE pairs AS
SELECT a.id AS first, b.id AS second, list_distance(a.v, b.v) AS distance, list_cosine_similarity(a.v, b.v) AS cosine
FROM points a JOIN points b ON a.id < b.id;
-- a matrix: every ordered pair, including each row with itself, turned into one column per row
CREATE TEMP TABLE matrix AS
PIVOT (SELECT a.id AS row_id, b.id AS col_id, list_distance(a.v, b.v) AS distance FROM points a CROSS JOIN points b) ON col_id USING first(distance) GROUP BY row_id ORDER BY row_id;
SELECT * FROM pairs ORDER BY first, second;
SELECT * FROM matrix;
SELECT CASE WHEN ((SELECT count(*) = 6 AND list((first || second) ORDER BY first, second) = ['AB', 'AC', 'AD', 'BC', 'BD', 'CD'] FROM pairs)
  AND (SELECT list(round(distance, 6) ORDER BY first, second) = [1.414214, 1.0, 2.236068, 1.0, 2.236068, 1.414214] FROM pairs)
  AND (SELECT list(round(cosine, 6) ORDER BY first, second) = [0.0, 0.707107, 0.707107, 0.707107, 0.707107, 1.0] FROM pairs)
  AND (SELECT count(*) = 4 FROM matrix) AND (SELECT count(*) = 5 FROM (DESCRIBE matrix))
  AND (SELECT "A" = 0 AND "B" = 0 AND "C" = 0 AND "D" = 0 FROM (SELECT max("A") FILTER (WHERE row_id = 'A') AS "A", max("B") FILTER (WHERE row_id = 'B') AS "B", max("C") FILTER (WHERE row_id = 'C') AS "C", max("D") FILTER (WHERE row_id = 'D') AS "D" FROM matrix))
  AND (SELECT abs("C" - 1) < 1e-12 AND abs("D" - sqrt(5)) < 1e-12 FROM matrix WHERE row_id = 'A')) THEN 'as expected' ELSE error('four rows should give 6 pairs with the distances and cosines worked by hand, and a 4 by 4 matrix with zeros on its diagonal') 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).

- KNIME Analytics Platform: [`KNIME Distance Matrix (extension)`](https://api.hub.knime.com/extensions/org.knime.features.distmatrix) (read 2026-09-27)
- KNIME Analytics Platform: [`Random Forest Distance`](https://api.hub.knime.com/nodes/*w_GCZJ7eZW2G8We-?details=full) (read 2026-09-27)
- RapidMiner Studio: [`Cross Distances`](https://docs.rapidminer.com/latest/studio/operators/modeling/similarities/cross_distances.html) (read 2026-09-27)
- RapidMiner Studio: [`Data to Similarity`](https://docs.rapidminer.com/latest/studio/operators/modeling/similarities/data_to_similarity.html) (read 2026-09-27)
- RapidMiner Studio: [`Data to Similarity Data`](https://docs.rapidminer.com/latest/studio/operators/modeling/similarities/data_to_similarity_data.html) (read 2026-09-27)
- RapidMiner Studio: [`Similarity to Data`](https://docs.rapidminer.com/latest/studio/operators/modeling/similarities/similarity_to_data.html) (read 2026-09-27)
- Yggdrasil Decision Forests: [`distance`](https://github.com/google/yggdrasil-decision-forests/blob/0a54b57c95ff0cf42ecf0310ace0513a4f32cb65/yggdrasil_decision_forests/port/python/ydf/model/decision_forest_model/decision_forest_model.py) (read 2026-09-27)