[postgis-users] Slow MapAlgebra performance

[Kirill Bykov]

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
Sent: Thursday, April 16, 2020 6:52 PM
To: postgis-users at 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

Let us know the difference.

Pierre

De : postgis-users <postgis-users-bounces at lists.osgeo.org> De la part de ????? ?????? ??????????
Envoyé : April 6, 2020 2:57 PM
À : postgis-users at 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 hameconnage at ulaval.ca.



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200416/06f782ec/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 61C67C62D9AF4A03A7B984F42AE98007.png
Type: image/png
Size: 156 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200416/06f782ec/attachment.png>