<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=utf-8">
<meta name="Generator" content="Microsoft Word 15 (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:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@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:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p
{mso-style-priority:99;
margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle19
{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:72.0pt 72.0pt 72.0pt 72.0pt;}
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-CA" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Hi
</span><span style="font-family:"Calibri",sans-serif;color:black">Kirill,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Using the callback version of ST_MapAlgebra() should be much faster but you have to write your own callback function.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Calibri",sans-serif"><a href="https://postgis.net/docs/RT_ST_MapAlgebra.html">https://postgis.net/docs/RT_ST_MapAlgebra.html</a><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Calibri",sans-serif"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Calibri",sans-serif">Let us know the difference.</span><span style="font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Pierre<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="FR" style="font-size:11.0pt;font-family:"Calibri",sans-serif">De :</span></b><span lang="FR" style="font-size:11.0pt;font-family:"Calibri",sans-serif"> postgis-users <postgis-users-bounces@lists.osgeo.org>
<b>De la part de</b> ????? ?????? ??????????<br>
<b>Envoyé :</b> April 6, 2020 2:57 PM<br>
<b>À :</b> postgis-users@lists.osgeo.org<br>
<b>Objet :</b> [postgis-users] Slow MapAlgebra performance<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">[Externe UL*] <o:p></o:p></p>
<div>
<div>
<p class="MsoNormal" style="background:white"><span style="font-size:10.0pt;font-family:"Tahoma",sans-serif;color:black">Dear All!</span><span style="font-family:"Calibri",sans-serif;color:black"><o:p></o:p></span></p>
</div>
<div>
<div>
<div>
<div>
<div>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black"><o:p> </o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black">I calculate vegetation indices on rasters (Landsat 8 scenes).<o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black">The calculation of SAVI=(NIR-RED)/(NIR+RED+L)*(1+L) takes about 2 minutes to complete.<o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black">This is enormous time compared to the same operation in QGIS which takes several seconds.<o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black"><o:p> </o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black">Here is the query </span><span lang="RU" style="font-family:"Calibri",sans-serif;color:black">(</span><span lang="EN-US" style="font-family:"Calibri",sans-serif;color:black">it’s
defined as postgres UDF and $1 parameter is L):</span><span style="font-family:"Calibri",sans-serif;color:black"><o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black"><o:p> </o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black">BEGIN<o:p></o:p></span></p>
<p style="margin-left:27.0pt;background:white"><span style="font-family:"Calibri",sans-serif;color:black">DROP TABLE IF EXISTS savi;<o:p></o:p></span></p>
<p style="margin-left:27.0pt;background:white"><span style="font-family:"Calibri",sans-serif;color:black">CREATE TABLE savi AS<o:p></o:p></span></p>
<p style="margin-left:27.0pt;background:white"><span style="font-family:"Calibri",sans-serif;color:black">SELECT bandt5.rid, ST_MapAlgebra(<o:p></o:p></span></p>
<p style="margin-left:81.0pt;background:white"><span style="font-family:"Calibri",sans-serif;color:black">bandt4.rast,<o:p></o:p></span></p>
<p style="margin-left:81.0pt;background:white"><span style="font-family:"Calibri",sans-serif;color:black">bandt5.rast,<o:p></o:p></span></p>
<p style="margin-left:81.0pt;background:white"><span style="font-family:"Calibri",sans-serif;color:black">'CASE WHEN ([rast2.val] + [rast1.val] = 0) THEN NULL ELSE (([rast2.val] - [rast1.val])*(1+$1) / ([rast2.val] + [rast1.val]+$1)::float) END', '32BF'<o:p></o:p></span></p>
<p style="margin-left:27.0pt;background:white"><span style="font-family:"Calibri",sans-serif;color:black"> )<o:p></o:p></span></p>
<p style="margin-left:27.0pt;background:white"><span style="font-family:"Calibri",sans-serif;color:black">FROM bandt5, bandt4 WHERE bandt5.rid=bandt4.rid;<o:p></o:p></span></p>
<p style="margin-left:27.0pt;background:white"><span style="font-family:"Calibri",sans-serif;color:black">RETURN 1;<o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black">END;<o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black"> <o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black">I used the data from USGS, Landsat 8 Level 2, bands are stored in separate tables.<o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black">DB indices are created for every band by raster2pgsql.<o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black">Tile size is 128x128.<o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black">The whole raster2pgsql command: for each tif band:<o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black">"raster2pgsql -s 4326 -N -32767 -t 128x128 -I -C -M -d bandX.tif public.bandtX > bandX.sql".<o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black">Afterwards bandX.sql files were ingested by psql.<o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black"><o:p> </o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black">Are there any ways to improve the performance?<o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black"><o:p> </o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black">Thank you!<o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black"> <o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black">---<o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black">Regards,<o:p></o:p></span></p>
<p style="background:white"><span style="font-family:"Calibri",sans-serif;color:black">Kirill<o:p></o:p></span></p>
</div>
</div>
</div>
<div>
<div id="x_x_Signature">
<div id="x_x_divtagdefaultwrapper">
<div id="x_x_divtagdefaultwrapper">
<p><span style="font-family:"Calibri",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<div>
<p class="MsoNormal" style="background:#E3E3E3"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif">*ATTENTION : L’émetteur de ce courriel est externe à l’Université Laval.
<o:p></o:p></span></b></p>
<div>
<p class="MsoNormal" style="background:#E3E3E3"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif">Évitez de cliquer sur un hyperlien, d’ouvrir une pièce jointe ou de transmettre des informations si vous ne connaissez pas l’expéditeur du courriel.
En cas de doute, contactez l’équipe de soutien informatique de votre unité ou <a href="mailto:hameconnage@ulaval.ca">
hameconnage@ulaval.ca</a>.<o:p></o:p></span></p>
</div>
</div>
<div class="MsoNormal" align="center" style="text-align:center">
<hr size="3" width="100%" align="center">
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>