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?
أكثر...
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?
أكثر...