Getting started with Raster and PostGIS - I

Steps for loading a raster in PostGIS (PgSql 9.4/ PostGIS 2.2) and pull it from database to view in QGIS 2.14.17.

1. Generate SQL for raster [NOTE: Do not create PostGIS table name starts with underscore, it creates problem while adding raster to QGIS from PostGIS database using DB Manager]

        raster2pgsql -s 4326 -I -C -M -F -t 50x50 -N nan biodiv_ssolnw.tif > biodiv_ssolnw.sql

2. Import generated raster into PostGIS database [Make sure to enable postgis extension in db]

         psql -h localhost -U postgres -d ecolservicedb -f biodiv_ssolnw.sql

the output is:
Processing 1/1: ags_473038164.tif
BEGIN
CREATE TABLE

INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
…
…
….
CREATE INDEX
ANALYZE
NOTICE:  Adding SRID constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding scale-X constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding scale-Y constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding blocksize-X constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding blocksize-Y constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding alignment constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding number of bands constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding pixel type constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding nodata value constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding out-of-database constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding maximum extent constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
 addrasterconstraints
----------------------


(1 row)

COMMIT
VACUUM

3. Check if your raster loaded properly- In PostGIS SQL query builder

            SELECT (ST_Metadata(rast)).* FROM TABLENAME LIMIT 1;

                                              
           




         SELECT ST_Metadata(rast) FROM TABLENAME LIMIT 1;

4. Retrieve the Raster in *.tiff format from PostGIS table

                      gdal_translate PG:"dbname=ecolservicedb host=localhost user=ecolservice         password=ecolservice port=5432 mode=2 schema=public column=rast table=ags_473038164" test.tiff 

5. Then check the histogram of newly created raster from PostGIS :
                  gdalinfo test.tiff
                 gdalinfo –hist test.tiff

6. Remove meta search catalog client to prevent from QGIS Crash while pulling raster from DB Manager

7. Connect to PostGIS database from QGIS using  “Add PostGIS Layer” then Open ‘Database -> DB Manager’

8. Add Raster from PostGIS to Qgis, In QGIS 2.18 Database->DB Manager->DB Manager from Menu. Then Add to Canvas.

References:
http://postgis.17.x6.nabble.com/raster2pgsql-is-not-creating-a-raster-table-in-postgis-td5007520.html
https://gis.stackexchange.com/questions/107617/why-does-qgis-2-4-crash-at-every-shutdown


*Re-project raster from Pseudo web mercator to WGS 84.
gdalwarp -s_srs EPSG:3785 -t_srs EPSG:4326 biodiv_ssolnw.tif biodiv_
ssolnw_wgs84.tif

PostGIS , PostgreGIS , QGIS , Raster