On Tue, 2013-09-03 at 19:35 +1200, Eliot Blennerhassett wrote: > 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 > > > Works a treat, and there doesn't seem to be any performance penalty.
Many thanks, Steve -- Steve Holdoway BSc(Hons) MIITP http://www.greengecko.co.nz Linkedin: http://www.linkedin.com/in/steveholdoway Skype: sholdowa _______________________________________________ Linux-users mailing list [email protected] http://lists.canterbury.ac.nz/mailman/listinfo/linux-users
