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

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 http://gis.stackexchange.com/questions/1041/open-source-methods-for-kriging/1072#1072 — GISHuman, Aug 05 '14 at 16:27
Are you married to using kriging in particular? Would another interpolation method work? — MakinFlippyFloppy, Aug 07 '14 at 07:47
What do you mean by interpolation? Would average of all neighbours will be good value? — Jendrusk, Aug 08 '14 at 09:45
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 07:13
Hex grid is used to colour the area and it performs well in real time for map rendering. I'd like to interpolate and create a nice raster heat map in the future but suspect performance will be slow — user1331131, Aug 12 '14 at 10:29

Jendrusk · Accepted Answer · 2014-08-08T21:13:08.723

5

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
  )

edited Aug 08 '14 at 21:13

answered Aug 08 '14 at 09:56

Jendrusk

2,617
9
16

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 08 '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 08 '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 08 '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 08 '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 08 '14 at 18:16
It depends of what OP wants. Question is quite unclear so maybe we'll let him decide if it's good or bad direction :) – Jendrusk Aug 08 '14 at 18:34
@MakinFlippyFloppy - added third version where null or zero values are not decreasing avg. Maybe that's the point – Jendrusk Aug 08 '14 at 21:16
The assertion that 0 = null is correct. I thought the question was quite clear about this as I stated that 0 has no value. Maybe to be ABSOLUTELY clear, I wish to interpolate a value for each cell that has a value of 0. I am not sure if this answer is correct as it only interpolates for touching cells. – user1331131 Aug 11 '14 at 10:25
If this method of interpolation is OK, only trouble is left null (0) values after first approach you can do it in loop while exists select 1 from foo where value = 0 – Jendrusk Aug 11 '14 at 17:30

Hex grid value interpolation in POSTGIS

1 Answers1