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@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 bkp...@ucdavis.edu _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users