Model / similarity · All operations

Compute pairwise distances

A variant of Find similar rows

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

Status

In arcform

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.
  • The statement checks its own result and raises an error when it is wrong: “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”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

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 (2)
  • KNIME Distance Matrix (extension)read 2026-09-27

    Extension outside the base install: 22 current nodes: Aggregated Distance; Bit Vector Distances; Byte Vector Distances; DBSCAN, …

  • Random Forest Distanceread 2026-09-27

    Derive a row-to-row distance measure from the proximities a random forest induces

RapidMiner Studio (4)
  • Cross Distancesread 2026-09-27

    Distance or similarity from each row of a request table to each row of a reference table

  • Data to Similarityread 2026-09-27

    Compute a similarity measure object between all pairs of rows in a table

  • Data to Similarity Dataread 2026-09-27

    Compute pairwise row similarities and return them as a table of pairs

  • Similarity to Dataread 2026-09-27

    Turn a similarity measure object into a table of pairwise similarities

Yggdrasil Decision Forests (1)
  • distanceread 2026-09-27

    Pairwise distance between rows of two datasets, from how often they share leaves

Background

A distance matrix is a square matrix containing the distances, taken pairwise, between the elements of a set.[1] A distance matrix for a set of N elements has size N×N.[1] Depending on the application, the distance used to define a distance matrix may or may not be a metric.[1]

When to use it

RapidMiner's Cross Distances operator expects both input tables to have the same attributes in the same order, and does not work properly when the order differs.[2] Before pairing two tables, line their columns up by name, since a positional comparison of mismatched columns gives numbers that look plausible and mean nothing. (Meridian's view)

The method

In a distance matrix for a metric distance, the entries on the main diagonal are all zero, the off-diagonal entries are positive, and the matrix is symmetric.[1] A distance matrix for a metric distance also satisfies the triangle inequality between any three of its elements.[1]

SciPy's pdist computes pairwise distances between m observations given as row vectors, with metrics including euclidean, cosine, hamming, jaccard and mahalanobis.[3] SciPy's pdist returns a condensed distance matrix, storing in one entry the distance for each pair of observations i and j where i is less than j.[3] SciPy's squareform turns an n-by-n symmetric distance matrix into a vector of n * (n-1) / 2 distances, and turns such a vector back into the square matrix.[4] When SciPy's squareform builds the square matrix, it sets both the (i, j) and the (j, i) entries from the one stored distance and makes every diagonal element zero.[4] Keep each unordered pair once, as a table of pairs, and widen it into a square matrix only when a downstream step asks for one. (Meridian's view)

Compared with the alternatives

RapidMiner's Cross Distances operator computes the distance between each example of a request set and each example of a separate reference set.[2] The all nearest neighbours problem asks for the nearest neighbour of every point in a set, and can reuse work, since the distance from X to Y is also the distance from Y to X.[5]

Sources

  1. [1]Distance matrix - Wikipediasecondary source
  2. [2]Cross Distances - Rapidminer Documentationdocumentation
  3. [3]pdist — SciPy v1.18.0 Manualdocumentation
  4. [4]squareform — SciPy v1.18.0 Manualdocumentation
  5. [5]Nearest neighbor search - Wikipediasecondary source