<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: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;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:2.0cm 42.5pt 2.0cm 3.0cm;}
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=ES link=blue vlink="#954F72"><div class=WordSection1><p class=MsoNormal><span style='mso-fareast-language:EN-US'>puff<o:p></o:p></span></p><p class=MsoNormal><span style='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>De:</b> postgis-users <postgis-users-bounces@lists.osgeo.org> <b>En nombre de </b>Kirill Bykov<br><b>Enviado el:</b> jueves, 16 de abril de 2020 17:55<br><b>Para:</b> PostGIS Users Discussion <postgis-users@lists.osgeo.org><br><b>Asunto:</b> Re: [postgis-users] Slow MapAlgebra performance<o:p></o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span lang=EN-US>Hello, thank you for your answer! <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>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?<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span lang=EN-US>From: </span></b><span lang=EN-US><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<o:p></o:p></span></p></div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p style='background:white'><span lang=EN-CA style='color:#1F497D'>Hi </span><span lang=EN-CA style='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 0cm 0cm 0cm'><p class=MsoNormal><b><span lang=FR>De :</span></b><span lang=FR> postgis-users <<a href="mailto:postgis-users-bounces@lists.osgeo.org">postgis-users-bounces@lists.osgeo.org</a>> <b>De la part de</b> ????? ?????? ??????????<br><b>Envoyé :</b> April 6, 2020 2:57 PM<br><b>À :</b> <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><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='color:black'><o:p> </o:p></span></p><p style='background:white'><span lang=EN-CA style='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='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='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='color:black'><o:p> </o:p></span></p><p style='background:white'><span lang=EN-CA style='color:black'>Here is the query </span><span lang=RU style='color:black'>(</span><span lang=EN-US style='color:black'>it’s defined as postgres UDF and $1 parameter is L):</span><span lang=EN-CA style='color:black'><o:p></o:p></span></p><p style='background:white'><span lang=EN-CA style='color:black'><o:p> </o:p></span></p><p style='background:white'><span lang=EN-CA style='color:black'>BEGIN<o:p></o:p></span></p><p style='margin-left:27.0pt;background:white'><span lang=EN-CA style='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='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='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='color:black'>bandt4.rast,<o:p></o:p></span></p><p style='margin-left:81.0pt;background:white'><span lang=EN-CA style='color:black'>bandt5.rast,<o:p></o:p></span></p><p style='margin-left:81.0pt;background:white'><span lang=EN-CA style='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='color:black'> )<o:p></o:p></span></p><p style='margin-left:27.0pt;background:white'><span lang=EN-CA style='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='color:black'>RETURN 1;<o:p></o:p></span></p><p style='background:white'><span lang=EN-CA style='color:black'>END;<o:p></o:p></span></p><p style='background:white'><span lang=EN-CA style='color:black'> <o:p></o:p></span></p><p style='background:white'><span lang=EN-CA style='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='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='color:black'>Tile size is 128x128.<o:p></o:p></span></p><p style='background:white'><span lang=EN-CA style='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='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='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='color:black'><o:p> </o:p></span></p><p style='background:white'><span lang=EN-CA style='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='color:black'><o:p> </o:p></span></p><p style='background:white'><span lang=EN-CA style='color:black'>Thank you!<o:p></o:p></span></p><p style='background:white'><span lang=EN-CA style='color:black'> <o:p></o:p></span></p><p style='background:white'><span lang=EN-CA style='color:black'>---<o:p></o:p></span></p><p style='background:white'><span lang=EN-CA style='color:black'>Regards,<o:p></o:p></span></p><p style='background:white'><span lang=EN-CA style='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='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.8958in;height:.0208in' id="Horizontal_x0020_Line_x0020_1" src="cid:image002.png@01D6141C.06F1FB90"></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 lang=EN-US style='font-size:12.0pt;font-family:"Times New Roman",serif'><o:p> </o:p></span></p></div></body></html>