[postgis-users] should I be using st_mapalgebra?

[Bborie Park]

Rhys,

ST_MapAlgebra would be faster as it is in C.  I'm assuming you're using
2.1 since you seem to be using ST_PixelAsCentroids().

The serial ST_SetValue() kills performance. Use ST_SetValues() if possible.

-bborie

On 11/29/2012 12:40 PM, Rhys A.D. Stewart wrote:
> Greetings,
> 
> I'm am trying to create a raster that is coloured based on the distance
> from an arbitrary point. kinda like the attached tiff file. To do that I
> used the following ACB:
> __________________________________________________
> DO
> $$
> DECLARE
> qq record;
> BEGIN
> for qq in
> with one as
>   (select (st_pixelascentroids(rast)).* from r.island_ouline_raster_125
>  ),
> two as
>   (select *, st_distance(geom, 'srid=3448;POINT(600000
> 660000)'::geometry)::int dist from one where val is not null ),
> three as
>   (select * , percent_rank() over (order by dist) pr from two),
> four as
>   (select *, dev.range_percent(200, 125, pr) red, dev.range_percent(25,
> 250, pr) green, dev.range_percent(100, 25, pr) blue from three order by
> dist )
> /*dev.range takes a percent and two endoints and returns the % of he diff
> plus endpoint 1 */
> select * from four LOOP
> update r.island_ouline_raster_12_point_5 set rast = st_setvalue(rast,1,
> qq.geom, qq.red);
> update r.island_ouline_raster_12_point_5 set rast = st_setvalue(rast,2,
> qq.geom, qq.green);
> update r.island_ouline_raster_12_point_5 set rast = st_setvalue(rast,3,
> qq.geom, qq.blue);
> 
> 
> END LOOP;
> /* below just writes the raster to disk */
> perform dev.wf(st_astiff(rast), '__kml/1250_changed5.tiff') from
> r.island_ouline_raster_1250;
> END;
> 
> $$ language plpgsql
> ____________________________________________________________
> 
> I used an ACB because I couldn't think of anyway to update the raster with
> each column/row value in plain sql. Even so,  it seems like overkill. Is
> there an easier way, possibly using st_mapalgebra to accomplish the same
> objective?
> 
> Also I'm running the same ACB on a raster which is ~ 1800x700 and it it
> still not finished, its currently at 3570123 ms. The attached raster is
> 185x73 and that took about 40 seconds to generate. So anything that would
> be faster would also be great.
> 
> Regards,
> 
> Rhys
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> 

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkpark at ucdavis.edu