[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Slow MapAlgebra performance
From: <jsantiso () srmconsulting ! es>
Date: 2020-04-16 16:22:46
Message-ID: 01cc01d6140b$440a9bb0$cc1fd310$ () srmconsulting ! es
[Download RAW message or body]
This is a multipart message in MIME format.
[Attachment #2 (multipart/related)]
This is a multipart message in MIME format.
[Attachment #4 (multipart/alternative)]
puff
De: postgis-users <postgis-users-bounces@lists.osgeo.org> En nombre de Kirill Bykov
Enviado el: jueves, 16 de abril de 2020 17:55
Para: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
Asunto: Re: [postgis-users] Slow MapAlgebra performance
Hello, thank you for your answer!
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?
From: Pierre Racine <mailto:Pierre.Racine@sbf.ulaval.ca>
Sent: Thursday, April 16, 2020 6:52 PM
To: postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
Subject: Re: [postgis-users] Slow MapAlgebra performance
Hi Kirill,
Using the callback version of ST_MapAlgebra() should be much faster but you have to \
write your own callback function.
<https://postgis.net/docs/RT_ST_MapAlgebra.html> \
https://postgis.net/docs/RT_ST_MapAlgebra.html
Let us know the difference.
Pierre
De : postgis-users <postgis-users-bounces@lists.osgeo.org \
<mailto:postgis-users-bounces@lists.osgeo.org> > De la part de ????? ?????? \
?????????? Envoyé : April 6, 2020 2:57 PM
À : postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
Objet : [postgis-users] Slow MapAlgebra performance
[Externe UL*]
Dear All!
I calculate vegetation indices on rasters (Landsat 8 scenes).
The calculation of SAVI=(NIR-RED)/(NIR+RED+L)*(1+L) takes about 2 minutes to \
complete.
This is enormous time compared to the same operation in QGIS which takes several \
seconds.
Here is the query (it's defined as postgres UDF and $1 parameter is L):
BEGIN
DROP TABLE IF EXISTS savi;
CREATE TABLE savi AS
SELECT bandt5.rid, ST_MapAlgebra(
bandt4.rast,
bandt5.rast,
'CASE WHEN ([rast2.val] + [rast1.val] = 0) THEN NULL ELSE (([rast2.val] - \
[rast1.val])*(1+$1) / ([rast2.val] + [rast1.val]+$1)::float) END', '32BF'
)
FROM bandt5, bandt4 WHERE bandt5.rid=bandt4.rid;
RETURN 1;
END;
I used the data from USGS, Landsat 8 Level 2, bands are stored in separate tables.
DB indices are created for every band by raster2pgsql.
Tile size is 128x128.
The whole raster2pgsql command: for each tif band:
"raster2pgsql -s 4326 -N -32767 -t 128x128 -I -C -M -d bandX.tif public.bandtX > \
bandX.sql".
Afterwards bandX.sql files were ingested by psql.
Are there any ways to improve the performance?
Thank you!
---
Regards,
Kirill
*ATTENTION : L'émetteur de ce courriel est externe à l'Université Laval.
É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 \
<mailto:hameconnage@ulaval.ca> hameconnage@ulaval.ca.
[Attachment #7 (text/html)]
<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>
["image002.png" (image/png)]
[Attachment #9 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic