How to import raster from PostGIS into Python as dataframe

Question

I am trying to import the raster from PostGIS into a raster for further analysis. I know that I should use psycopg2 to connect to DB, but I do not know how to query the raster from DB, and then convert it into dataframe. Each column should show only the pixel value of the one raster

The aim to do classification, and I need to import single and multiband rasters.

Answer 1

A bit convoluted, but this seems to work:

import geopandas as gpd
import pandas as pd
import psycopg2
con = psycopg2.connect(database="somedbname", user="someuser", password="somepassword",
 host="somehost")
sql = "SELECT x, y, val, geom FROM (SELECT dp.* FROM somerastername, LATERAL ST_PixelAsCentroids(rast, 1) AS dp) foo"
df = gpd.GeoDataFrame.from_postgis(sql, con, geom_col='geom' ) #Create geodataframe
df2 = pd.pivot_table(df, values='val', index='x', columns='y') #Pivot

The sql query should create a table looking like screenshot below:

enter image description here

I then pivot using pandas to this:

df2.head(5)
Out[17]: 
y 1 2 3 ... 1998 1999 2000
x ... 
1 290.527374 291.153809 292.508698 ... 290.498169 294.330933 297.080139
2 290.632050 290.655670 290.633057 ... 293.128540 296.367706 297.665710
3 294.361938 291.505859 291.971100 ... 293.437775 295.462372 297.046967
4 295.409454 294.503357 293.324707 ... 292.185974 293.646912 294.779114
5 296.388123 296.558533 294.026611 ... 289.595337 291.347290 291.802368
[5 rows x 2000 columns]