There are two-point tables. One includes temperature value and the other does not have this information. I would like to calculate the average of the temperature for the 2nd table based on the four closest points around the first ones. I wonder how can I do it in PostGIS.
this should do the trick -- just replace your tables and columns where needed
SELECT a.id, a.geom, avg(c.temp_val) temp_val FROM tablea a CROSS JOIN lateral (SELECT temp_val FROM tableb b ORDER BY b.geom <-> a.geom LIMIT 4)c GROUP BY a.id,a.geom
the idea is to take table a (outer table) 1 record at a time and find the 4 closest points for that record in the cross join subquery. the cross join lateral is bit tricky to explain but in a way it kind of functions like a for loop. for a better explanation on lateral refer to the answer in this question https://stackoverflow.com/questions/28550679/what-is-the-difference-between-lateral-and-a-subquery-in-postgresql