Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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)

share|improve this question
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.