How to efficiently fetch multiple ST_Value from PostGIS raster(s)

Question

I have a raster table with index created by raster2pgsql

CREATE TABLE tbl (rid serial PRIMARY KEY, rast raster);
CREATE INDEX ON tbl USING gist (st_convexhull(rast));

I can see the best way to fetch a single point value is

SELECT ST_Value(rast, :point) FROM tbl WHERE ST_ConvexHull(rast) ~ :point;

However, given the structure and the functions I can see, it's not obvious how to best fetch e.g. 1000 point values at once. Neither from a single tile, not if they span across tiles.

I had an initial try with MULTIPOINT geometry, but they don't work with ST_Value, and I didn't know where to start with the WHERE clause.

Answer 1

This should trivially be

SELECT ST_Value(r.rast, pt.geom)
FROM   <raster> AS r
JOIN   <points> AS pt
  ON   ST_Intersects(r.rast, pt.geom)
;

where <points> can be any table expression you can think of.

Answer 2

To account for repeated points, boundaries, and overlapping or missing rasters, this was my final solution:

SELECT (array_agg(ST_Value(r.rast, pt.geom) ORDER BY r.rid DESC)
        FILTER ( WHERE ST_Value(r.rast, pt.geom) IS NOT NULL ))[1] 
FROM tbl AS r 
  RIGHT JOIN ST_Dump(:multipoint) AS pt 
  ON ST_ConvexHull(r.rast) ~ pt.geom
GROUP BY pt.path;