CODEWITHSUNDEEP
phpmysqlsqlalgorithm
evnartabt
evnartabt

Reputation: 25

SQL select query with data calculations

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"

Upvotes: 2

Views: 110

Answers (1)

Diego Mazzaro
Diego Mazzaro

Reputation: 2882

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;

Upvotes: 3

Related Questions