I need to write webapp where user will be able to perform search based on images colors. My question is how to store color data? I think that best solution will be to reduce image colors and prepare histogram for each r, g and b channel but I don't have idea how to design database. I want to use MySQL DBMS. Could someone point me into right direction?
我需要编写webapp，用户可以根据图像颜色执行搜索。我的问题是如何存储颜色数据？我认为最好的解决方案是减少图像颜色并为每个r，g和b通道准备直方图，但我不知道如何设计数据库。我想使用MySQL DBMS。有人能指出我正确的方向吗？
Regards
问候
2 个解决方案
#1
3
A couple of ideas come to mind for storing histogram data. The obvious choice is to have one table (or three for separate R/G/B channels) that represents the (normalized) histogram, with a column for each bin. If you're in 24 bit color (8 bits/channel), you could break each channel into 16 bins ([0-15], ..., [240-255]), and in each column store the percentage of pixels that fell into that bin.
存储直方图数据时会想到几个想法。显而易见的选择是有一个表（或三个用于单独的R / G / B通道）表示（标准化的）直方图，每个bin有一列。如果你是24位颜色（8位/通道），你可以将每个通道分成16个区（[0-15]，...，[240-255]），并在每列存储像素百分比落入那个垃圾箱。
Something like this:
像这样的东西：
id imgID R_0_15 ... R_240_255 G_0_15 ... G_240_255 B_0_15 ... B_240_255
1 1234 0.1 0.23 0.023 0.234 0.11 0.01
With this design, the entire (normalized) histogram for each image would be represented as a single row in the table.
通过这种设计，每个图像的整个（标准化）直方图将在表格中表示为单行。
Queries would be a bit challenging--you'd have to generate them dynamically to plug in the right column names for the value range of interest.
查询将有点挑战 - 您必须动态生成它们以插入感兴趣的值范围的正确列名称。
Perhaps a better way would be a HistogramBins table with a row entry for each image and each bin:
也许更好的方法是HistogramBins表，每个图像和每个bin都有一个行条目：
id imgID component bin_min bin_max percentage
1 1234 R 0 15 0.1
....omitted rows...
1 1234 R 240 255 0.23
...etc...
With that storage format, queries could be prepared rather than dynamically computed. It's not clear to me whether the components should be broken out as I did or if you should store one row for "bin 1" of all three color components. I'd probably want to write some queries and see what felt best for your application.
使用该存储格式，可以准备查询而不是动态计算。我不清楚组件是否应该按照我的方式进行分解，或者是否应该为所有三种颜色组件的“bin 1”存储一行。我可能想写一些查询，看看哪种方法最适合您的应用程序。
Also, the reason I keep saying 'normalized' is that this scheme would make your binning independent of image size.
此外，我一直说'标准化'的原因是这个方案会使你的分箱独立于图像大小。
Hope this helps get you started. Let us know what you end up with!
希望这有助于您入门。让我们知道你最终得到了什么！
#2
2
RGB values have no meaning to human perception but they can be easily converted to Hue, Saturation, Luminance which is more sensible to people. Unfortunately, saturation and luminance are pretty intuitive: richer:paler and lighter:darker, but we have no natural ordering for colors so hue is expressed as an arbitrary number of degrees around a circle. In practice, asking people to make fine hue discriminations, especially when searching for something yet unseen is pretty hard. Therefore, you might want to limit your categories to the vertices of the hexagon in figure "a".
RGB值对人类感知没有意义，但它们可以很容易地转换为Hue，Saturation，Luminance，这对人们来说更为明智。不幸的是，饱和度和亮度非常直观：更丰富：更淡和更亮：更暗，但我们没有自然的颜色排序，因此色调表示为围绕圆的任意数度的度数。在实践中，要求人们做出良好的色调区分，特别是在寻找尚未看到的东西时，这很难。因此，您可能希望将类别限制为图“a”中六边形的顶点。
Then you run into the question of what is the representative color of a photograph? Is the image that is half blue sky and half tan sand blue or tan? Are you picking a dominant hue? You might want to apply a huge Gaussian blur and then average the resultant hues. You probably need to refine your question and goals further.
然后你会遇到一个问题，即照片的代表性颜色是什么？图像是半蓝天，半棕褐色或棕褐色？你选择了主导色调吗？您可能希望应用巨大的高斯模糊，然后对结果色调进行平均。您可能需要进一步完善您的问题和目标。
Idle musing on perceptual models
Even HSL has its descriptive limitations. I mention "tan" above as the color of sand. Most readers probably have no problem at all perceiving or naming it, but unless you have too much experience playing with color, it is pretty non-obvious that the hue of tan is orange but pale (less saturated) and bright (higher value). And about a third of the hue circle is devoted to greens, etc.
甚至HSL也有其描述性限制。我在上面提到“棕褐色”作为沙子的颜色。大多数读者可能完全没有问题或命名它，但除非你有太多使用颜色的经验，否则棕褐色的色调是橙色但是苍白（不太饱和）和明亮（更高的值）是非常明显的。大约三分之一的色调圈用于绿色等。
#1
3
A couple of ideas come to mind for storing histogram data. The obvious choice is to have one table (or three for separate R/G/B channels) that represents the (normalized) histogram, with a column for each bin. If you're in 24 bit color (8 bits/channel), you could break each channel into 16 bins ([0-15], ..., [240-255]), and in each column store the percentage of pixels that fell into that bin.
存储直方图数据时会想到几个想法。显而易见的选择是有一个表（或三个用于单独的R / G / B通道）表示（标准化的）直方图，每个bin有一列。如果你是24位颜色（8位/通道），你可以将每个通道分成16个区（[0-15]，...，[240-255]），并在每列存储像素百分比落入那个垃圾箱。
Something like this:
像这样的东西：
id imgID R_0_15 ... R_240_255 G_0_15 ... G_240_255 B_0_15 ... B_240_255
1 1234 0.1 0.23 0.023 0.234 0.11 0.01
With this design, the entire (normalized) histogram for each image would be represented as a single row in the table.
通过这种设计，每个图像的整个（标准化）直方图将在表格中表示为单行。
Queries would be a bit challenging--you'd have to generate them dynamically to plug in the right column names for the value range of interest.
查询将有点挑战 - 您必须动态生成它们以插入感兴趣的值范围的正确列名称。
Perhaps a better way would be a HistogramBins table with a row entry for each image and each bin:
也许更好的方法是HistogramBins表，每个图像和每个bin都有一个行条目：
id imgID component bin_min bin_max percentage
1 1234 R 0 15 0.1
....omitted rows...
1 1234 R 240 255 0.23
...etc...
With that storage format, queries could be prepared rather than dynamically computed. It's not clear to me whether the components should be broken out as I did or if you should store one row for "bin 1" of all three color components. I'd probably want to write some queries and see what felt best for your application.
使用该存储格式，可以准备查询而不是动态计算。我不清楚组件是否应该按照我的方式进行分解，或者是否应该为所有三种颜色组件的“bin 1”存储一行。我可能想写一些查询，看看哪种方法最适合您的应用程序。
Also, the reason I keep saying 'normalized' is that this scheme would make your binning independent of image size.
此外，我一直说'标准化'的原因是这个方案会使你的分箱独立于图像大小。
Hope this helps get you started. Let us know what you end up with!
希望这有助于您入门。让我们知道你最终得到了什么！
#2
2
RGB values have no meaning to human perception but they can be easily converted to Hue, Saturation, Luminance which is more sensible to people. Unfortunately, saturation and luminance are pretty intuitive: richer:paler and lighter:darker, but we have no natural ordering for colors so hue is expressed as an arbitrary number of degrees around a circle. In practice, asking people to make fine hue discriminations, especially when searching for something yet unseen is pretty hard. Therefore, you might want to limit your categories to the vertices of the hexagon in figure "a".
RGB值对人类感知没有意义，但它们可以很容易地转换为Hue，Saturation，Luminance，这对人们来说更为明智。不幸的是，饱和度和亮度非常直观：更丰富：更淡和更亮：更暗，但我们没有自然的颜色排序，因此色调表示为围绕圆的任意数度的度数。在实践中，要求人们做出良好的色调区分，特别是在寻找尚未看到的东西时，这很难。因此，您可能希望将类别限制为图“a”中六边形的顶点。
Then you run into the question of what is the representative color of a photograph? Is the image that is half blue sky and half tan sand blue or tan? Are you picking a dominant hue? You might want to apply a huge Gaussian blur and then average the resultant hues. You probably need to refine your question and goals further.
然后你会遇到一个问题，即照片的代表性颜色是什么？图像是半蓝天，半棕褐色或棕褐色？你选择了主导色调吗？您可能希望应用巨大的高斯模糊，然后对结果色调进行平均。您可能需要进一步完善您的问题和目标。
Idle musing on perceptual models
Even HSL has its descriptive limitations. I mention "tan" above as the color of sand. Most readers probably have no problem at all perceiving or naming it, but unless you have too much experience playing with color, it is pretty non-obvious that the hue of tan is orange but pale (less saturated) and bright (higher value). And about a third of the hue circle is devoted to greens, etc.
甚至HSL也有其描述性限制。我在上面提到“棕褐色”作为沙子的颜色。大多数读者可能完全没有问题或命名它，但除非你有太多使用颜色的经验，否则棕褐色的色调是橙色但是苍白（不太饱和）和明亮（更高的值）是非常明显的。大约三分之一的色调圈用于绿色等。