1

I am trying to learn how to import rasters into PostGIS (2.1).

I have read through all the material I can find on doing this and know that the recommended tool for this is the raster2pgsql tool.

I am running the command using the OSGeo Shell after first navigating to the directory where the tool lives. So my commands look like this:

First:

cd C:\Program Files\PostgreSQL\9.3\bin

Then:

raster2pgsql -s 27700  -t 100x100 C:\Users\Public\myraster.tif test8 > rasteradd2.sql

I get the result:

Processing 1/1:  C:\Users\Public\myraster.tif

WARNING: The size of each output tile may exceed 1 GB. Use -t to specify a reasonable tile size

I have also run:

raster2pgsql -M -t 1x1 C:\Users\Public\myraster.tif public.test6 > rasteradd.sql

And got:

Processing 1/1:  C:\Users\Public\myraster.tif

(I based my command on LR1234567's answer to a previous problem here: Loading a raster into a PostGIS 2.0 database on Windows)

I get a .sql file which I then double click and execute and a table is created. However, the data seems to be invalid. QGIS says it has no geometry, and there are just 2 columns (its a 3 band image so should have at least 3 I assume). I have tried opening the file and copying the code directly into the Postgres SQL dialogue. I have also tried running the command without all of the various options.

Thanks for your time

EDIT:

I have tried the following also, taken from this question Loading a raster into a PostGIS 2.0 database on Windows:

raster2pgsql -I -C -e -Y -F -s 27700  -t 100x100 C:\Users\Public\myraster.tif test11 | psql.exe -U postgres -d postgis_21_sample -h localhost -p 5942

This produced the result:

Processing 1/1: C:\Users\Public\coregistered_stack_sub2_reprojected_1percent.tif

WARNING: The size of each output tile may exceed 1 GB. Use -t to specify a reaso
nable tile size
CREATE TABLE
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
----------------------
 t
(1 row)

But I still cannot add the resulting raster to QGIS as a PostGIS layer with Geometry (i.e. there is still no geometry information)

  • Or is there another way to check if the raster upload has worked or not? – user24956 Oct 14 '14 at 9:08
  • 1
    For load ther raster in qgis you have to use dbmanager (right click on the raster table--> add to canvas). Raster don't have a geometry column but a raster column – Sergio Oct 14 '14 at 12:46
  • That's done it! It seems the raster uploads fine, thanks! – user24956 Oct 14 '14 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.