Insertion of a raster geotiff in PostgreSQL database

Question

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.

Answer 1

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!