8

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:-

enter image description here

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/… – GISKid Aug 5 '14 at 16:27
  • Are you married to using kriging in particular? Would another interpolation method work? – MakinFlippyFloppy Aug 7 '14 at 7:47
  • What do you mean by interpolation? Would average of all neighbours will be good value? – Jendrusk Aug 8 '14 at 9:45
  • Any interpolation method would be fine. – user1331131 Aug 11 '14 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? – Simbamangu Aug 12 '14 at 7:13
5
+100

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. – MakinFlippyFloppy Aug 8 '14 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 – Jendrusk Aug 8 '14 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..." – MakinFlippyFloppy Aug 8 '14 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 – Jendrusk Aug 8 '14 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. – MakinFlippyFloppy Aug 8 '14 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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