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)