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?
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)