PostGIS, closest point to another point

Question

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

Answer 1

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:

• Nearest Neighbor
• PostGIS: ST_ClosestPoint join error
• Optimizing my script; taking too long; distance between many objects PostGIS
• Speedup function to calculate distance from centroid of building to river segment in PostgreSQL