2

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 3 '18 at 19:18
  • 1
    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 5 '18 at 15:50
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.