[postgis-users] Slow MapAlgebra performance

[jsantiso at srmconsulting.es]

puff

 

De: postgis-users <postgis-users-bounces at lists.osgeo.org> En nombre de Kirill Bykov
Enviado el: jueves, 16 de abril de 2020 17:55
Para: PostGIS Users Discussion <postgis-users at 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 at sbf.ulaval.ca> 
Sent: Thursday, April 16, 2020 6:52 PM
To: postgis-users at lists.osgeo.org <mailto: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> https://postgis.net/docs/RT_ST_MapAlgebra.html

 

Let us know the difference.

 

Pierre

 

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



 

 

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