MySQL GROUP BY if Multiple Numbered Columns are Close to Each Other (+/- 1)

user_2005612 0
Viewed 48

I have a mysql table with a large list of coordinates (x, y, z). I want to find the most common spots, but when the same place is logged, it isn't identical. For example, x could be 496.0481 or 496.3904, but that is actually the same place.

When I do the following query I get a list of the absolute exact matches, but those are very few and far between:

SELECT x, y, z, COUNT(*) AS coords
FROM coordinates 
GROUP BY x, y, z
ORDER BY coords DESC
LIMIT 10;

How can I adjust this to be grouped by each of x, y, and z to be +/- 1 to catch a larger area? I've tried a mix of IF and BETWEEN statements but can't seem to get anything to work.

If I do GROUP BY round(x), round(y), round(z), that gets a larger range but doesn't capture if the number goes from 496 to 497 even if they are just slightly different.

Thanks in advance for the help.

mysql
1 Answers

Very naive way:

select t1.x as x1, t1.y as y1, t1.z as z1, t2.x as x2, t2.y as y2, t2.z as z2 
from coordinates t1
join coordinates t2 on sqrt(power(t2.x-t1.x, 2) + power(t2.y-t1.y, 2) + power(t2.z-t1.z, 2)) <= 1

For each coordinates (t1) query finds all other coordinates (t2) that dinstanced less or equal than 1 from each other.

But this query has very bad performance: O(n^2)

user_140224230
Related