php - 如何在 SQL 查询中通过相似性查找图像？

Question

我想处理图像以在每个图像中建立 9 个区域，然后找到每个区域的平均颜色，然后将其保存到这样的 char 字段中:

255,255,255,255,255,255,107,195,305

然后要找到与给定图像相似的所有图像，我必须计算每对颜色之间的距离(与相同区域进行比较)，例如:

这些图像之间的差异是 1:

255,255,255,255,255,255,107,195,305
255,255,255,255,255,255,107,195,304

这些图像之间的区别是 3:

255,255,255,255,255,255,105,195,305
255,255,255,255,255,255,107,195,304

我的问题是如何执行这样的查询，并按相似度排序？该字段只是一个字符串，值以逗号分隔。

这样的查询有可能很快吗？还是我应该寻找不同的方法？我们谈论的是数千张图片

编辑:作为@therealsix，一种选择是将每个平均颜色值放入单独的列中。

Answer 1

一个更“SQLey”的方法来做到这一点，可能是使用一个更规范化的数据库方法，有 2 个表:

Image(ImageID int, ... other columns as required ...)
ImageZone(ImageID int, ZoneIndex int, ColourValue int, ...)

所以对于你的例子，你可能有

ImageID   ZoneIndex   ColourValue
-------   ---------   -----------
  1          1          255
  1          2          255
  ...
  1          9          304
  2          1          255
  ...
  2          9          305

然后，为了获得距离，类似于(我是 SQL Server 专家，但这应该很容易翻译成 MySQL):

 SELECT
    Candidate.ImageID,
    Candidate.ImageFile, /* or whatever... */
    Scores.Difference
 FROM
 (
   SELECT
      Original.ImageID AS OriginalID,
      Candidate.ImageID AS CandidateID,
      SUM(ABS(Original.ColourValue - Candidate.ColourValue)) AS Difference
   FROM ImageZone AS Original
   INNER JOIN ImageZone AS Candidate
     ON (Original.ImageID <> Candidate.ImageID)
     ON (Original.ZoneIndex = Candidate.ZoneIndex)
 ) AS Scores
 INNER JOIN Image AS Candidate ON (Scores.CandidateID = Candidate.ImageID)
 WHERE Scores.OriginalID = 1 /* or the image ID you want to look up */
 ORDER BY Difference

因此内部查询为每个候选区域创建一行，例如(O = 原始，C = 候选):

 O.ImageID  O.ZoneIndex  O.ColourValue  C.ImageID  C.ZoneIndex  C.ColourValue
 ---------  -----------  -------------  ---------  -----------  -------------
    1           1           255            2            1           255
    ... then ...
    1           9           305            2            9           304
    1           1           255            3            1            99
    ... then ...
    99          9           100           98            9            99

然后汇总为总差:

 OriginalID  CandidateID  Difference
 ----------  -----------  ----------
    1            2            1
    1            3           10
    ...
    99          98          500

然后，您仅在 OriginalID 为 1 的地方从该虚拟表中进行选择，并将其连接回原始图像表以获得最低“差异”分数(在本例中为 2)所需的任何详细信息。

恕我直言，这是一个更简洁的数据库设计(如果您以后使用更多区域等，则非常适合)。