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

0 comments :
Post a Comment