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

postgis-2.0postgresqlrasterwindows

I'm trying to figure out how to load a raster into a PostGIS2.0 database (I have asked previous questions on this topic here and here). I am trying to use the raster2pgsql.exe program provided with PostGIS2.0.

After figuring out that the command prompt in windows needs to be run as an administrator (In Windows 7 to run the command line as an administrator, type cmd into the search bar and hit ctrl + shift + enter) to enable raster2pgsql.exe to function I have sort-of managed to load a raster into my database. I have a raster file called ras_test.tif that I temporarily placed in the bin folder of my postgresql installation. Using the following code I convert and load this raster:

C:\Program Files (x86)\PostgreSQL\9.1\bin>raster2pgsql -s 102003 ras_test.tif -t> elev.sql
Processing 1/1: ras_test.tif

C:\Program Files (x86)\PostgreSQL\9.1\bin>psql.exe -p 5434 -U postgres -d test2 -f elev.sql
BEGIN
psql:elev.sql:2: NOTICE:  CREATE TABLE will create implicit sequence "-t_rid_seq" for serial column "-t.rid"
psql:elev.sql:2: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "-t_pkey" for table "-t"
CREATE TABLE
INSERT 0 1
COMMIT

When I view this table in PostGIS it looks like this:

enter image description here

However, I haven't been able to view this in QGIS, and I am not sure if I loaded it correctly as there appears to be no data in this file. It seems like I have loaded the filename in as a raster, rather than the data content. Have I made any obvious errors that are preventing me from loading a raster into my database?

The PostGIS documentation provides an example of how to load a raster, but I don't understand which arguments are optional, and am still unclear on what I should use if I want to use the default schema. For example, in the following example from the documentation:

raster2pgsql -s 4236 -I -C -M *.tif -F -t myschema.demelevation > elev.sql 
psql -d gisdb -f elev.sql

  1. Do I have to provide an SRID? -s 4236

  2. Are the arguments -I -C -M all optional?

  3. -t appears to be tile size; Do I need to specify this if I do not have a custom schema?

  4. Can I just leave out myschema.demelevation ?

EDIT: I've included the outcome of the suggestions below:

C:\Program Files (x86)\PostgreSQL\9.1\bin>raster2pgsql -s 102003 -t 300x300 ras_test.tif ras_test | psql.exe -U postgres
 -d raster_analysis -h localhost -p 5434
Processing 1/1: ras_test.tif
BEGIN
NOTICE:  CREATE TABLE will create implicit sequence "ras_test_rid_seq" for serial column "ras_test.rid"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "ras_test_pkey" for table "ras_test"
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
COMMIT

This results in a table with two columns rid and rast. rid has four values, nad rast has none. When I try using some more arguments:

C:\Program Files (x86)\PostgreSQL\9.1\bin>raster2pgsql -I -C -e -Y -F -s 102003 -t 300x300 ras_test.tif ras_test1 | psql
.exe -U postgres -d raster_analysis -h localhost -p 5434
Processing 1/1: ras_test.tif
NOTICE:  CREATE TABLE will create implicit sequence "ras_test1_rid_seq" for serial column "ras_test1.rid"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "ras_test1_pkey" for table "ras_test1"
CREATE TABLE
CREATE INDEX
ANALYZE
NOTICE:  Adding SRID constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 49 at RETURN
NOTICE:  Adding scale-X constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 49 at RETURN
NOTICE:  Adding scale-Y constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 49 at RETURN
NOTICE:  Adding blocksize-X constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 49 at RETURN
NOTICE:  Adding blocksize-Y constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 49 at RETURN
NOTICE:  Adding alignment constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 49 at RETURN
NOTICE:  Adding number of bands constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 49 at RETURN
NOTICE:  Adding pixel type constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 49 at RETURN
NOTICE:  Adding nodata value constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 49 at RETURN
NOTICE:  Unable to add constraint "enforce_nodata_values_rast"
CONTEXT:  PL/pgSQL function "_add_raster_constraint_nodata_values" line 40 at RETURN
PL/pgSQL function "addrasterconstraints" line 94 at assignment
PL/pgSQL function "addrasterconstraints" line 49 at RETURN
WARNING:  Unable to add constraint: 'nodata_values'.  Skipping
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 49 at RETURN
NOTICE:  Adding maximum extent constraint
CONTEXT:  PL/pgSQL function "addrasterconstraints" line 49 at RETURN
 addrasterconstraints
----------------------
 t
(1 row)

I get the following output. This results in a new table with the following structure:

enter image description here

I assume that this is not a properly loaded raster as I cannot view the data. Are there any other options that I can try?

EDIT: This last attempt did work, I just was not accessing the raster correctly.

Best Answer

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).

Related Question