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
  • 1
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.