...

View Full Version : Wondering how to query for the closest matching



Kurisvo
01-04-2012, 10:47 PM
Lemme explain what I want to do...

I want to have a collection of images and then get the average color from each and combine the two to get the mid color (I have a pretty good idea on how to do this).

Then the hard part is this. I want to have a database with a limited amount of different images, with a field of their average color. So like this:


id: 1 | image: flowers.png | avarage_color: #11BD47 (a green color)
id: 2 | image: panadas.png | avarage_color: #858585 (a grey color)
id: 3 | image: sunset.jpg | avarage_color: #9360B3 (a purple color)


And then I have two images that the script is handling: a.png with an average of #F22955 (red) and b.png with an average of #293AF2 (a blue).

Let's say that A and B theoretically combine into #B71DBF (a purple).

Is it possible to query mysql so it returns the color closest to #B71DBF (doesn't have to be perfect)?

If this isn't possible with mysql, any ideas on how to do it?

Old Pedant
01-04-2012, 11:43 PM
A fun one!

Yes, I think it could be done.

It would be best if your colors table had separate R, G, B fields. Just stored as simple INT values.

And then break your desired color into R, G, B as well and just treat those, too, as INT.

And then something as simple as


SELECT ( ABS(R - 0xB7) + ABS(G - 0x1D) + ABS(B - 0xBF) ) AS colorDiff, *
FROM table
ORDER BY colorDiff
LIMIT 1

What do you think?

********

I think it could even be done if you don't have the R,G,B separate, but it would be a lot more work.

You'd have to use the MySQL HEX and CONV functions and a lot of care. Ask again if you can't use separate R,G,B values.

Old Pedant
01-04-2012, 11:50 PM
Ehhh...I guess it's not that hard:


SELECT ( ABS( CONV(SUBSTRING(RGB,1,2),16,10) - CONV(SUBSTRING('B71DBF',1,2),16,10 )
+ ABS( CONV(SUBSTRING(RGB,3,2),16,10) - CONV(SUBSTRING('B71DBF',3,2),16,10 )
+ ABS( CONV(SUBSTRING(RGB,5,2),16,10) - CONV(SUBSTRING('B71DBF',5,2),16,10 ) ) AS colorDiff, *
FROM table
ORDER BY colorDiff
LIMIT 1

The 'B71DBF' would of course come from a PHP (or other language) variable. Or you could split it apart ahead of time in your PHP code and pass the values in as simple ints.

Kurisvo
01-05-2012, 02:13 AM
Oooh awesome! Thanks so much!

Only thing is I get this when I run it.


You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM colors ORDER BY colorDiff LIMIT 1' at line 3

Thanks for giving me an idea on how to do it! :)

EDIT:

I poked at the query and this got rid of the issue


SELECT (ABS( CONV(SUBSTRING(RGB,1,2),16,10) - CONV(SUBSTRING('B71DBF',1,2),16,10 )
+ ABS( CONV(SUBSTRING(RGB,3,2),16,10) - CONV(SUBSTRING('B71DBF',3,2),16,10 )
+ ABS( CONV(SUBSTRING(RGB,5,2),16,10) - CONV(SUBSTRING('B71DBF',5,2),16,10 ) AS colorDiff), *
FROM colors
ORDER BY colorDiff
LIMIT 1

But now it is saying


Incorrect parameters in the call to native function 'ABS'

:)

Old Pedant
01-05-2012, 07:12 AM
Well,you didn't copy my parentheses, so you have the alias in the wrong place, but I goofed as well.

Let's try again:


mysql> select * from colors;
+--------+
| rgb |
+--------+
| 7F3A21 |
| 3A22ff |
| A920C0 |
| FFFFFF |
+--------+
4 rows in set (0.00 sec)

mysql>
SELECT RGB,
( ABS( CONV(SUBSTRING(RGB,1,2),16,10) - CONV(SUBSTRING('B71DBF',1,2),16,10) )
+ ABS( CONV(SUBSTRING(RGB,3,2),16,10) - CONV(SUBSTRING('B71DBF',3,2),16,10) )
+ ABS( CONV(SUBSTRING(RGB,5,2),16,10) - CONV(SUBSTRING('B71DBF',5,2),16,10) )
) AS colorDiff
FROM colors
ORDER BY colorDiff;
+--------+-----------+
| RGB | colorDiff |
+--------+-----------+
| A920C0 | 18 |
| 3A22ff | 194 |
| 7F3A21 | 243 |
| FFFFFF | 362 |
+--------+-----------+
4 rows in set (0.00 sec)


I purposely didn't put in the LIMIT 1 so you could see how reasonable/unreasonable the results are.

MySQL won't let you use the * wildcard (meaning "all fields") after you use a named field (aliased or not), so if you wanted to use * in that query, you'd need to put it where the very first RGB (in red) is.

I should note this will be about as efficient as molasses on a winter's day, so hopefully the table you are using it with isn't very large and/or you can limit the choices with a WHERE clause.

Kurisvo
01-05-2012, 06:11 PM
Oh wow, that's perfect!!!! Thanks so much!! :D



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum