CODEWITHSUNDEEP
mysqlmathdistance
rfa
rfa

Reputation: 53

how to do mathematical calculation in mysql?

Lets say I have a table like this

ID   Point   x     y     z
---  ----   ---   ---   ---
1     A      3     2     1    
2     B      1     3     2
3     C      2     4     1
4     D      1     3     3
5     E      5     1     2

I want to get the nearest point to N which in (x,y,z) = (1,2,3)

To get the nearest point, we need euclidean distance formula. Lets say, squareroot of [(N(x)-data(x))^2 + (N(y)-data(y))^2 + (N(z)-data(z))^2]

How do I do the query contains the euclidean distance formula to select the rows I want?

Upvotes: 1

Views: 384

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271191

Just use order by and limit:

select t.*
from table t
order by power(x - 1, 2) + power(y - 2, 2) + power(z - 3, 2)
limit 1;

As mentioned in a comment, you don't actually need the square root, because the ordering is the same.

By the way, you can put the calculation in the select so you can return the distance:

select t.*,
       sqrt(power(x - 1, 2) + power(y - 2, 2) + power(z - 3, 2)) as distance
from table t
order by distance
limit 1;

Upvotes: 1

Related Questions