Posted by Erland Sommarskog on 05/07/06 19:54

(bevanward@gmail.com) writes:
> I have a large data set of points situated in 3d space. I have a simple
> primary key and an x, y and z value.
>
> What I would like is an efficient method for finding the group of
> points within a threshold.
>
> So far I have tested the following however it is very slow.
>
> ---------------
> select *
> from locations a full outer join locations b
> on a.ID < b.ID and a.X-b.X<2 and a.Y-b.Y<2 and a.Z-b.Z<2
> where a.ID is not null and b.ID is not null
> ---------------

As indicated by Celko's posting there is most probably an error here:
you need abs(a.X - b.X) etc, else you will get far too many points.

Mathematically it would also make more sense to use

sqrt(square(a.X - b.X) + square(a.Y - b.Y) + square(a.Z - b.Z)) < cutoff


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: locating set of points close to one another (within a threshold)
• Prev in forum: Re: return into one string
• Thread view: Re: locating set of points close to one another (within a threshold)

[Reply to this message]