August 9th, 2003, 07:24 PM

SQL, sphere, coords
Table of coords: id,x,y,z
124424352345
2435550
33234455
...
How to select all rows where all coords are in the sphere? E.g. if we have sphere with center at position 0:0:0 and radius of 500, only 2nd and 3rd row will be returned.
Thanks.
August 11th, 2003, 05:16 PM

return those with x squared plus y squared plus z squared less than r squared (25,000)
if speed / efficiency is important then you can first refuse anything with x greater than r, y greater than r or z greater than r and accept anything with (i think  you'll have to verify this yourself) x, y or z squared less than one third of r etc.
August 11th, 2003, 06:57 PM

August 11th, 2003, 07:01 PM

you're welcome!
note: the final line above should read "less than one third of r squared etc." not "less than one third of r etc."
August 11th, 2003, 07:51 PM

Thans again for the idea. I have found couple mistakes.
At first, we should take absolute values
abs(x) < 500 AND abs(y) < 500 AND abs(z) < 500
AND pow(x,2) + pow(y,2) + pow(z,2) < 500
And maybe note should be fixed, because:
322 r=4
3^2=9 not less than 4/3
maybe 3^2 should be less than 4/3?
August 11th, 2003, 07:58 PM

Just finished.
abs(x) < 500 AND abs(y) < 500 AND abs(z) < 500
AND sqrt(abs(x)) < 500/3 AND sqrt(abs(y)) < 500/3 AND sqrt(abs(z)) < 500/3
AND pow(x,2) + pow(y,2) + pow(z,2) < 500
Unfortunatelly, pow(x,2) + pow(y,2) + pow(z,2) < 500 if enough to select all results and other conditions are optional and don't give any processing power.
August 12th, 2003, 04:03 AM

ok, here it is in c syntax if that's easier for you to read:
Code:
bool hitTest(int x,int y,int z,int r)
{if (x<0) {x=x;}
if (y<0) {y=y;}
if (z<0) {z=z;}
if (r<x) {return false;} // outside outer / containing cube
if (r<y) {return false;} // outside outer / containing cube
if (r<z) {return false;} // outside outer / containing cube
x*=x;y*=y;z*=z;r*=r;
if ((3*x<r) // leave this bit out as is, or optimise it and include
&&(3*y<r)
&&(3*z<r)) {return true;} // inside inner cube (cube's not ideal shape here...)
return (x+y+z<r);
}
Last edited by epl; August 12th, 2003 at 04:18 AM.
August 12th, 2003, 04:25 AM

this also uses a rotated cube (ie like a diamond) as a second inner shape, for illustration
Code:
bool hitTest(int x,int y,int z,int r)
{if (x<0) {x=x;}
if (y<0) {y=y;}
if (z<0) {z=z;}
if ((x+y+z<r) {return true;} // inside inner diamond
if (r<x) {return false;} // outside outer / containing cube
if (r<y) {return false;} // outside outer / containing cube
if (r<z) {return false;} // outside outer / containing cube
x*=x;y*=y;z*=z;r*=r;
if ((3*x<r) // leave this bit out as is, or optimise it and include
&&(3*y<r)
&&(3*z<r)) {return true;} // inside inner cube
return (x+y+z<r);
}