Select nearest 3D point with sfcgal

The Data

Two sets of 1000 points A.geom and B.geom in PostGIS

The Problem

1. From Set A take the first point and find its nearest neighbour in Set B
2. Calculate the 3D distance between these two points
3. Save the result to the dist column of Set A

I've been testing out various python libraries, I thought I had it working in shapely just to discover it ignores the z-dimension.

I think SFCGAL is the way to go, but I am inexperienced, is this a simple query, can anyone help me with this?

• It looks like I will need the ST_3DDistance() – Spatial Digger Oct 2 '18 at 10:53
• I'm also looking into numpy – Spatial Digger Oct 2 '18 at 11:22
• The answer given by @ThingumaBob to your question yesterday, using a LATERAL JOIN with the <<->> operator should also do the trick here. You just set LIMIT 1 in each lateral query, so you get the nearest point to every other point. – John Powell Oct 2 '18 at 11:41
• Thanks, I'll return to that solution, I'm circling a bit with this one. – Spatial Digger Oct 2 '18 at 12:08
• You don't need SFCgal. There is also native PostGIS functions for ST_3DDistance and other distance related functions. – Nicklas Avén Oct 2 '18 at 14:08