SQL select query with data calculations

Question

I need to find the closest point of three given numbers in my database and return their "test" cell.

these are the calculations i want to do where r1, g1 and b1 are different columns in my table and r2,g2 and b2 are the given numbers.

d=sqrt((r2-r1)^2+(g2-g1)^2+(b2-b1)^2)
p=min(d/sqrt((255)^2+(255)^2+(255)^2))

What ive got so far is

$rr = 130;
$gg = 83;
$bb = 234;

SELECT test
  FROM `my table` 
 WHERE MIN(SQRT(POWER($rr-r,2)+POWER($gg-g,2)+POWER($bb-b,2)) / 
          (SQRT(POWER(255,2)+POWER(255,2)+POWER(255,2)))

Im looking for ways to solve this, maybe if i do the calculations outside of mysql with the use of php?

The data in my table are random numbers between 0 and 255, what algorithm can i use for optimal search?

sample table

╔════╦══════════════╦══════╦════╦═════╦
║ id ║  test        ║  r   ║  g ║ b   ║
╠════╬══════════════╬══════╬════╬═════╣
║  1 ║ sample1      ║  4   ║ 72 ║ 251 ║
║  2 ║ sample2      ║  148 ║ 9  ║ 139 ║
║  3 ║ sample3      ║  101 ║ 233║ 88  ║
║  4 ║ sample4      ║  231 ║ 147║  16 ║
╚════╩══════════════╩══════╩════╩═════╝

given the numbers rr=151 gg= 18 and bb=140 i want it to return "sample2"

please post .a schema, sample data, and expected output - ideally as a SQLFiddle — Neville Kuyt
– Neville Kuyt, Commented Apr 21, 2017 at 8:26
You have one id column, and another column named primary_key. Which one is the primary key? — jarlh
– jarlh, Commented Apr 21, 2017 at 8:55

Diego Mazzaro · Accepted Answer · 2017-04-26 23:42:37Z

3

The idea is that you can order the rows by distance and then get back only the first one (lower distance).

Also consider that for the order the result does not change if you omit the radical and the fraction. This will speed up the search as well as writing explicit products instead than using the POWER function.

SELECT test
  FROM `my table` 
  ORDER BY ($rr-r)*($rr-r) + ($gg-g)*($gg-g) + ($bb-b)*($bb-b)
  LIMIT 1;

edited Apr 26, 2017 at 23:42

answered Apr 21, 2017 at 9:39

Diego Mazzaro

2,9021 gold badge23 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

maraca Over a year ago

I don't know about MySQL, but maybe it would be even faster to write ($rr-r) * ($rr - r) instead of using the POWER function.

Diego Mazzaro Over a year ago

@maraca I agree, I have updated according to your note.

Collectives™ on Stack Overflow

SQL select query with data calculations

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related