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.

• Moreau Colin: Thanks a lot, it works flawlessly and non of the ST_values returned null