I have a table of about 90k points that represent yield values in two farm fields, and I am sampling values from a table of rasters. The raster table is a collection of many ndvi rasters, each clipped to a farm field boundary. There is a column 'year' and for each farm field there is only one ndvi raster per year.

When I use this query to sample the raster, I get about 14k points that are not assigned a value:

UPDATE pnt_table
SET ndvi_2014 = ST_Value(rast, geom)
FROM ndvi_table
WHERE ndvi_table.year = 2014 AND ST_Intersects(rast, geom);

Then running the following query confirms that over 14k points assigned 'NaN':

SELECT COUNT (*) FROM pnt_table
WHERE ndvi_2014 = 'nan';

Viewing this raster and the points in QGIS confirms this is not correct, as there are many points that lie over pixels that have values. It is interesting that all the points assigned 'nan' are clustered in one part of the field.

However, by creating a view and sub-setting the rasters according to the two fields I am working in, I can get the expected results:

CREATE OR REPLACE VIEW raster_subset_view AS
SELECT * FROM ndvi_table
WHERE field_name = 'field1' OR field_name = 'field2';

Now re-running the UPDATE line:

UPDATE pnt_table
SET ndvi_2014 = ST_Value(rast, geom)
FROM raster_subset_view
WHERE raster_subset_view.year = 2014 AND ST_Intersects(rast, geom);


SELECT COUNT (*) FROM pnt_table
WHERE ndvi_2014 = 'nan';

Now all records have been assigned the expected value and there are no 'Nan' values.

This doesn't make any sense to me. It's not a huge deal to just create a view each time I need to do this, but I don't see why I need to. Does anybody have an idea of what might be happening?

  • what do those actual raster values look like when you select them? – ThingumaBob Mar 31 '18 at 8:28
  • @ThingumaBob the values are floats ranging from 0 to 1. – pdavis Mar 31 '18 at 14:12

Your Answer

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

Browse other questions tagged or ask your own question.