ST_3DClosestPoint返回多个点 [英] ST_3DClosestPoint returning multiple points

问题描述

数据

我有2张桌子 -3D点几何(obs.geom)，n = 10 -一个3D点(target.geom)，n = 1

I have 2 tables - 3D point geometries (obs.geom), n=10 - a single 3D point (target.geom), n=1

问题-第1部分

当我运行以下代码时，它将列出所有10种obs geoms，而不仅仅是最接近target.geom的点.谁能给我一些指示?

When I run the following code it lists all 10 of the obs geoms rather than just the closest point to the target.geom. Can anyone give me some pointers?

SELECT ST_3DClosestPoint(target.geom, obs.geom)
FROM target, obs

第2部分

然后我要添加Distance3D

I then want to add in the Distance3D

SELECT ST_3DDistance(ST_3DClosestPoint(target.geom, obs.geom) as dist_closest, obs.geom) as distance
FROM target, obs
where dist_closest > 1.5

推荐答案

我们不能使用knn运算符(它仅适用于2D)，因此我们必须解决一些问题. 对于目标表中的单个点，将是这样.

We cannot use knn operator(it works only with 2D) so we have to work around a bit. For a single point in the target table it will be like this.

select * , st_distance(o.geom, t.geom), st_3ddistance(o.geom, t.geom)
  from obs o, target t
 order by st_3ddistance(o.geom, t.geom) limit 1

但是，如果您一次想要多个目标的结果，它将不起作用.如果您想找到许多目标的最近点，那么我们必须使用横向连接

But it will not work if you want results for many targets at once. If you want find closest points for many targets then we have to use lateral join

select t2.*, a.*
  from target t2,
  lateral (select o.*
             from obs o, target t
            where t2.id=t.id
            order by st_3ddistance(o.geom, t.geom) limit 1) a

如果您想要更多，那么只需增加一个最接近的点即可.

If you want more then one closest point just increase the limit.

这篇关于ST_3DClosestPoint返回多个点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！