Automatically Snapping Points to Closest Part of Line

Question

I have some line points representing manholes. I also have line features representing roads. Both of these features are currently living in a PostGIS database. I'd like to automatically move these points to the closest part of the line, so all of the points are arranged in a straight line.

Is there an appropriate PostGIS function that does this? Or, a function in QGIS?

Edit: I've found the "Snap Geometries to Layer" function in QGIS 3.0 works great for this, but I don't want to create another layer with the straightened points, I want to update the geometry of the existing points. Any way around this?

Answer 1

To permanently update the point geometries on the database level, run

UPDATE
  <point_table> AS pt
SET
  <geometry_column> = (
    SELECT
      ST_ClosestPoint(ln.<geometry_column>, pt.<geometry_column>)
    FROM
      <line_table> AS ln
    -- WHERE
    --  pt.<point_attribute> = ln.<line_attribute>
    ORDER BY
      pt.<geometry_column> <-> ln.<geometry_column>
    LIMIT
      1
  );

in either the DB Manager in QGIS or pgAdmin (or the client of your choice).

This will find the closest line to each point and projects the closest point on that line to the given point as the new geometry, effectively 'snapping' them to the closest line.
(Note: in your picture, the point at the lower left will be snapped to the closest line, so to the one it kind of sits on already; if that is undesireable, you can specify an attribute of the line table to match any of the point's to only snap to those lines, see -- WHERE ... above; uncomment that line if you need or delete it).
This will make use of the spatial index on the lines table <geometry_column>.

After the update, I suggest to reindex the table and run VACUUM ANALYZE <point_table> to update the table statistics, as part of standard maintenance.