0

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.

2
  • to add, there is no relationship whatsoever like an ID, between columns of Table A and B
    – domiho1
    Aug 13, 2022 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. Aug 14, 2022 at 21:28

1 Answer 1

2

That's a 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:

Recommended reading:

About the LATERAL join:

1
  • thanks, really worked! plus the additional links. Next time will provide table definitions and Postgres version. Fiddle, need to look what is...
    – domiho1
    Aug 15, 2022 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.