|
|
|
|
| Select 3D points from an SQL database based on range |
|---|
|
Problem:
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
|
|
Solution:
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.
|
|
|
|
|
|
This is free technical advice without any warranties.
Use at your own risk. Solutions offered may not apply to your particular problem at all.
Opinions and views expressed in the articles are the authors' personal
and may not necessarily reflect or coincide with those of Ano-Tech Computers.
We take no responsibility if anything bad happens from following any of
the intructions given. If you don't understand and accept this, please
contact us for personal assistance instead.
To read our full disclaimer, go
here.
We welcome anyone who is willing to contribute to this public knowledge base,
contact siteadmin@atc.no
if you have information you would like to share. The idea is not to replace
the commercial support sites, but to publish those hard-to-find
solutions you've found yourself looking for over and over again.
Show all articles
