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);