<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=WordSection1>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Thanks for your answer Regina. I repost to the group…<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>I planned those functions to get and set block of values from/into
the raster:<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>-ST_GetValues(rast, band, x, y, width, height) -> values[]<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>-ST_SetValues(rast, band, x, y, width, height, values[])<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>-ST_SetValues(rast1, band1, x1, y1, fromrast, band, x2, y2,
width, height)<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>-ToRaster(raster, band, geometry, val)<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>The two first one works with value arrays and the third one copy
values from a raster band. They are differenciated from their pixel by pixel version
by the “s” at the end. Those three are all planned as C functions. The
last one burn the shadow of a geometry into a raster band with the provided
value. I don</span><span lang=FR-CA style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>’t know yet if a plpgsql version of this last one</span><span
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>
would be quick enough.</span><span lang=FR-CA style='font-size:11.0pt;
font-family:"Calibri","sans-serif";color:#1F497D'><o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>However some functions (ST_MapAlgebra for example) need to get
and set pixels values one at a time. My questionning is whether we could keep a
function like mapalgebra in plpgsql, getting all or part of the values to get
and set in one or many of those get and set C calls, or whether we really have
to implement it in C. <o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>There are some advantages to have functions in plpgsql when we
can: they are easily modifiable by users.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Pierre<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div style='border:none;border-left:solid blue 1.5pt;padding:0cm 0cm 0cm 4.0pt'>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> Obe, Regina
[mailto:robe.dnd@cityofboston.gov] <br>
<b>Sent:</b> 31 août 2010 08:27<br>
<b>To:</b> Pierre Racine; George Silva<br>
<b>Cc:</b> Jorge Arévalo<br>
<b>Subject:</b> RE: [postgis-devel] To PL/pgSQL or not to PL/pgSQL<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:blue'>Hi Guys,</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:blue'>Sorry I've been very quiet. I haven't had a chance to take a
close look at this since I've been tied up with other things.</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:blue'>I think you asked once if plpgsql is slower than implementing this
stuff in C.</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:blue'>The short answer is it usually is but it doesn't necessarily need
to be and in some cases can be faster since its closer to the PostgreSQL
side. Take for example ST_MinimimumBoundingCircle. Its implemented
in plpgsql but I would suspect its not much slower than if implemented in C.</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:blue'>The real problem -- is you really got to come up with a better way
of getting pixels in and out of the C layer than ST_SetValue,
ST_Value or tackle the more fundamental issue of why so much memory/time is
losting switching between the barriers.</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:blue'>That is the part that is really really slow. If you compare
say ST_Dump vs. doing the same with generate_series (ST_GeometryN/ST_PointN),
that is what makes all the difference. The more</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:blue'>things you need to pull out, it doesn't get worse linearly, it gets
worse quadratically. ST_GeometryN/ST_PointN is quadratically worse in speed
than doing the same with ST_Dump (except in the case </span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:blue'>where you only need to work with a few points of a large set of
points/geometries - then you gain with ST_PointN/ST_GeometryN</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:blue'>What I have found kills you with implementing things in plpgsql
programming (atleast against PostGIS and I assume any non-native C that is not
part of the core PostgreSQL memory space) is what I call the reentry
effect - converting back and forth between the native PostgreSQL environment
(plpgsql/ native PostgreSQL C functions). Which is why you </span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:blue'>want to get as much data as you plan to work with in say PostgreSQL
arrays (even if you overshoot it a bit), and feed it back in a single function
or alternatively figure out a way to reduce that reentry effect.</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:blue'>This is really why I've been pushing the change to ST_SetValue , ST_Value
to allow setting and pulling blocks of pixels. Without that fundamental
change, I feel that any function you implement in plpgsql will</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:blue'>be about 1000 times slower than it needs to be. The
alternative of researching that would be nice and getting rid of that
barrier. I fear that is harder to fix though. Though maybe
not. After all in the end -- PostgreSQL is all C.</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<div class=MsoNormal align=center style='text-align:center'>
<hr size=2 width="100%" align=center>
</div>
<p class=MsoNormal style='margin-bottom:12.0pt'><b><span style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'>From:</span></b><span style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> Pierre Racine
[mailto:Pierre.Racine@sbf.ulaval.ca] <br>
<b>Sent:</b> Friday, August 27, 2010 2:32 PM<br>
<b>To:</b> George Silva<br>
<b>Cc:</b> Obe, Regina; Jorge Arévalo<br>
<b>Subject:</b> RE: [postgis-devel] To PL/pgSQL or not to PL/pgSQL</span><o:p></o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>George,<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Here is a first version of MapAlgebra. There is a one raster
version and a two rasters version. Some enhancement are still to do:<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>-Everything is implemented in plpgsql and might be (very) slow. <o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>-They are not yet able to look for neighbour pixels. Ex.
rast1[-1,1] or rast2[-3,2]<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>-They do not yet work with neighbour tiles pixels.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>-The two raster version do not handle rotation very well.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>-The two raster version do not resample when needed.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Let me know if you like it. I’m doing amazing things with
the two rasters version: <o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>-A very sophisticated ST_Union(raster) into a unique raster<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>-ST_Reclass<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>-And more to come (ST_Clip, ST_Intersection(geometry, raster)
-> raster, ST_SelectByValue).<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>I think this function will be, with st_intersection, the
very core of WKT Raster analysis capacity.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>I wrote them to assert my specifications for a number of
function that I am about to put in the wiki.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Pierre<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div style='border:none;border-left:solid blue 1.5pt;padding:0cm 0cm 0cm 4.0pt'>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>
postgis-devel-bounces@postgis.refractions.net
[mailto:postgis-devel-bounces@postgis.refractions.net] <b>On Behalf Of </b>George
Silva<br>
<b>Sent:</b> 19 août 2010 17:41<br>
<b>To:</b> PostGIS Development Discussion<br>
<b>Subject:</b> Re: [postgis-devel] To PL/pgSQL or not to PL/pgSQL<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<div>
<p class=MsoNormal>Hello Pierre,<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal>I'm up for a plpgsql implementation for now. I've written a
few functions for wkt raster, but as you noticed, I stumbled upon some problems
for the more complex ones.<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal>I've implemented also: plus, minus, and other math
operations for rasters. They all work fine.<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal>When I get home I'll email it to the list and you can use
them as you seem fit.<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal>Cheers,<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal style='margin-bottom:12.0pt'>George<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal>On Thu, Aug 19, 2010 at 5:10 PM, Pierre Racine <<a
href="mailto:Pierre.Racine@sbf.ulaval.ca">Pierre.Racine@sbf.ulaval.ca</a>>
wrote:<o:p></o:p></p>
<p class=MsoNormal>Hi,<br>
<br>
Are functions written in C REALLY faster than functions written in PL/pgSQL?
Why?<br>
<br>
Is there any rule saying what king of function should be better implemented in
C and what kind should be better (of OK) in PL/pgSQL?<br>
<br>
Do you guys prefer to publish a slower PL/pgSQL function NOW (warning that it
might be implemented in C later with the same signature) or wait months that
the C equivalent is implemented?<br>
<br>
I understand that most of the PostGIS functionality is in GEOS but we don't
have a GEOS equivalent for WKT Raster (we have GDAL but it's very limited in
terms of analysis functionality) and I feel that everything would go much
faster implementing many things in PL/pgSQL. For example right now I'm implementing
a nice ST_MapAlgebra function in PL/pgSQL. I have no idea if it would be really
much faster in C. I love PL/pgSQL...<br>
<br>
Thanks for your advices,<br>
<br>
Pierre<br>
<br>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@postgis.refractions.net">postgis-devel@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-devel"
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-devel</a><o:p></o:p></p>
</div>
<p class=MsoNormal><br>
<br clear=all>
<br>
-- <br>
George R. C. Silva<br>
<br>
Desenvolvimento em GIS<br>
<a href="http://blog.geoprocessamento.net">http://blog.geoprocessamento.net</a><o:p></o:p></p>
</div>
<div class=MsoNormal align=center style='text-align:center'>
<hr size=1 width="100%" align=center>
</div>
<p><strong>The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure pursuant to
Massachusetts law. It is intended solely for the addressee. If you received
this in error, please contact the sender and delete the material from any
computer. </strong><o:p></o:p></p>
</div>
</div>
</body>
</html>