1

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.

1 Answer 1

4

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]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.