### Thread: SQL, sphere, coords

1. No Profile Picture
Junior Member
Devshed Newbie (0 - 499 posts)

Join Date
Aug 2003
Posts
16
Rep Power
0

#### SQL, sphere, coords

Table of coords: id,x,y,z

1|2442|-4352|345
2|-435|55|0
3|3|234|-455
...

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.
2. No Profile Picture
epl
Contributing User
Devshed Newbie (0 - 499 posts)

Join Date
Mar 2001
Location
Dublin
Posts
413
Rep Power
14
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.
3. No Profile Picture
Junior Member
Devshed Newbie (0 - 499 posts)

Join Date
Aug 2003
Posts
16
Rep Power
0
Thanks!
4. No Profile Picture
epl
Contributing User
Devshed Newbie (0 - 499 posts)

Join Date
Mar 2001
Location
Dublin
Posts
413
Rep Power
14
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."
5. No Profile Picture
Junior Member
Devshed Newbie (0 - 499 posts)

Join Date
Aug 2003
Posts
16
Rep Power
0
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:
3|-2|-2 r=4
3^2=9 not less than 4/3
maybe 3^-2 should be less than 4/3?
6. No Profile Picture
Junior Member
Devshed Newbie (0 - 499 posts)

Join Date
Aug 2003
Posts
16
Rep Power
0
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.
7. No Profile Picture
epl
Contributing User
Devshed Newbie (0 - 499 posts)

Join Date
Mar 2001
Location
Dublin
Posts
413
Rep Power
14
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.
8. No Profile Picture
epl
Contributing User
Devshed Newbie (0 - 499 posts)

Join Date
Mar 2001
Location
Dublin
Posts
413
Rep Power
14
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);
}