8

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()
  • 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 '17 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 '17 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. – eclipsed_by_the_moon Jun 26 '17 at 6:22
3
+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)
4

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)
  • 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? – eclipsed_by_the_moon Jun 26 '17 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 '17 at 7:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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