Updating point by nearest neighbor 3Ddistance

Question

A follow on from this question: ST_3DClosestPoint returning multiple points

1) I have a xyz target point stored as a geom, I want to update the row with the 3Ddistance to the nearest obs point in another table. So find the nearest obs point and then update the target point with the distance.

expected result for the target point: id|geom|3Ddistance_To_Nearest_Point_In_Obs_Table

obs table: id|geom e.g. 100 records

2) To complicate matters, I also want to select n-neighbours from the obs table (lets say 10 for example) and calculate the average distance and update the target table.

expected target result: id|geom|average_3Ddistance

I've been trying to alter the former example, but no joy, any ideas?

Thanks

Btw. it is not a good practice to store redundant data - you will have too check and update it all the time when you update geom/insert new point/delete existing point. — Grzegorz Grabek
I've added examples of the expected result. The point collections are static, they will not have any new points added. — Spatial Digger

Grzegorz Grabek Grzegorz Grabek · Accepted Answer · 2018-10-13T00:41:36

If collections are static then you can CTAS (create table as select) your results instead of updating it.

create table new_table as
select t2.id, t2.geom, min(3ddistance) min_3DDistance, avg(3ddistance) avg_3ddistance
  from target t2,
  lateral (select t.id, st_3ddistance(o.geom, t.geom) 3ddistance
             from obs o, target t
            where t2.id=t.id
            order by st_3ddistance(o.geom, t.geom) limit 10) a
 group by t2.id, t2.geom;

or if you want to update

update target
   set (average_3ddistance, min_3ddistance)=(
  from (select id, min(3ddistance) min_3DDistance, avg(3ddistance) avg_3ddistance
          from (select t.id, st_3ddistance(o.geom, t.geom) 3ddistance
                  from obs o, target t
                 where t2.id=t.id
                 order by st_3ddistance(o.geom, t.geom) limit 10) a
         group by id) b
 where b.id=t.id;

Updating point by nearest neighbor 3Ddistance

1 Answers