My goal is to create a system that will take a GeoTIFF format raster image and programmatically import it into a Postgres table using PostGIS raster2pgsql. In order to get this done with minimal intermediate files, I am using the Python subprocess module to pipe the stream from the raster2pgsql command into the psql command directly.

Here is some relevant code:

def importRaster(rasterFileName, rasterId):
    rasterFileName = config.rasterImportFolder() + "/" + rasterFileName 
    tableName = "raster" + str(rasterId)

    env = os.environ.copy()
    env["PGPASSWORD"] = config.dbPassword()

    gdal = subprocess.Popen(("raster2pgsql", "-s", config.rasterSourceProjection(),
            "-I", "../" + rasterFileName, "-F", "-t", "100x100", "-d", tableName), 
            stdout=subprocess.PIPE, cwd=os.path.dirname(os.path.realpath(__file__)), env=env)

    psql = subprocess.Popen(("psql", "-U", config.dbUser(), config.dbName()), 
            stdin=gdal.stdout, stdout=subprocess.PIPE, env=env)

    print gdal.stdout.read()

This solution has always been fairly unstable, but when running on my Windows machine it was bearable. However, I recently migrated my test environment to a Debian based distribution on a Raspberry Pi 3. I was expecting a decrease in speed/performance, but there was also a significant decrease in reliability, with the command failing nearly every time.

I started printing the GDAL process output, as you can see in the code, and I discovered a very sporadic collection of errors occurring from attempt to attempt. Sometimes I will get something to the tune of "Premature end of WKB data", sometimes I will get "relation raster6 does not exist", and sometimes the piped data will completely miss the commit query at the end of the stream and no action will be taken at all. It seems to me that the pipe from GDAL to PSQL is randomly skipping or corrupting the queries from the output SQL. This would explain a missing or corrupted CREATE TABLE causing the "relation does not exist" error. It would also explain corrupted WKB data strings and the missing commit statement. If I run these commands manually in the console with the pipe, the process works completely fine.

I have considered running these processes separately and streaming the data from raster2pgsql into an SQL file as an intermediary. However, these SQL files are quite large in my application (greater than half a gigabyte) and are not ideal for the types of machines this software will be running on.

Why does it seem that Python subprocess is dumping some of the SQL stream during the pipe?

I would also like to add that I am using a very similar process for piping from shp2pgsql to psql. In this case, there is much less data due to smaller file sizes and the pipe works without error consistently. It seems to be a volume issue, but I was under the impression that the subprocess module could handle large data volumes.

  • I used to have a small script to batch insert a dir of rasters; running the subprocess.run() (or subprocess.call() for Python < 3.5) wrapper in Shell mode (shell = True) via dynamically constructed string never disappointed me. there are of course security considerations concerning the use of the shell, and this might not suite you for other reasons, but might be worth a try – ThingumaBob Jun 15 '18 at 17:10

Your Answer

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

Browse other questions tagged or ask your own question.