In this tutorial, we will learn how to read raster data from postgis table using python.
Python is a powerful tool for raster data analysis due to rich library ecosystem: Python has a large number of libraries specifically designed for geospatial data analysis, such as rasterio, rioxarray, gdal, geopandas, shapely, etc. These libraries provide a wide range of functions and tools to work with and analyze raster data.
Before reading raster data from postgis using python, we will set up conda environment and install required python GIS packages for this task. Make sure conda is installed on your system. Use following commands to create a conda environment and to install python libraries.
|
1 2 3 4 5 6 7 |
(base) geoknight@pop-os:~$conda create -n spatial-dev.guru python=3.10 (base) geoknight@pop-os:~$conda activate spatial-dev.guru (spatial-dev.guru) geoknight@pop-os:~$conda install -c conda-forge geocube (spatial-dev.guru) geoknight@pop-os:~$conda install -c conda-forge pygeos (spatial-dev.guru) geoknight@pop-os:~$conda install -c conda-forge rasterio (spatial-dev.guru) geoknight@pop-os:~$conda install -c conda-forge rioxarray (spatial-dev.guru) geoknight@pop-os:~$conda install -c conda-forge psycopg2 |
Step by step tutorial for reading a raster from a PostGIS table using Python:
- Import necessary modules:
psycopg2for connecting to the PostgreSQL databaserasterio.iofor working with raster files in memoryrioxarrayfor opening and processing the raster data
- Configure the database connection:
- Define the database name, username, password, host, and port.
- Use
psycopg2to connect to the database and create a cursor.
- Define the table name:
- Specify the name of the table in PostGIS that contains the raster data.
- Read the raster from PostGIS:
- Use the cursor to execute a SQL query that selects the raster data using the
ST_AsGDALRasterfunction. - Fetch the result using the
fetchonemethod.
- Use the cursor to execute a SQL query that selects the raster data using the
- Load the raster in memory:
- Create a
MemoryFileobject using the result from the previous step. - Open the memory file using
rioxarray.
- Create a
- Read the raster in memory:
- Use the
open_rasteriomethod fromrioxarrayto read the in-memory raster data and return aDatasetobject.
- Use the
The raster data is now stored in the raster_dataset object and can be manipulated and processed using rioxarray.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
# Import necessary modules import psycopg2 from rasterio.io import MemoryFile import rioxarray as riox # DB Configuration dbname = "postgres" dbuser = "postgres" dbpass = "admin" dbhost = "localhost" dbport = "5432" # Connect to database using psycopg2 try: conn = psycopg2.connect(database=dbname, user=dbuser, password=dbpass, host=dbhost, port=dbport) curs = conn.cursor() print("Database connected successfully") except: print("Database not connected successfully") table_name = "sample_raster" # Read raster from postgis using ST_AsGDALRaster function curs.execute(f"""select ST_AsGDALRaster(st_union(rast), 'GTIFF') from {table_name}""") # Fetch result result = curs.fetchone() # Load raster in memory file using MemoryFile module of rasterio inMemoryRaster = MemoryFile(bytes(result[0])) # Read in memoery raster using rioxarray raster_dataset = riox.open_rasterio(inMemoryRaster) |
I hope this tutorial will create a good foundation for you. If you want tutorials on another GIS topic or you have any queries, please send an email at contact@spatial-dev.guru.
Pingback: Import rasters file to PostGIS database using raster2pgsql - Spatial Dev Guru