我有一个MySQL表有数千个数据点存储在3列R，G，B。我怎么能找到该数据点是最接近指定点（A，B，C）采用欧氏距离​​？

我保存单独一个表中的颜色的RGB值，所以在每一列中的值限制为0-255。我想要做的是通过寻找具有最小欧几里得距离的颜色找到最接近的色彩匹配。

我能明显通过上表计算距离的每一个点上运行，但将不足以有效地扩展。任何想法？

解决方案 既然你要寻找的最小距离，而不是精确的距离，你可以跳过的平方根。我想欧氏距离平方适用于这里。 您已经说过的值为0到255之间的界限，这样你就可以做出一个索引查找表255的值。

下面是我想在SQL中的条款。 R0 ， G0 和 B0 重present目标色彩。该表向量将守稳在＃2中提及的平方值。这种解决方案将访问的所有记录，但结果集可以通过排序和选择仅在第一行被设置为1。

 选择
    C.R，C，G，C.B，
    mR.dist + mG.dist + mB.dist为squared_dist
从
    颜色C，
    矢量先生，
    矢量毫克，
    矢量MB
哪里
    C.R-R0 = mR.point和
    C，G-G0 = mG.point和
    C.B-B0 = mB.point
通过...分组
    C.R，C，G，C.B
 

I have a MySQL table with thousands of data points stored in 3 columns R, G, B. how can I find which data point is closest to a given point (a,b,c) using Euclidean distance?

I'm saving RGB values of colors separately in a table, so the values are limited to 0-255 in each column. What I'm trying to do is find the closest color match by finding the color with the smallest euclidean distance.

I could obviously run through every point in the table to calculate the distance but that wouldn't be efficient enough to scale. Any ideas?

解决方案

Since you're looking for the minimum distance and not exact distance you can skip the square root. I think Squared Euclidean Distance applies here. You've said the values are bounded between 0-255, so you can make an indexed look up table with 255 values.

Here is what I'm thinking in terms of SQL. r0, g0, and b0 represent the target color. The table Vector would hold the square values mentioned above in #2. This solution would visit all the records but the result set can be set to 1 by sorting and selecting only the first row.

select 
    c.r, c.g, c.b,
    mR.dist + mG.dist + mB.dist as squared_dist
from 
    colors c,
    vector mR,
    vector mG,
    vector mB
where
    c.r-r0 = mR.point and
    c.g-g0 = mG.point and
    c.b-b0 = mB.point
group by
    c.r, c.g, c.b