Nearest point on line from a point in PostGIS and its attribute value

Question

Consider the following image;

I have points (shown in blue circles) stored in my database with their geometry and an attribute value. Additionally, I have another table with User Points (yellow rhomboids). I want to get the projection of user points on the path line of the vehicle points on the basis of their proximity to path line and also on those project points on line, I want to interpolate the value of attribute values of the nearby vehicle points.

So far, I have been able to get the points as shown in yellow circle using ST_ClosestPoint but currently I am unable to proceed further to get the interpolated values of the attribute and assign it to the yellow circles.

Answer 1

If you intend on running this over and over, have possible other/future tasks that need edges to work with or those tables are very large, I recommend to create a properly indexed edges table with src_id&trg_id and src_val&trg_val (ids and values, name them as you like of course) of their connecting Vehicle Points. Using these columns, it will be easy to interpolate from one connected point's value to the other by getting a fraction of their value's difference.

I will assume you have those edges stored as such a table and with the above naming; running

SELECT up.id,
       edg.src_val + (edg.trg_val - edg.src_val) * ST_LineLocatePoint(edg.geom, up.geom) AS int_val,
       ST_ClosestPoint(edg.geom, up.geom) AS geom
FROM <user_points> AS up
JOIN LATERAL (
  SELECT src_val,
         trg_val,
         geom
  FROM edges
  ORDER BY up.geom <-> geom
  LIMIT 1
) AS edg
ON true;

will return the <user_points>.id, the projected point geom on the closest edge of each point and the interpolated value int_val (interpolated based on the fraction of line length from the start at which the closest point was projected). Add <user_points> columns as you need in the outer query.
Using the <-> KNN operator, this will also make excellent use of the spatial index on both tables.

Update:
Based on your info, you can create the edge table with

CREATE TABLE edges AS
  SELECT ROW_NUMBER() OVER() AS id,
         edgs.*
  FROM (
    SELECT id AS src_id,
           LEAD(id) OVER(ORDER BY id) AS trg_id,
           val AS src_val,
           LEAD(val) OVER(ORDER BY id) AS trg_val,
           ST_MakeLine(geom, LEAD(geom) OVER(ORDER BY id)) AS geom
    FROM points_from_video
  ) AS edgs
  WHERE edgs.geom IS NOT NULL;

ALTER TABLE edges ADD PRIMARY KEY (id);

CREATE INDEX sidx_edges_geom
  ON edges
  USING GIST (geom);

Can't test right now, not 100% sure if ST_MakeLine returns NULL or throws an error if the last row is processed and no leading row was found. Give a shout if you encounter an error.