<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:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:8.5in 11.0in;
margin:56.7pt 42.5pt 56.7pt 85.05pt;}
div.WordSection1
{page:WordSection1;}
--></style></head><body lang="EN-US" link="blue" vlink="#954F72"><div class="WordSection1"><p class="MsoNormal">Hello, thank you for your answer! </p><p class="MsoNormal">There is difference, you are right. It takes only one minute instead of 2. But still not as fast as QGIS :c Any more optimization tricks?</p><p class="MsoNormal"><o:p> </o:p></p><div style="mso-element:para-border-div;border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in"><p class="MsoNormal" style="border:none;padding:0in"><b>From: </b><a href="mailto:Pierre.Racine@sbf.ulaval.ca">Pierre Racine</a><br><b>Sent: </b>Thursday, April 16, 2020 6:52 PM<br><b>To: </b><a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br><b>Subject: </b>Re: [postgis-users] Slow MapAlgebra performance</p></div><p class="MsoNormal"><o:p> </o:p></p><p style="background:white"><span lang="EN-CA" style="font-family:"Calibri",sans-serif;color:#1F497D">Hi </span><span lang="EN-CA" style="font-family:"Calibri",sans-serif;color:black">Kirill,<o:p></o:p></span></p><p class="MsoNormal"><span lang="EN-CA" style="font-size:12.0pt;color:#1F497D"><o:p> </o:p></span></p><p class="MsoNormal"><span lang="EN-CA" style="font-size:12.0pt;color:#1F497D">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 lang="EN-CA" style="font-size:12.0pt;color:#1F497D"><o:p> </o:p></span></p><p class="MsoNormal"><span lang="EN-CA" style="font-size:12.0pt"><a href="https://postgis.net/docs/RT_ST_MapAlgebra.html"><span style="color:#0563C1">https://postgis.net/docs/RT_ST_MapAlgebra.html</span></a><o:p></o:p></span></p><p class="MsoNormal"><span lang="EN-CA" style="font-size:12.0pt"><o:p> </o:p></span></p><p class="MsoNormal"><span lang="EN-CA" style="font-size:12.0pt">Let us know the difference.<span style="color:#1F497D"><o:p></o:p></span></span></p><p class="MsoNormal"><span lang="EN-CA" style="font-size:12.0pt;color:#1F497D"><o:p> </o:p></span></p><p class="MsoNormal"><span lang="EN-CA" style="font-size:12.0pt;color:#1F497D">Pierre<o:p></o:p></span></p><p class="MsoNormal"><span lang="EN-CA" style="color:#1F497D"><o:p> </o:p></span></p><div><div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in"><p class="MsoNormal"><b><span lang="FR">De :</span></b><span lang="FR"> 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"><span lang="EN-CA" style="font-size:12.0pt;font-family:"Times New Roman",serif"><o:p> </o:p></span></p><p class="MsoNormal"><span lang="EN-CA" style="font-size:12.0pt;font-family:"Times New Roman",serif">[Externe UL*] <o:p></o:p></span></p><div><div><p class="MsoNormal" style="background:white"><span lang="EN-CA" style="font-size:10.0pt;font-family:"Tahoma",sans-serif;color:black">Dear All!</span><span lang="EN-CA" style="font-size:12.0pt;color:black"><o:p></o:p></span></p></div><div><div><div><div><div><p style="background:white"><span lang="EN-CA" style="font-family:"Calibri",sans-serif;color:black"><o:p> </o:p></span></p><p style="background:white"><span lang="EN-CA" 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 lang="EN-CA" 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 lang="EN-CA" 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 lang="EN-CA" style="font-family:"Calibri",sans-serif;color:black"><o:p> </o:p></span></p><p style="background:white"><span lang="EN-CA" 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 style="font-family:"Calibri",sans-serif;color:black">it’s defined as postgres UDF and $1 parameter is L):</span><span lang="EN-CA" style="font-family:"Calibri",sans-serif;color:black"><o:p></o:p></span></p><p style="background:white"><span lang="EN-CA" style="font-family:"Calibri",sans-serif;color:black"><o:p> </o:p></span></p><p style="background:white"><span lang="EN-CA" style="font-family:"Calibri",sans-serif;color:black">BEGIN<o:p></o:p></span></p><p style="margin-left:27.0pt;background:white"><span lang="EN-CA" 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 lang="EN-CA" 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 lang="EN-CA" 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 lang="EN-CA" 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 lang="EN-CA" 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 lang="EN-CA" 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 lang="EN-CA" style="font-family:"Calibri",sans-serif;color:black"> )<o:p></o:p></span></p><p style="margin-left:27.0pt;background:white"><span lang="EN-CA" 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 lang="EN-CA" style="font-family:"Calibri",sans-serif;color:black">RETURN 1;<o:p></o:p></span></p><p style="background:white"><span lang="EN-CA" style="font-family:"Calibri",sans-serif;color:black">END;<o:p></o:p></span></p><p style="background:white"><span lang="EN-CA" style="font-family:"Calibri",sans-serif;color:black"> <o:p></o:p></span></p><p style="background:white"><span lang="EN-CA" 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 lang="EN-CA" 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 lang="EN-CA" style="font-family:"Calibri",sans-serif;color:black">Tile size is 128x128.<o:p></o:p></span></p><p style="background:white"><span lang="EN-CA" 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 lang="EN-CA" 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 lang="EN-CA" 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 lang="EN-CA" style="font-family:"Calibri",sans-serif;color:black"><o:p> </o:p></span></p><p style="background:white"><span lang="EN-CA" 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 lang="EN-CA" style="font-family:"Calibri",sans-serif;color:black"><o:p> </o:p></span></p><p style="background:white"><span lang="EN-CA" style="font-family:"Calibri",sans-serif;color:black">Thank you!<o:p></o:p></span></p><p style="background:white"><span lang="EN-CA" style="font-family:"Calibri",sans-serif;color:black"> <o:p></o:p></span></p><p style="background:white"><span lang="EN-CA" style="font-family:"Calibri",sans-serif;color:black">---<o:p></o:p></span></p><p style="background:white"><span lang="EN-CA" style="font-family:"Calibri",sans-serif;color:black">Regards,<o:p></o:p></span></p><p style="background:white"><span lang="EN-CA" 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 lang="EN-CA" 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 lang="EN-CA" style="color:black">*ATTENTION : L’émetteur de ce courriel est externe à l’Université Laval. </span></b><b><span lang="EN-CA"><o:p></o:p></span></b></p><div><p class="MsoNormal" style="background:#E3E3E3"><span lang="EN-CA" style="color:black">É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"><span style="color:#0563C1">hameconnage@ulaval.ca</span></a>.</span><span lang="EN-CA"><o:p></o:p></span></p></div></div><p class="MsoNormal" align="center" style="text-align:center"><span lang="EN-CA" style="font-size:12.0pt;font-family:"Times New Roman",serif"><img border="0" width="566" height="2" style="width:5.9in;height:.025in" id="Horizontal_x0020_Line_x0020_1" src="cid:image002.png@01D61420.7B5D7640"></span><span lang="EN-CA" style="font-size:12.0pt;font-family:"Times New Roman",serif"><o:p></o:p></span></p><p class="MsoNormal"><span lang="EN-CA" style="font-size:12.0pt;font-family:"Times New Roman",serif"><o:p> </o:p></span></p><p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Times New Roman",serif"><o:p> </o:p></span></p></div></body></html>