PostGIS raster functions aspect and slope slow

Question

I am trying to compute the raster and slope values from a DGM table (8184 x 5159) imported from tif file tiled in 500x500 tiles.

In my query in which I merge the DGM table with a vector (point type) table consisting of 3000 (or 15000) points this query takes extremely long.

Explain analyze tells me for a single point to compute aspect and slope it takes more than 3s. I set on both tables an index (gist (st_convexhull(rast)) on the DGM and a gist(geom) on the point table). Any idea how to speed up this query?

Markus

EXPLAIN ANALYZE 
SELECT
  ST_Value(ST_Aspect(dem.rast, 1, '32BF'), sat.geom,true) AS aspect,
  ST_Value(ST_Slope(dem.rast, 1,'32BF'), sat.geom,true) AS slope
FROM 
   safer.st_dgm_tiled dem, safer.aoi_bolz_rsat_a sat
WHERE ST_Intersects(dem.rast, sat.geom) LIMIT 1;

And this is the query plan:

Limit (cost=0.00..1101.35 rows=1 width=64) (actual time=3332.439..33051.935 rows=10 loops=1) -> Nested Loop (cost=0.00..1101.35 rows=1 width=64) (actual time=3332.434..33051.913 rows=10 loops=1) Join Filter: _st_intersects(dem.rast, sat.geom, NULL::integer) -> Seq Scan on st_dgm_tiled dem (cost=0.00..3.87 rows=187 width=32) (actual time=0.103..0.119 rows=59 loops=1) -> Index Scan using aoi_bolz_rsat_a_gist on aoi_bolz_rsat_a sat (cost=0.00..3.35 rows=1 width=32) (actual time=0.006..0.007 rows=0 loops=59) Index Cond: ((dem.rast)::geometry && geom) Total runtime: 33065.589 ms (7 rows)

Answer 1

There are a few things to consider:

Tile Size

Some research at my University has shown that 100x100 tile size is optimal for our work with SRTM. I see from your comment you've discovered the same. There are a few ways to tile your data:

• If you haven't loaded your DEM into PostGIS yet, use the -t flag with raster2pgsql
• If you have, check out ST_Retile (v2.2+) or ST_Tile and ST_Union and create a new table.

Constraints and Spatial Indexing

After you've tiled your data, you should also add constraints and a spatial index to further speed up queries. My post on generating dummy raster data provides a decent example to follow.