On 03/09/13 17:48, Steve Holdoway wrote: > I'm trying to select data from a Mysql database ordered by the closest > (x,y) coordinates to a point ( all 2 dimensional, no oblate spheroids > here to complicate stuff ). Problem is that there's a lot of data in > there, so selecting and ordering by the length of the hypotenuse from > the requested point is going to bring it to it's knees - running > functions on a full table scan. > > At the moment, I'm taking the top 10 closest as ordered by the sum of > the absolute x and y differences,
Depending on the data, this might not contain the closest point. E.g. (1,0) is further away than (0.6, 0.6) but 1+0 < 0.6+0.6 Is it worse to use xdiff * xdiff + ydiff * ydiff ? I.e. the hypotenuse squared. Replaces abs with multiplication, but give result truly correctly ordered. > and then re-ordering that list by the > length of said hypotenuse to get the 'real' closest one. I'm still using > a filesort, but at least no trig functions on the database. If you don't need to display the distance, the square of the hypotenuse is enough for ordering (ie. x^2 + y^2), so you can avoid the square root. > > Does anyone have a better idea? Although this seems to work ok, it's is > a bit rough and ready... > > Note: mysql and php. It's the best i can do (: > > Steve > _______________________________________________ Linux-users mailing list [email protected] http://lists.canterbury.ac.nz/mailman/listinfo/linux-users
