Re: Nearest point problem
Wolfram Kaiser wrote:
>
> Hi everyone,
>
> I have a very large table with following columns:
>
> pid integer, loc Pnt, direction char(1)
>
> The points represent two tracks parallel to the x,y axis that cross
> over once. The letters a or d are used in direction to specify to
> which track a particular point belongs.
> The task is to retrieve the pair of points from different tracks with
> the minimum distance apart in a SQL script .
> Perhaps it could help to use the 2d datablade to find the fastest
> solution.
Knowing almost nothig about datablades but certain there is a distance
function:
First, a query to determine the shortest distance to separate a point
from line1 from line2:
select min(distance(p1.loc, p2.loc)
from point_table p1, point_table p2;
That was a warmup. Now use it the above in a where clause:
select pt1.pid, pt1.loc, pt1.direction
pt2.pid, pt2.loc, pt2.direction
from point_table pt1, point_table pt2
where pt1.direction < pt2.direction -- Don't test point against self
and distance(pt1.loc, pt2.loc)
= (select min(distance(p1.loc, p2.loc)
from point_table p1, point_table p2);
Does this make sense?
I've done this for the fun of it, seeing nobody has responded in 2 days.
Any corrections: please post. I'm willing to stick my neck out in order
to learn something new.
--
-- Jake (Never yelled "CROWDED THEATER!" during a fire)
+------------------------------------------------------------+
| The expedient performance of a task with excessive concern |
| regarding its duration-to-completion engenders a virtual |
| certainty of diminished benefit therefrom. |
| -- Benjamin Franklin (but he said it in 3 words) |
+------------------------------------------------------------+