<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1251">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255);">
<span style="font-family: Tahoma; font-size: 13.3333px;">Dear All!</span><br>
</div>
<div dir="ltr">
<div dir="ltr">
<div style="margin: 0px; font-family: "Times New Roman"; color: black; background-color: rgb(255, 255, 255);">
<div style="margin:0px">
<div style="margin: 0px; font-size: 12pt; font-family: Calibri, Arial, Helvetica, sans-serif; background-color: white;">
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
<br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
I calculate vegetation indices on rasters (Landsat 8 scenes).<br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
The calculation of SAVI=(NIR-RED)/(NIR+RED+L)*(1+L) takes about 2 minutes to complete.</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
This is enormous time compared to the same operation in QGIS which takes several seconds.</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
<br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
Here is the query<span> </span><span lang="ru" style="margin:0px">(</span><span lang="en-US" style="margin:0px">it’s defined as postgres UDF and $1 parameter is L):</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
<span lang="en-US" style="margin:0px"><br>
</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
BEGIN</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px 0px 0px 27pt">
DROP TABLE IF EXISTS savi;</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px 0px 0px 27pt">
CREATE TABLE savi AS</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px 0px 0px 27pt">
SELECT bandt5.rid, ST_MapAlgebra(</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px 0px 0px 81pt">
bandt4.rast,</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px 0px 0px 81pt">
bandt5.rast,</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px 0px 0px 81pt">
'CASE WHEN ([rast2.val] + [rast1.val] = 0) THEN NULL ELSE (([rast2.val] - [rast1.val])*(1+$1) / ([rast2.val] + [rast1.val]+$1)::float) END', '32BF'</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px 0px 0px 27pt">
<span style="margin:0px"> </span>)</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px 0px 0px 27pt">
FROM bandt5, bandt4 WHERE bandt5.rid=bandt4.rid;</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px 0px 0px 27pt">
RETURN 1;</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
END;</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
I used the data from USGS, Landsat 8 Level 2, bands are stored in separate tables.<br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
DB indices are created for every band by raster2pgsql.<br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
Tile size is 128x128.<br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
The whole raster2pgsql command: for each tif band:<br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
"raster2pgsql -s 4326 -N -32767 -t 128x128 -I -C -M -d bandX.tif public.bandtX > bandX.sql".<br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
Afterwards bandX.sql files were ingested by psql.</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
<br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
Are there any ways to improve the performance?<br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
<br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
Thank you!</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
---</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
Regards,</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px">
Kirill</p>
</div>
</div>
</div>
<div>
<div id="x_x_Signature">
<div id="x_x_divtagdefaultwrapper" dir="ltr" style="font-size: 12pt; font-family: Calibri, Helvetica, sans-serif; color: rgb(0, 0, 0);">
<div id="x_x_divtagdefaultwrapper" dir="ltr" style="font-size: 12pt; font-family: Calibri, Helvetica, sans-serif; color: rgb(0, 0, 0);">
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin-top:0px; margin-bottom:0px; margin-top:0px; margin-bottom:0px; margin-top:0; margin-bottom:0">
<br>
</p>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>