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.

to add, there is no relationship whatsoever like an ID, between columns of Table A and B — domiho1, Aug 13 '22 at 17:55
Please provide proper table definitions as CREATE TABLE scripts. That's the canonical form to define a table, and we can use it directly to run tests. Ideally provide a fiddle. And always declare your version of Postgres. — Erwin Brandstetter, Aug 14 '22 at 21:28

score 2 · Accepted Answer · answered Aug 13 '22 at 22:52

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);

How to SELECT "closest" rows from another table?

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

1 Answers1