Forums.ATC.no

Teknisk => Generelt teknisk => Emne startet av: ATC på 27. ſeptember 2008, 18:24 pm

Tittel: Select 3D points from an SQL database based on range
Skrevet av: ATC på 27. ſeptember 2008, 18:24 pm

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

Tittel: [Solved] Select 3D points from an SQL database based on range
Skrevet av: ATC på 27. ſeptember 2008, 18:24 pm

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.