[postgis-users] should I be using st_mapalgebra?

Rhys A.D. Stewart rhys.stewart at gmail.com
Thu Nov 29 12:40:40 PST 2012


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:
qq record;
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);

/* below just writes the raster to disk */
perform dev.wf(st_astiff(rast), '__kml/1250_changed5.tiff') from

$$ 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

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.


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121129/09db0f0d/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 1250_changed4.tiff
Type: image/tiff
Size: 54386 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121129/09db0f0d/attachment.tiff>

More information about the postgis-users mailing list