Accessing raster column of a table via psycopg2

Question

I have a table in PostGIS with several rasters, which have the same spatial reference, but the tiffs are from different dates. Now I am trying to access the column "rast" to detect changes between rows. My aim is to subtract the pixel value of the first row from the second and then from the third row's pixel values, and so on.

How can I iterate over the rows and subtract the pixel values of each row from the following row?

[enter image description here][1]

#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys

conn = None

conn = psycopg2.connect(database="postgres", user="postgres",host="localhost", password="password")
cur = conn.cursor()

cur.execute('SELECT * from my_table')

while True:

   row = cur.fetchone()

   if row == None:
     break

   rast_col = row[1]

I imported several rasters, which have the same spatial area but diffrent dates via following command:

C:\Program Files\PostgreSQL\9.6\bin>raster2pgsql -s 4326 -F -I "C:\User\Desktop\Data\*.tif" public.all_data|psql -U User -h localhost -p 5432

This is the table that was created in postgresql after importing the data [1]: https://i.stack.imgur.com/uBHX3.jpg

Each row is representing one raster image in "TIFF" format. The column "rast" contains the pixel values. My aim is to calculate the diffrence between the adjacent rows...same like the lag windows function, but it does not work on raster column type...

The only thing, that i fixed was calculating the diffrence between two raster images. For that I had to create for each row a separate table. U can see it below:

CREATE TABLE table1 AS SELECT * FROM my_tabke WHERE rid=1;
CREATE TABLE table2 AS SELECT * FROM my_table WHERE rid=2;

And then I did a simple MapAlgebra Operation on both tables like this:

SELECT ST_MapAlgebra(t1.rast,t2.rast, '([rast1]-[rast2])') AS rast INTO diffrence FROM table1 t1, table2 t2;

but this is just the diffrence between two rasters, and for the MapAlgebra operation I had to create extra tables for each raster images. But I have more the 40 raster images in one table, and I want to detect the change of all adjacent rows between my table.

Might you be looking for something like this: http://stackoverflow.com/questions/41682882/find-diff-between-2-numbers-in-different-rows ? — Ilja Everilä, Jan 21 '17 at 18:50
Yes it might be but I couldnt solve it ... is there a way to loop over the pixel values in the column "rast" and calculate the diffrence between each row? — , Jan 24 '17 at 21:32
@Ilja Eveilä your following link http://stackoverflow.com/questions/24691462/postgresql-calculate-difference-between-rows was a good hint, but in my case I am working with "rast" column type. So that is the reason, why I get an error message... — , Jan 25 '17 at 18:56
To me [`ST_MapAlgebra`](http://postgis.net/docs/RT_ST_MapAlgebra_expr.html) sounds like it might aid you in your quest for difference between 2 rasters. Cannot say more since even though I'm somewhat familiar with PostGIS, I never had to use rasters. — Ilja Everilä, Jan 25 '17 at 19:13
Btw it'd help you a lot in getting good answers if you'd include your table definition and a bit more in the way of "rasters ordered by ..." etc. — Ilja Everilä, Jan 25 '17 at 19:39
ok u can see more details to my question on the top, I edited my question — , Jan 25 '17 at 21:01

Ilja Everilä · Accepted Answer · 2017-01-26T10:22:49.563

The lag() window function should work on raster columns just like on any old column. It just selects the value from a row before the current offset by some amount in the window frame.

You of course cannot just subtract rasters using Postgresql operators – not without overloading at least.

In order to calculate the differences between adjacent rasters ordered by rid you should pass the lagged raster as an argument to ST_MapAlgebra

SELECT ST_MapAlgebra(rast, lag(rast) OVER (ORDER BY rid DESC),
                     '[rast1] - [rast2]')
FROM my_table;

Since lag() selects rows before the current row in the partition, the rows are ordered by rid in descending order; 2 comes before 1 etc. Also because a window frame by default consists only of rows that come before the current row, this is easier than using lead() and a frame clause that selects rows following the current.

Disclaimer

I've not used rasters and you may have to fine tune the query to suit your specific needs.

I tried out you example, but I got following Error message --> ERROR: windows function lag requires over clause. — , Jan 26 '17 at 09:59
Sorry, forgot to put the over clause where it belongs (with the `lag()` call). — Ilja Everilä, Jan 26 '17 at 10:23

Accessing raster column of a table via psycopg2

1 Answers1