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-boun...@lists.osgeo.org> De la part de ????? ?????? ?????????? Envoyé : April 6, 2020 2:57 PM À : 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 hameconn...@ulaval.ca<mailto:hameconn...@ulaval.ca>. ________________________________
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
