How to interpolate missing data from neighbours in PostGIS?

[المشرف العام]

I am a bit surprised this hasn't been asked before and googling did not help me either. So go ahead and flag as duplicate if I just did not use the right vocabulary...

I have a dataset with point data within a given country. Let's say my dataset looks somewhat like this:

tree_id | species | age | height |*geom--------------------------------------- 0 | Ash | null| 9.5 | ... 1 | Beech | 70 | 20.0 |*... 2 | Ash | 10 | 7.2 | ... 3 | Beech | 70 | null |*... 4 | Beech | null| 20.0 |*... 5 | Beech | 60 | 22.0 |*... ... | ... | ... | ... |*...As you can see the dataset has some missing data. For instance, tree_id 0 has no age. Therefore I would like to interpolate those missing values from the

1. 100 nearest neighbours and also the
2. neighbours within 100 meters radius.

In both cases I would like to have a mean and also median for the same species. 1. should also give the resulting radius and 2. the number of sample trees used. A result table could then look like this (query 2.):

tree_id | age_avg | age_median | height_avg |*height_median | samples----------------------------------------------------------------------- 0 | 11.8 | 10.5 | 19.7 | 22.6 | 113 3 | 12.2 | 12.4 | 12.7 | 12.6 | 97 5 | 50.7 | 53.1 | 32.0 |* 35.7 | 272 ... | ... | ... | ... |* ... | ...Could you get me started with some PostgreSQL query code, please?
If you have better ideas how to fill those data gaps, I would also be grateful to hear those.



أكثر...