2

I have two tables: point1 and point2. For each point from table point1, I want to find the closest point from table point2.

For example: For point with id=1 from table point1, the closest point from table point2 has id (?? I want to get that id).

I tried to use ST_ClosestPoint but but it did not work properly. My code:

SELECT ST_ClosestPoint(pont2.geom, (SELECT point1.geomxy WHERE point1.id=1))
FROM pint2, point1
1
  • 1
    You misspelled "point2" two different ways in the provided SQL. What error message did you receive, and how did it "not work properly"? Please Edit the question. – Vince Jun 14 at 10:06
5

This is referred to as (spatial) (K)NN search; the most performant approach is a LATERAL sub-query in conjunction with the index driven <-> operator:

SELECT a.id,
       b.id,
       -- a.geom / b.geom
FROM   <points1> AS pt1
CROSS JOIN LATERAL (
  SELECT id
  FROM   <points2>
  ORDER BY
         geom <-> a.geom
  LIMIT  1
) AS b
;

I recommend to further read on multiple topics concerning (K)NN queries, to understand LATERAL queries, distances and index usage.

Related:

Your Answer

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

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