How to import QGIS Raster into PostGIS

I have created a IDW heatmap using QGIS 1.8.0 and need to import the raster into PostGIS 2.0.1. QGIS saves the raster layer as a .asc file.

How do you import the raster into PostGIS as a Raster? What format should QGIS save the raster as? Can pgAdmin's PostGIS Shapefile and DBS Loader be used?

Answer

I don't believe the Shapefile loader will work in your case. However, PostGIS comes with a raster loading tool called raster2pgsql. This tool will load any GDAL supported raster format into PostGIS Raster. It is a command-line tool so to execute it you just need to run:

raster2pgsql raster_options schema.table_name > output.sql

So, the tool will take the input raster options (which includes the raster file you want to load, or a folder of files) and then generate a SQL script which you can then use to load the data into PostGIS using the command:

psql -d database_name -f output.sql

Of course you can also combine the commands using UNIX pipes:

raster2pgsql raster_options schema.table_name | psql -d database_name -u username -h host -p port

Chapter 5 of the PostGIS documentation provides all the necessary details you need with regards the possible raster_options you can use. If you want to know if the format you have is supported by the tool then you can run the command:

raster2pgsql -G

The .asc file is an ASCII grid which is normally supported by GDAL so you shouldn't have too many problems.