Issues with raster data on postgresql / error:Attempting to get pixel value with out of range raster coordinates

Question

So here is the issue, i am trying to extract values from a big raster divided in 200 or so tiles and applying them to a selection. The points are startpoints, endpoints and midpoints from vector items, lines created using flow tools on SAGA. Everytime i try to run the query it returns me the following error:

NOTICE: Attempting to get pixel value with out of range raster coordinates: CONTEXT: fonction PL/pgsql st_value(raster,integer,geometry,boolean)

Here is my function so far, tried to use a "where" query in order to force him to read only the raster data where it intersects with my lines but that didn't fix a thing and the query failed.

SELECT

    tronc.id AS id,
    tronc.geom AS geom,
    ST_value(raster.rast, ST_startpoint(tronc.geom), TRUE) AS alt_startpoint,
    ST_value(raster.rast, ST_endpoint(tronc.geom), TRUE) AS alt_endpoint,
    ST_value(raster.rast, ST_line_interpolate_point(tronc.geom, 0.5), TRUE) AS mid_point,
    (ST_value(raster.rast, ST_startpoint(tronc.geom), TRUE) + ST_value(raster.rast, ST_endpoint(tronc.geom), TRUE) + ST_value(raster.rast, ST_line_interpolate_point(tronc.geom, 0.5), TRUE))/3 AS mean

FROM

    sinuosity.channels as tronc,
    rasters.tr_rge_alti_5m_2016 AS raster

WHERE

    ST_intesects(tronc.geom, raster.rast, 1)

Answer 1

This is not an error, this is a warning that you are trying to get the value for a point lying outside the raster area. It's not because a geometry ST_Intersects() with a raster that the ST_StartPoint() or the ST_EndPoint() of this geometry actually intersects with this raster, hence the warning.

If you want no failure for all requested point you must test exactly for that point in the WHERE clause. But you can query only for one point at a time. So instead of querying for the associated raster values in parallel you should query them in series a bit like this:

WITH startpoints AS (
  SELECT tronc.id AS id, 
         tronc.geom AS geom,
         ST_value(raster.rast, ST_StartPoint(tronc.geom), TRUE) AS startpoint
  FROM sinuosity.channels as tronc LEFT OUTER JOIN 
       rasters.tr_rge_alti_5m_2016 AS raster 
       ON (ST_Intersects(ST_StartPoint(geom), raster.rast, 1))
), endpoints AS (
  SELECT id, 
         geom,
         startpoint,
         ST_value(raster.rast, ST_EndPoint(geom), TRUE) AS endpoint
  FROM startpoints LEFT OUTER JOIN 
       rasters.tr_rge_alti_5m_2016 AS raster 
       ON (ST_Intersects(ST_EndPoint(geom), raster.rast, 1))
), midpoints AS (
  SELECT id, 
         geom,
         startpoint,
         endpoint,
         ST_Value(raster.rast, ST_Line_Interpolate_Point(geom, 0.5), TRUE) AS midpoint
  FROM endpoints LEFT OUTER JOIN 
       rasters.tr_rge_alti_5m_2016 AS raster 
       ON (ST_Intersects(ST_Line_Interpolate_Point(geom, 0.5), raster.rast, 1))
)
  SELECT id,
         geom,
         startpoint,
         endpoint,
         midpoint,
         (startpoint + mid_point + endpoint)/3 as mean
  FROM  midpoints;

Then you will have to decide what to do if some of those points are completely outsides the global extent covered by all your tiles in which case ST_Value() would return NULL.