# Hex grid value interpolation in POSTGIS

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
)
``````
• Can you use kriging in postgis? Check out this thread gis.stackexchange.com/questions/1041/… Commented Aug 5, 2014 at 16:27
• Are you married to using kriging in particular? Would another interpolation method work? Commented Aug 7, 2014 at 7:47
• What do you mean by interpolation? Would average of all neighbours will be good value? Commented Aug 8, 2014 at 9:45
• Any interpolation method would be fine. Commented Aug 11, 2014 at 10:26
• Out of curiosity - why interpolate from the hex grid when you have values from a point set that you could use for interpolation? Commented Aug 12, 2014 at 7:13

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
)
``````
• The way your are calculating average would skew low because the numerator and denominator include zero-valued cells. From his problem description, it looks to me like his zero-valued cells are more appropriately viewed as nulls. Commented Aug 8, 2014 at 17:21
• @MakinFlippyFloppy - No? Denominator is always 6 to prevent divide-by-zero error. On the example there is 0 not null... hope I don't have to explain here that's not the same :) Even if there'll be six times zero interpolation one way or another will be zero Commented Aug 8, 2014 at 17:41
• Yes? From OP: "The shapes with '0' have no value and I'd like to interpolate these values from the values of the neighbors..." Commented Aug 8, 2014 at 17:47
• no value != 0, no value is null, so sth in this question is wrong... whether example or comment below it I wrote query looking at example, but even if 0 in example means null (It's like saying black and meaning white) you can always use coalesce() function Commented Aug 8, 2014 at 18:01
• Correct, 0 != NULL, but what OP wants is an example of a spatial interpolation problem. He or she wants to "fill in" or interpolate missing values -- which, as you correctly point out, are incorrectly modeled as zeroes -- based observations of known values. Your calculation of the local average increments the denominator for zero-valued (i.e., null) cells, thus skewing the interpolated value downward. Coalescing the numerator does nothing to fix this problem. Your code will also be wrong for ALL cells at the boundary of his or her data. Commented Aug 8, 2014 at 18:16