#1
  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. #2
  3. No Profile Picture
    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.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    16
    Rep Power
    0
    Thanks!
  6. #4
  7. No Profile Picture
    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."
  8. #5
  9. 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?
  10. #6
  11. 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.
  12. #7
  13. No Profile Picture
    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.
  14. #8
  15. No Profile Picture
    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);
     }

IMN logo majestic logo threadwatch logo seochat tools logo