1

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?

  • 1
    Try rewritting your query like this: SELECT ST_UNION(rast) INTO elevationdata FROM uk_rasters e, (SELECT ST_Buffer(ST_Makeline(CAST(startloc AS GEOMETRY), CAST(endloc AS GEOMETRY)), 75) as geom) AS buff WHERE ST_Intersects(rast, buff.geom ); It isn't testing, so I might have a couple of parenthesis in wrong place, but the general idea is that spatial indices work better when you arrange it this way, rather than having a sub-query inside ST_Intersects with an operation like Buffer – John Powell May 27 '15 at 10:38
  • Still taking an age and not coming back after 4 minutes. – Knightsy May 27 '15 at 10:53
2

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 :).

  • Thanks Andy, I didn't realise that it would create it every time, sadly this did not solve the slow down though. – Knightsy May 27 '15 at 10:55
  • I've just edited my answer to use a bounding box lookup, which should use the index on the rasters. PostGIS &&, try that. – AndyHarney May 27 '15 at 11:10
  • Sadly still not coming back, is there any further analysis I can do within my function that would help see what is going on? I checked the original query without the st_buffer and its still 0.1 seconds, so indexing is there and good. – Knightsy May 27 '15 at 12:35
  • Just out of interest, are your rasters projected in EPSG:27700? – AndyHarney May 27 '15 at 13:36
  • 1
    You might not be buffering your line by 75 metres then, I think the unit for EPSG:4326 is decimal degrees. – AndyHarney May 27 '15 at 13:44

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.