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.

  • cross join lateral on the 2 tables -- in the lateral subquery find the 4 closest points and only return the temperature values to the outer query – ziggy Feb 5 at 20:39
  • @ziggy could you please provide the sample code since I am new in post gis, so it is difficult for me to implement the theory :) – Behzad Feb 5 at 20:43
  • do you need the average of the 4 closest points? – ziggy Feb 5 at 20:43
  • @ziggy Yes, I need the avg. – Behzad Feb 5 at 20:44

this should do the trick -- just replace your tables and columns where needed

SELECT a.id,
       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

  • if I want to calculate the weighted average based on the distance what theoretically do then? – Behzad Feb 5 at 21:21
  • not exactly sure I know what you're asking but i'd probably ask another question – ziggy Feb 5 at 21:21
  • I would also recommend editing your question to include that you want the average. seems like some people want to close your question. normally the questioner shows some attempt with code but your question seemed clear enough for me... – ziggy Feb 5 at 21:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.