Raster and Geometry ST_Intersects slows when add buffer in PostGIS

Question

I have a table containing rasters for the whole of the UK, the below query is very fast (0.1 seconds) and gets all of the rasters that are intersected by the line between a start point and an end point:

SELECT ST_UNION(rast) INTO elevationdata
FROM uk_rasters e
WHERE ST_Intersects(rast, ST_Makeline(CAST(startloc AS GEOMETRY), CAST(endloc AS GEOMETRY)));

I want to do the same, but with a buffer around the line, like so:

SELECT ST_UNION(rast) INTO elevationdata
FROM uk_rasters e
WHERE ST_Intersects(rast, ST_Buffer(ST_Makeline(CAST(startloc AS GEOMETRY), CAST(endloc AS GEOMETRY)), 75));

This was still running after 4 minutes. I assume the buffer is somehow stopping ST_Intersects from using the Index on the table. I saw mentions of ST_DWithin, but I don't see a version that takes a raster and a geometry.

Is there another approach I should be using here?

Answer 1

One possible improvement would be to create the line buffer once, and re-use that for each raster. As currently you're recreating the buffer for each raster.

WITH linebuffer AS (
SELECT ST_Buffer(ST_Makeline(CAST(startloc AS GEOMETRY), CAST(endloc AS GEOMETRY)), 75) AS linebuffer_geom
)
SELECT ST_UNION(rast) INTO elevationdata
FROM uk_rasters, linebuffer
WHERE rast && linebuff_geom
AND ST_INTERSECTS(rast, linebuff_geom)

EDIT

Checking the unit for the projection you're using may help, 75 metres & 75 decimal degrees are worlds apart :).