[postgis-users] Slow MapAlgebra performance

[Pierre Racine]

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<mailto:hameconnage at ulaval.ca>.
________________________________

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200416/4f4b8bf9/attachment.html>