Speeding up ST_Value in PostgreSQL?

Question

I am working on a large point file and am looking to get the raster value underneath each point. I am using:

UPDATE Locations a SET image3b3_sum = ST_Value(rast, 1, geom)
FROM image_sum_sum;

10 records takes 31 seconds and 1,000 takes 5:24 minutes. As I add more points I am noticing a linear trend on how long this will take, and for 1,000,000 points this will get nuts.

I have tried tiling the raster however it results in NULL values being assigned. What can I do to speed this up? I have looked at: How to speed up queries for raster databases? but, as I mentioned, tiling causes null values to occur:

NOTICE:  Attempting to get pixel value with out of range raster coordinates: (-2638, -2441)
CONTEXT:  PL/pgSQL function st_value(raster,integer,geometry,boolean) line 18 at RETURN

The raster and point file have indexes. Are there constraints I can add to the raster to make it go faster?

UPDATE_1:

I have tried adding in an ST_Intersects as suggested but still yielding NULL values and long times to process:

UPDATE Locations a SET image3b3_sum = ST_Value(ST_Tile(rast,1,100,100), 1, geom)
FROM image_sum_sum b
WHERE ST_Intersects(a.geom, b.rast);

Null values still persist as before. It is like it is loading all points and testing each against each tile. Would a FOR loop with RID as the agent and ST_Envelope of each tile be a solution? — D_C, Nov 03 '18 at 19:18
Taking the Tile out of the query and using the ST_Intersects resulted in 19s for 100,000 records with no NULL values!! Please put that as the answer. — D_C, Nov 05 '18 at 15:50

Andre Silva · Accepted Answer · 2018-11-05T16:27:44.170

Import the raster with raster2pgsql using the -t TILE_SIZE as suggested in djq's answer to How to speed up queries for raster databases?, instead of tiling it on the fly with ST_Tile (for memory efficiency). Every tile will be inserted in a row in the raster table (image_sum_sum).

Then, as suggested in apricity's answer to Only select non null values using st_value on Postgis tiled raster, use the ST_Intersects in the WHERE clause to take advantage from the gist index in the geometry table (locations).

UPDATE locations a SET image3b3_sum = ST_Value(b.rast, 1, a.geom)
FROM image_sum_sum b
WHERE ST_Intersects(a.geom, b.rast);

Speeding up ST_Value in PostgreSQL?

1 Answers1