CODEWITHSUNDEEP
phpmysqldatabase-designsearch
Minion
Minion

Reputation: 565

Database design to store image color pattern in MySQL for searching Image by color

I am building a image galley using PHP and MySQL where I want to implement Image search by it's color. By following Imagick::getImageHistogram i got the most presented colors from the images. 

<?php
$image = new Imagick("thing.png");
$pixels=$image->getImageHistogram();
foreach($pixels as $p){
 $colors = $p->getColor();
 foreach($colors as $c){
        print( "$c\t" );
 }
 print( "\t:\t" . $p->getColorCount() . "\n" );
}
?>

This will print out something like:

Red    Green    Blue       Alpha    :    No of times appeared
252    250      252        1        :       125
194    156      182        1        :       126
109    18       79         1        :       11440
2      117      162        1        :       12761
255    255      255        1        :       40769

Although I am done with getting the colors, I am stuck with designing the database to store the color information along with image path in the database.

My question is how to design a database (table structure) to store this kind of data where search can query can be applied in an effective manner.

Update:

Secondly how can I get the images with a matching color. Let's say user is searching for a color #ff0000, then how can I get all the nearest matched images from the database.

Thank You

Upvotes: 5

Views: 1728

Answers (3)

Teaqu
Teaqu

Reputation: 3263

How about you first times all the red values by the number of times they appeared and then add those values together to get the overall red value. Then divide by the total number of times appeared. Do the same for Green and Blue.

For example:

total red = ((252*125)+(194*126)+(109*11440)+(2*12761)+(255*40769)) 
            / (125 + 126 + 11440 + 12761 + 40769)
          = 180

Then just store them in the database like

             id         red      green       blue     image_path
Image 1      1          225      134         4        /dir/
Image 2      2          143      0           145      /dir/
Image 3      3          239      200         111      /dir/

If you were searching for Red images you could do something like:

SELECT id, image_path WHERE red > 200 AND blue < 100 AND green < 100

Im not sure of the specifics, but you could mess around with the values.

Upvotes: 0

Daniel W.
Daniel W.

Reputation: 32290

You should normalize this.

3 Tables:

Image {image_id, name}
Colors {color_id, red, green, blue, alpha}
ImageHasColor {image_id, color_id, number_of_times_appeared}

Inserting data should be simple, use ...insert_id functions to get the id from the row you just inserted.

Select with joins like:

SELECT * FROM
Image i
JOIN ImageHasColors h
ON i.image_id = h.image_id
JOIN Colors c
ON c.color_id = h.color_id
ORDER BY i.image_id

Check this link on how to convert HEX color to RGB values: http://bavotasan.com/2011/convert-hex-color-to-rgb-using-php/

Search top 10 really red pictures:

SELECT * FROM
Image i
JOIN ImageHasColors h
ON i.image_id = h.image_id
JOIN Colors c
ON c.color_id = h.color_id
WHERE c.red > 200
AND   c.green < 50
AND   c. green < 50
ORDER BY h.number_of_times_appeared
LIMIT 10

Search rather black pictures:

SELECT * FROM
Image i
JOIN ImageHasColors h
ON i.image_id = h.image_id
JOIN Colors c
ON c.color_id = h.color_id
WHERE c.red < 30
AND   c.green < 30
AND   c. green < 30
ORDER BY h.number_of_times_appeared
LIMIT 10

Upvotes: 3

Gravy
Gravy

Reputation: 12445

Why don't you create a database table with the following fields:

Images Table

id
name
red
green
blue
alpha

Upvotes: 0

Related Questions