Add row from table B based on minimum distance between table A and B

Question

I am a beginner with PostgreSQL and have two tables A and B, both with multiple columns, including a point column (geom):

TableA includes a given 'ID', geom, etc , TableB is a "DB" of locations with a 'name' column, geom, etc...

Need to find for each TableA.id what is the the 'name' (row columns) and distance of the closest location in TableB.

already could calculate the distance between all points of TableA and TableB with :

ST_DistanceSpheroid(pointA,pointB,[SPHEROID])

But the best could build so far, is a select query where provides for each TAbleA.ID all distances and respective names from TableB - does not find the minimum for each ID only.

While needed to have the new calculated columns (distance and name) added to Table A.

It is not yet clear to me how to work with data output when using "select" vs an actual table data output. or use of views.

Answer 1

That's a nearest-neighbor classic. The basic solution:

SELECT *
FROM   tableA a
CROSS  JOIN LATERAL (
   SELECT b.name, b.geom <-> a.geom AS dist
   FROM   tableB b
   ORDER  BY dist
   LIMIT  1
   ) b;

Be sure to have a spatial GiST index on tableB(geom):

CREATE INDEX b_geom_gist_idx ON tableB USING GIST (geom); 

Related:

• How to SELECT "closest" rows from another table?

Recommended reading:

• https://postgis.net/workshops/postgis-intro/knn.html
• The manual about the distance operator <->

About the LATERAL join:

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?