What is the most efficient way to query based on 3D Euclidean distance?
I have a MySQL table with X, Y, and Z coordinates. Each row of this table corresponds to a specific point in three space. Currentl开发者_如何学运维y, this data is stored as three separate integer columns, but I can change that if need be.
I want to query this table to find the closest point given the input point (x, y, z). One naive way to do this would be to select SQRT(POW((TableName.X - x), 2) + POW((TableName.Y - y), 2) + POW((TableName.Z - z), 2)) AS Distance
for each row in the table, and then select the row that has the smallest Distance
.
I know MySQL has a Point
data type, but I'm not sure if that will help here. Does anyone know of an efficient way to calculate Euclidean distance? Thanks in advance.
Order by POW((TableName.X - x), 2) + POW((TableName.Y - y), 2) + POW((TableName.Z - z), 2)
without the SQRT
.
Since you only care about ordering, and the square root is monotone increasing, you can skip the square root. Without the SQRT
, the math should be fast enough.
精彩评论