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
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
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 (
ORDER BY up.geom <-> geom
) AS edg
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.
<-> KNN operator, this will also make excellent use of the spatial index on both tables.
Based on your info, you can create the edge table with
CREATE TABLE edges AS
SELECT ROW_NUMBER() OVER() AS id,
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
) AS edgs
WHERE edgs.geom IS NOT NULL;
ALTER TABLE edges ADD PRIMARY KEY (id);
CREATE INDEX sidx_edges_geom
USING GIST (geom);
Can't test right now, not 100% sure if
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.
ST_LineLocatePointreturns the fraction [0, 1] of linelength of the projected point (i.e. sort of one step further than
ST_ClosestPointand skipping the geometry creation), which you could use directly as percentage to interpolate between the two Vehicle Points attributes. wrap the nearest line search in a
LATERAL JOINquery (i.e. (K) Nearest Neighbor (KNN).