[postgis-devel] [PostGIS] #2133: possible performance issue with ST_MapAlgebra

[PostGIS]

#2133: possible performance issue with ST_MapAlgebra
--------------------+-------------------------------------------------------
 Reporter:  robe    |       Owner:  dustymugs    
     Type:  defect  |      Status:  new          
 Priority:  medium  |   Milestone:  PostGIS 2.1.0
Component:  raster  |     Version:  trunk        
 Keywords:          |  
--------------------+-------------------------------------------------------
 In the docs I have this example:
 http://postgis.net/documentation/manual-2.1SVN/RT_ST_MapAlgebraExpr2.html

 which is much faster now with r10798 upgrade
 --on my 64-bit windows 2008 it used to take about 3.6 seconds
 -- now it's down to 490ms (SUPER improvement)
 {{{
 (
 WITH pr AS
 -- Note the order of operation: we clip all the rasters to dimensions of
 our region
 (SELECT ST_Clip(rast,ST_Expand(geom,50) ) As rast, g.geom
         FROM aerials.o_2_boston AS r INNER JOIN
 -- union our parcels of interest so they form a single geometry we can
 later intersect with
                 (SELECT ST_Union(ST_Transform(the_geom,26986)) AS geom
                   FROM landparcels WHERE pid IN('0303890000',
 '0303900000')) As g
                 ON ST_Intersects(rast::geometry, ST_Expand(g.geom,50))
 ),
 -- we then union the raster shards together
 -- ST_Union on raster is kinda of slow but much faster the smaller you can
 get the rasters
 -- therefore we want to clip first and then union
 prunion AS
 (SELECT ST_AddBand(NULL,
 ARRAY[ST_Union(rast,1),ST_Union(rast,2),ST_Union(rast,3)] ) As
 clipped,geom
 FROM pr
 GROUP BY geom)
 -- return our final raster which is the unioned shard with
 -- with the overlay of our parcel boundaries
 -- add first 2 bands, then mapalgebra of 3rd band + geometry
 SELECT ST_AddBand(ST_Band(clipped,ARRAY[1,2])
         , ST_MapAlgebraExpr(ST_Band(clipped,3),
 ST_AsRaster(ST_Buffer(ST_Boundary(geom),2),clipped, '8BUI',250),
          '[rast2.val]', '8BUI', 'FIRST', '[rast2.val]', '[rast1.val]') )
 As rast
 FROM prunion;
 )
 }}}

 However I thought I could just replace my ST_MapAlgebraExpr with
 ST_MapAlgebra so I'm not using a deprecated function, and while the output
 looks the same as the original, my time for this query
 is: 2320 ms which granted is better than the older timing of
 ST_MapAlgebraExpr, but not nearly as good as the new timing of
 ST_MapAlgebraExpr.

 {{{
 (
 WITH pr AS
 -- Note the order of operation: we clip all the rasters to dimensions of
 our region
 (SELECT ST_Clip(rast,ST_Expand(geom,50) ) As rast, g.geom
         FROM aerials.o_2_boston AS r INNER JOIN
 -- union our parcels of interest so they form a single geometry we can
 later intersect with
                 (SELECT ST_Union(ST_Transform(the_geom,26986)) AS geom
                   FROM landparcels WHERE pid IN('0303890000',
 '0303900000')) As g
                 ON ST_Intersects(rast::geometry, ST_Expand(g.geom,50))
 ),
 -- we then union the raster shards together
 -- ST_Union on raster is kinda of slow but much faster the smaller you can
 get the rasters
 -- therefore we want to clip first and then union
 prunion AS
 (SELECT ST_AddBand(NULL,
 ARRAY[ST_Union(rast,1),ST_Union(rast,2),ST_Union(rast,3)] ) As
 clipped,geom
 FROM pr
 GROUP BY geom)
 -- return our final raster which is the unioned shard with
 -- with the overlay of our parcel boundaries
 -- add first 2 bands, then mapalgebra of 3rd band + geometry
 SELECT ST_AddBand(ST_Band(clipped,ARRAY[1,2])
         , ST_MapAlgebra(ST_Band(clipped,3),
 ST_AsRaster(ST_Buffer(ST_Boundary(geom),2),clipped, '8BUI',250),
          '[rast2.val]', '8BUI', 'FIRST', '[rast2.val]', '[rast1.val]') )
 As rast
 FROM prunion;
 )
 }}}

 Is there more to it than just swapping the function names?

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/2133>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.