ST_ClosestPoint() moves all points to one line instead of closest line

Question

I have a line network and I've created evenly-spaced points along those lines. The problem is that the points are not exactly on the line so subsequent overlay operations will not work as expected. I've tried to move the points to the nearest place on the lines, but the result is that I end up moving ALL the points in the table to only one line, and it is not the nearest line to most of the points. I have a couple different queries with the same result. Here is one sample:

SELECT pt.id, ST_ClosestPoint(a.geom, pt.geom) geom
FROM pt, (
SELECT line.geom 
FROM line, pt
ORDER BY line.geom <-> pt.geom
LIMIT 1) a

Anyone know why my points are being moved to one (often far-away) line, instead of the closest lines?

Answer 1

The inner query (a) will likely always return the same result, as it is computing the distance between all lines and all points and return the line being the closest to any point.

To fix it, you need to use a lateral join that will find the line being the closest to the point of interest (so, for each row, to only one point). Note that there is only a single reference to the pt table

SELECT pt.id, ST_ClosestPoint(a.geom, pt.geom) geom
FROM pt
CROSS JOIN LATERAL (
  SELECT line.geom 
  FROM line
  ORDER BY line.geom <-> pt.geom
  LIMIT 1) a;