Timeline for Raster and Geometry ST_Intersects slows when add buffer in PostGIS
Current License: CC BY-SA 3.0
13 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 27, 2015 at 14:04 | vote | accept | Adam Knights | ||
| May 27, 2015 at 13:59 | comment | added | Andy | That'd be one big buffer, answer edited. | |
| May 27, 2015 at 13:57 | history | edited | Andy | CC BY-SA 3.0 |
added projection units
|
| May 27, 2015 at 13:51 | comment | added | Adam Knights | That would cause issues! Yes, using 0.0012 as a quick guess at 75m in the UK gets the speed back. Do you want to add that to your answer and I'll accept it? | |
| May 27, 2015 at 13:44 | comment | added | Andy | You might not be buffering your line by 75 metres then, I think the unit for EPSG:4326 is decimal degrees. | |
| May 27, 2015 at 13:42 | comment | added | Adam Knights | The function goes on to sample points by using ST_VALUE on the elevationdata we union together. All very fast, but I'm getting a number of z=0's and wondered if it was due to elevationdata not quite being 'wide' enough. So the thought process was to add a buffer around the line im sampling on to grab a few more rasters in the union. Then I hit the speed issue with the union/intersects (I've checked its definitely this part and not the latter part of the function) | |
| May 27, 2015 at 13:38 | comment | added | Adam Knights | No, I used ST_Transform with alignto to create a new table of 4326 aligned rasters that I then created a new index on. | |
| May 27, 2015 at 13:36 | comment | added | Andy | Just out of interest, are your rasters projected in EPSG:27700? | |
| May 27, 2015 at 12:35 | comment | added | Adam Knights | 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. | |
| May 27, 2015 at 11:10 | comment | added | Andy | I've just edited my answer to use a bounding box lookup, which should use the index on the rasters. PostGIS &&, try that. | |
| May 27, 2015 at 11:07 | history | edited | Andy | CC BY-SA 3.0 |
Added BoundingBox overlap
|
| May 27, 2015 at 10:55 | comment | added | Adam Knights | Thanks Andy, I didn't realise that it would create it every time, sadly this did not solve the slow down though. | |
| May 27, 2015 at 10:49 | history | answered | Andy | CC BY-SA 3.0 |