5

We have a database-table where we manage a range of colors. In this table we save the following attributes for a color:

  • RBG-values
  • HSV-values
  • and Lab-values

Each of those values is in a separate field. What we would like to achieve now is to find the best matching colors if you search for those values.

The user can choose for which of those color-models he wants to do a search. Our first approach was to search for those values in the database with a range (so if the user searches for a value 150 of red (RGB) we query the database with BETWEEN 100 AND 200.

The result is not very good, since the values combined are always different from when you search for them and find appropriate rows.

Is there any better approach for searching our database for best matching colors?

Explanation of "best match":
We want to find the nearest color, so if we search for a red, we just want reds. Maybe it's possible to calculate the percentage of match so the user could choose if it has to be a 100% match or if 50% are also okay.

Tim
  • 5,893
  • 3
  • 35
  • 64
  • 1
    You should explain the algorithm you want to use which decides what color is considered the "best match". – Jon Dec 08 '10 at 12:40
  • If I would knew that I think I wouldn't need to ask this question ;) The user will have 3 fields: R, G and B if he wants to search with a RGB-value. And as stated above, the results are not very satisfying. – Tim Dec 08 '10 at 12:45
  • 1
    That's not what he 's asking, he's asking you to define what you mean by "best match". Is it the one that matches it the closest, or the best complementing color, or something totally different? – wimvds Dec 08 '10 at 12:51
  • Actually that's the question :) "Is it the one that matches it the closest, or the best complementing color, or something totally different?" What is "matching the closest" and what is "something totally different"? Tim, you must define - in human terms - what is "best matching". – Rudie Dec 08 '10 at 12:55
  • 1
    You might also find the answers to my question useful - http://stackoverflow.com/questions/4057475/rounding-colour-values-to-the-nearest-of-a-small-set-of-colours – El Yobo Dec 08 '10 at 13:02

2 Answers2

2

I worked on a similar project earlier...They used a simple formula to determine which Color is the closest...

Say Rm,Gm,Bm is Color to be Matched....and Rx,Gx,Bx is another color....

So, we calculate e = (Rm-Rx)^2 + (Gm-Gx)^2 + (Bm-Bx)^2....The One with the lowest value was considered close...Our aim is find the (Rx,Gx,Bx) with the minimum e.

Our Query looked like this Select ColorName from Colortable order by (Rm-Rx)*(Rm-Rx)+...(Bm-Bx) TOP 10 ( i dont remember the exact query eithor...)

This gave you the top 10 best matched colors...

Note : I don't stand for the formula, but it works just fine in practical cases.

st0le
  • 33,375
  • 8
  • 89
  • 89
  • This matches a color per R, G and B, not the combined color value. Maybe it's the best, but I don't think human eyes would always agree. Ibviously there's also the personal factor: some people think color X is blue and others think it's more green. – Rudie Dec 08 '10 at 12:58
  • This is what I would also have suggested, after reading this: http://en.wikipedia.org/wiki/Color_quantization (read "Algorithms", the part just below the images). – Jon Dec 08 '10 at 12:59
0

The closest match would be the one with shortest vector between the requested ($r, $g, $b) and stored values. e.g.

SELECT c.id, c.r, c.g, c.b
FROM colours c
ORDER BY ((c.r-$r)*(c.r-$r)) 
   + ((c.g-$g)*(c.g-$g))
   + ((c.b-$b)*(c.b-$b)) ASC
LIMIT 0,1;

(the same method will work for HSV)

But placing bounds on the query would allow any indexes to be used to reduce the result set:

SELECT c.id, c.r, c.g, c.b
FROM colours c
WHERE c.r BETWEEN ($r-$t) AND ($r+$t)
AND c.g BETWEEN ($g-$t) AND ($g+$t)
AND c.b BETWEEN ($b-$t) AND ($b+$t)
ORDER BY ((c.r-$r)*(c.r-$r)) 
   + ((c.g-$g)*(c.g-$g))
   + ((c.b-$b)*(c.b-$b)) ASC
LIMIT 0,1;

(he value for $t, depends on how many colours you have and represents the maximum vector distance between any 2 adjacent points. (try some values and see what happens).

symcbean
  • 47,736
  • 6
  • 59
  • 94