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() gdal.stdout.close()
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.