I am new in PostgreSQL and psql. What is the method to insert a raster geotiff in a postgreSQL database using command psql? What is the best format? Someone indicates to save a raster image as BLOB format.

  • What is the size of your geotiff: 2GB or considerably larger? Moreover psql is not the best software for working with PostgreSQL database. Use Database Master 5 or pgAdmin 3 – AmShegar Feb 14 '14 at 5:47

I used this process recently through the cmd window (Windows 7):

  • Step 1: Positionning inside raster2pgsql directory

    cd C:\Program Files\PostgreSQL\9.2\bin
  • Step 2: Generate SQL script (see options legend below)

    raster2pgsql -c -s 3947 -t 70x111 -N 0 -I "D:\myRaster.tif" schema.table > "D:\myRaster.sql"

    -c : create a new table in specified schema

    -s : SRID to be used (replace with yours)

    -t : raster size in columns x lines (open in QGIS for example and get this info from layer properties)

    -N : No-data pixels value (see also in QGIS > Layer properties)

    -l : creation of an index for rast column (special column for storing raster geometry)

  • Step3: Script execution (can also be launched in a SQL window within pgAdmin III)

    psql -U userName -d dataBase -h host -f "D:\myRaster.sql"

This worked fine for me... You can check in QGIS if your raster was imported properly in your database (with DB Manager plugin for instance).

Hope this helps!

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.