Hex grid value interpolation in POSTGIS

Question

I have a table of tessellated hexagons that I created as a hex binning grid to cover an area.

I've then overlaid a point set to give some of the hexagons a value, see diagram:-

The shapes with value "0" have no value and I'd like to interpolate these from the values of the neighbours as per Kriging. How can I accomplish this in postgres given my table looks like:-

hex_grid_data
(
 gid SERIAL,
 wkb_geometry (geometry),
 value INTEGER
)

Answer 1

I don't know what kind of interpolation you're talking about but if average of all neighbours will be good value this could be the solution:

create table hex_grid_data_av as 
  (
  select gid, wkb_geometry, value,
    case
      when value > 0 then value
      else (select sum(h2.value)/6 from hex_grid_data h2 where ST_Touches(h1.wkb_geometry, h2.wkb_geometry))
    end as int_value 
  from hex_grid_data h1
  )

Or following @MakinFlippyFloppy doubts if really 0 in example means null (no value):

create table hex_grid_data_av as 
  (
  select gid, wkb_geometry, value,
    case
      when value > 0 then value
      else (select sum(coalesce(h2.value,0))/6 from hex_grid_data h2 where ST_Touches(h1.wkb_geometry, h2.wkb_geometry))
    end as int_value 
  from hex_grid_data h1
  )

Or if null or zero values should not decrease average:

create table hex_grid_data_av as 
  (
  select gid, wkb_geometry, value,
    case
      when value > 0 then value
      when value = 0 and not exists (select 1 from hex_grid_data h2 where ST_Touches(h1.wkb_geometry, h2.wkb_geometry) and h2.value != 0 and h2.value is not null ) then 0
      else (select sum(coalesce(h2.value,0))/(select count(nullif(value,0)) from hex_grid_data h3 where ST_Touches(h1.wkb_geometry, h3.wkb_geometry) ) from hex_grid_data h2 where ST_Touches(h1.wkb_geometry, h2.wkb_geometry))
    end as int_value 
  from hex_grid_data h1
  )