10

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?

enter image description here

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()
3
  • Can you add more details? Can you mention what you have tried, errors received, are you strictly tied to using QGIS Python, why running this on the command line is not an option, etc.?
    – cm1
    Jun 23, 2017 at 16:36
  • Can you provide some more information about what you're uploading? Are all the rasters parts of the same dataset, such as SRTM 1x1 degree tiles, for example? Or do they need to go into separate tables?
    – nronnei
    Jun 25, 2017 at 20:53
  • I‘ve to upload a few hundred GeoTIFFs in the QGIS cloud. The tiles are exported from a proprietary GIS software. Uploading the .tif files (40 MB) using the QGIS cloud plugin is incredibly slow. Jun 26, 2017 at 6:22

3 Answers 3

6

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)
2
  • There is no error message running this script, but nothing happens. I've already checked the path of the 'bin' folder and all database parameters. The raster2pgsql command works fine the terminal. Have you tested this script? Jun 26, 2017 at 6:26
  • I've tested it several times, does the postgis extension is installed in your target db? I've added a couple of print commands, could you check if the script finds tiff in your folder?
    – NettaB
    Jun 26, 2017 at 7:09
4
+200

+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)
1
  • Thanks for the answer. As of Dec 2021, the link in your answer goes to 404. And what is the auto doing in the code? I have included auto and an error popped up saying unable to read raster file: auto.
    – ay__ya
    Dec 13, 2021 at 21:04
0

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)

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.