CODEWITHSUNDEEP
mysqlsql
Grant Upson
Grant Upson

Reputation: 65

Determining the Euclidean Distance of all tuples against each other in MySQL

Say I have a table T with two columns, X and Y, and I want to find all the pairs of tuples where the result of calculating their Euclidean Distance using each tuples X and Y is equal to some value D.

In addition, this cannot have duplicates. i.e a pair (X, Y) of two tuples and the (Y, X) of the same two tuples cannot be in the result.

Without giving me the answer, is anyone able to guide me in the right path for answering this with an sql query? I've been wracking my brain for hours and I'm just not sure where to even start.

Upvotes: 0

Views: 160

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

Basically, you want a cross join and arithmetic. Let me assume that each row has a unique id. Then something like this:

select t1.*, t2.*,
       <formula for Euclidean distance here>
from t t1 join
     t t2
     on t1.id < t2.id

If you don't have a unique id, you can use tuple comparisons instead:

on (t1.x, t1.y) < (t2.x, t2.y)

Upvotes: 1

Related Questions