[postgis-users] Slow MapAlgebra performance

[Быков Кирилл Валерьевич]

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

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