How do I import/export raster data?

Importing Data

You can import raster data using either raster2pgsql, which is part of the PostGIS distribution, or gdal_translate, which is part of GDAL.

The raster2pgsql is usually easier for loading, as the options for controlling the load are exposed directly.

Exporting Data

The gdal_translate utility is usually easier for exporting raster data.

PGHOST=localhost \
PGPORT=5432 \
PGUSER=postgres \
PGPASSWORD=password \
gdal_translate \
    -of PNG \
    -outsize 10% 10% \
    "PG:dbname=db table=tbl" \
    foo.png

You can also use SQL “where” clauses in your export, adding the filter you would like to be placed after the “where” into your connection string. Note that the single quotes in the filter have been artfully escaped.

PGHOST=localhost \
PGPORT=5432 \
PGUSER=postgres \
PGPASSWORD=password \
gdal_translate \
    -of PNG \
    -outsize 10% 10% \
    "PG:dbname=db table=tbl where='filename=\'abcd.sid\'' " \
    outfile.png

You can also use spatial filters in the “where” clause.

PGHOST=localhost \
PGPORT=5432 \
PGUSER=postgres \
PGPASSWORD=password \
gdal_translate \
    -of PNG \
    -outsize 10% 10% \
    "PG:dbname=db table=tbl where='ST_Intersects(rast, ST_SetSRID(ST_Point(-71.0,42.3),4326))' " \
    outfile.png