3

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
6

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:

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .