Load Raster into PostGIS 2.0 Database Using QGIS – How to

postgis-2.0postgresqlqgisraster

Following on from my attempt to install a postgresql 9.1 db with postgis 2.0 on windows 7, I am trying to figure out how to load a raster. I have successfully loaded a shapefile, and am trying to do the same with a .tif file using the Load Raster to PostGIS plugin (version 0.5.1) in QGIS (version 1.7). I have set up the connection to my db, and am using the following settings:

enter image description here

When I click on the OK button, I get the below error message. I've tried this with a .adf file and also a .tif file, both projected in Albert Equal Area Conic with an SRID of 102003.

Checking parameters...
Connecting to database...
Storing overview 1 on database...
Failed.
Finished storing overview 1.
Storing overview 2 on database...
Failed.
Finished storing overview 2.
Storing overview 3 on database...
Failed.
Finished storing overview 3.
Finished.

This process has not inserted anything into my database, and I don't understand the error message. Some previous related questions are here (asked by me, using a different process on loading rasters) and here (using the same plugin but much earlier in the year).

Best Answer

celenius,

It's quite possible that the QGIS raster loader hasn't been revised yet to fit with the new changes we have in place. I suggest trying the raster2pgsql.exe packaged with the latest windows experimental. It should load tifs just fine.

Instructions here.

http://www.postgis.org/documentation/manual-svn/using_raster.xml.html#RT_Loading_Rasters

Related Solutions

How to Import a Raster into PostGIS

It seems there's a typo on that page; in the line:

python raster2pgsql.py -s 4269 -I -r *.tif -F myschema.demelevation -o elev.sql

The -F parameter should be -t which specifies the table name to import the data into. The part before the . is the optional schema name if you want your table in a schema other than public. The -o parameter specifies the file that is generated by the Python script. This file is the SQL representation of your table definition and the actual data from the source raster, so it can get pretty large. Once this file is generated, you need to run it through psql -d <db_name> -f elev.sql to actually populate the database, after which it can be deleted.

However, what strikes me as odd is that you're calling raster2pgsql.py from the PostGIS 2.0 source directory. Have you actually compiled (by running make) and installed (by running make install as root) PostGIS 2.0? Because it should be on your path, and IIRC the installer automatically updates your PYTHON_PATH environment variable.

As for the missing GDALSetRasterUnitType, the thing I would first check for is that you don't have an earlier version of GDAL libraries installed that Python is picking up instead of 1.8.1. Try gdalinfo --version This site may shed some light on your problem.

[GIS] Loading a raster into a PostGIS 2.0 database on Windows

Celenius,

If you don't use the -t option with size dimensions, then your raster file will come in as a single record. I just noticed an error in the docs which is probably what's confusing you. I'll fix that. The -t should always be followed by a widthxheight.

If you want it to be chunked say in 100x100 pixel width height -- as Mapperz says -- use the -t option.

So your command would be:

raster2pgsql -s 102003  -t 100x100 ras_test.tif ras_test > elev.sql

Take a look at the aerial example in the docs. http://www.postgis.org/documentation/manual-svn/using_raster.xml.html#RT_Raster_Loader

Which is the one a lot of the manual examples use:

raster2pgsql -I -C -e -Y -F -s 26986 -t 128x128 -l 2,4 bostonaerials2008/*.jpg aerials.boston | psql -U postgres -d gisdb -h localhost -p 5432

I know the switches can be a bit daunting because their are so many and most are optional. Perhaps in 2.1 we'll have a gui similar to the shp2pgsql-gui to make this a bit simpler for newbies.

In the latest verson of raster2pgsql there is logic to infer the srid from the georeference info in the tif or other raster file (this version is available as window binary now). Haven't tried that feature yet, but if it works you can even leave out the srid and it will guess (hopefully correctly).

Best Answer

Related Solutions

How to Import a Raster into PostGIS

[GIS] Loading a raster into a PostGIS 2.0 database on Windows

Related Question