Select 3D points from an SQL database based on range

[ATC]

Given a table "universe" with the fields "id", "x", "y" and "z", select all points with a distance lower than or equal to $range from the point described by $x,$y,$z

[ATC]

Use Pythagoras' theorem for calculating the distance between two points in 3D space:

dx = Ax-Bx
dy = Ay-By
dz = Az-Bz
distance = sqrt((Ax-Bx)**2 + (Ay-By)**2 + (Az-Bz)**2)

Example SQL statement:

SELECT id, SQRT(POWER(x-$x,2)+POWER(y-$y,2)+POWER(z-$z,2)) AS distance
FROM universe
HAVING distance <= $range
ORDER BY distance

Be warned... for large tables, this query is a tough one. If you don't really need the distance returned from the SQL server and the order is insignificant, you can optimize the query like this:

SELECT id, x, y, z
FROM universe
WHERE x-$x <= $range
AND y-$y <= $range
AND z-$z <= $range
AND SQRT(POWER(x-$x,2)+POWER(y-$y,2)+POWER(z-$z,2)) <= $range

The first three tests are a LOT cheaper and will quickly rule out any point that lies outside the range along any of the three axes x, y and z.