1

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)

  • performance has speeded up: e.g. 500x500 tiles: 3.2 s e.g. 250x250 tiles: 0.83 s e.g. 100x100 tiles: 0.12 s – markusin Jul 31 '13 at 9:17
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:

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.

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.