Using raster2pgsql in Python console of QGIS?

Question

I have to upload GeoTiFF files into a PostGIS/PostgreSQL database and I am new to raster2pgsql.

How do I run a raster2pgsql command in the Python console?

import psycopg2
import subprocess

db_name = 'enter_qgiscloud_db'
db_host = 'db.qgiscloud.com'
db_user = 'enter_qgiscloud_user'
db_password = 'enter_qgiscloud_pw'

conn = psycopg2.connect( "dbname={0} host={1} user={2} password={3}".format( db_name, db_host, db_user, db_password ) )
cursor = conn.cursor()

cmds = 'raster2pgsql -s 3857 -p -Y -I -C -M C:\qgis_cloud_data\ -F -t auto .tif | psql'
subprocess.call(cmds, shell=True)

cursor.execute(cmds)
conn.commit()

Answer 1

Here's a script that iterates on all the tiff rasters in a folder and creates a table with auto tile size for each (based on this link):

import psycopg2
import subprocess 
import sys, os

input_path = " C:\\qgis_cloud_data\\"
#Change to the location of pgsql bin folder
os.environ['PATH'] = r';C:\pgsql\9.6\bin'
os.environ['PGHOST'] = 'localhost'
os.environ['PGPORT'] = '9008'
os.environ['PGUSER'] = 'postgres'
os.environ['PGPASSWORD'] = 'dbpass'
os.environ['PGDATABASE'] = 'dbname'

for raster in os.listdir(input_path):
    if raster.endswith(".tif"):
       name = raster.split(".tif")[0]
       # Print the foound tiff name
       print(name)     
       raster = os.path.join(input_path, raster)                    
       # Print the full path of the tiff raster
       print(raster)
       rastername = str(name)
       rasterlayer = rastername.lower()
       conn = psycopg2.connect(database="dbname", user="postgres", host="localhost", password="dbpass", port=9008)
       cursor = conn.cursor()
       cmds = 'raster2pgsql -s 3857 -t auto "' + raster + '" |psql'
       subprocess.call(cmds, shell=True)

Answer 2

+1 for NettaB's answer but just wanted to add that if all of your tiff files are in the same folder you should be able to accomplish this using a single command (be it from the command line or via subprocess in Python):

# Set environment variables for database connection
set PGHOST=db.qgiscloud.com
set PGPORT=5432
set PGUSER=enter_qgiscloud_user
set PGPASSWORD=enter_qgiscloud_pw
set PGDATABASE=enter_qgiscloud_db

# Call the raster2pqsql utility
raster2pgsql -s 3857 -C -F -t auto C:/qgis_cloud_data/*.tif schema.target_table | psql

This will create a new table named schema.target_table and push the data into it. If you need some more info on the switches to use, this page is useful - and includes some examples.

With the Python implementation you don't need to use Psycopg unless you plan to execute SQL queries - if you're just loading the data straight in you only need the raster2pgsql utility. So your code can be adapted to:

import os
import subprocess

db_name = 'enter_qgiscloud_db'
db_host = 'db.qgiscloud.com'
db_user = 'enter_qgiscloud_user'
db_password = 'enter_qgiscloud_pw'

# Set pg password environment variable - others can be included in the statement
os.environ['PGPASSWORD'] = db_password 

# Build command string
cmd = 'raster2pgsql -s 3857 -C -F -t auto C:/qgis_cloud_data/*.tif schema.target_table | psql -U {} -d {} -h {} -p 5432'.format(db_user,db_name,db_host)

# Execute
subprocess.call(cmd, shell=True)

Answer 3

For those Who have the same problem in Ubuntu, Here is the solution that worked for me, thanks to @NettaB 's solution:

import psycopg2
import subprocess
import os

input_path = "/home/projects/outputs/"
#Change to the location of pgsql bin folder
os.environ['PATH'] = r'/usr/bin'       # path to the raster2pgsql file
os.environ['PGHOST'] = 'localhost'     # localhost server 
os.environ['PGPORT'] = '5432'          # port (default is this)
os.environ['PGUSER'] = 'postgres'      # postgres username
os.environ['PGPASSWORD'] = '1234'‌      # my password
os.environ['PGDATABASE'] = 'p3'        # my database's name

for raster in os.listdir(input_path):
    print(raster)
    if raster.endswith(".tif"):
       name = raster.split(".tif")[0]
       # Print the found tiff name
       print(name)
       raster = os.path.join(input_path, raster)
       # Print the full path of the tiff raster
       print(raster)
       rastername = str(name)
       rasterlayer = rastername.lower()
       conn = psycopg2.connect(database="p3", user="postgres", host="localhost", password="1234", port=5432)
       cursor = conn.cursor()
       cmds = 'raster2pgsql -s 3857 -t auto "' + raster + '" |psql'
       subprocess.call(cmds, shell=True)