Find best matching colors out of a database

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 sea开发者_如何学Gorches 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.

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.

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).