20

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.

Improve this question
5
  • raster2pgsql refers to the reference postgis.org/documentation/manual-svn/…
    – Mapperz ♦
    Dec 29, 2011 at 15:04
  • I tried to load raster file (jpg) into post gis using the syntax provided here "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" I could see a raster table created in the database. It was just as shown by Celenius where you do not see any thing under "rast raster" column unless you click on a particular cell. Here is my actual problem. How to view the raster layer from ArcGIS using sql query layer. When I tried adding this layer and validate it, it says "rast raster
    – GISTECH
    Jul 25, 2012 at 16:00
  • I think you should ask this as a new question as you'll get more responses. I don't know how you can connect using ArcGIS but you can use QGIS to access it.
    – djq
    Jul 25, 2012 at 16:56
  • 1
    Note: pgAdminIII shows a blank cell for data beyond a certain size; see ticket #297
    – Mike T
    Jul 25, 2012 at 23:18
  • the above querry works but can any one tell me how to store pixel values/cell values in the table?
    – user21303
    Aug 22, 2013 at 6:16

3 Answers 3

Reset to default
10

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

Improve this answer
3
  • Thanks for the suggestions! Still not able to get it to work, but starting to understand the switches a bit better.
    – djq
    Feb 4, 2012 at 21:29
  • It did work, but I did not understand how this should look within the database. Thanks again for your help.
    – djq
    Feb 4, 2012 at 23:12
  • Did the GUI for loading rasters into pgsql finally arrive? :)
    – astrojuanlu
    Jul 10, 2017 at 15:56
6

SRID? -s 4236 (required)

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

-C Apply raster constraints -- srid, pixelsize etc. to ensure raster is properly registered in raster_columns view. (required)

-M (Vacuum analyze the raster table.) optional

-I (Create a GiST index on the raster column. ) optional but recommended

-t TILE_SIZE (Cut raster into tiles to be inserted one per table row. TILE_SIZE is expressed as WIDTHxHEIGHT.)

myschema.demelevation leave out (yes)

http://postgis.net/docs/using_raster_dataman.html#RT_Loading_Rasters

Improve this answer
1
  • Thanks Mapperz, I did understand partially how raster2pgsql works - however my raster in postgresql contains only one cell which is why I am trying to figure out what arguments to use.
    – djq
    Dec 29, 2011 at 21:47
-2

In the SQL view, you cannot see data when it is large. You can execute the query into a file and it will show the data.

Improve this answer
1
  • 1
    The original (old) question has already an accepted answer and yours is not really an alternative solution to the question, but rather a call for visual inspection.
    – Curlew
    Sep 8, 2013 at 19:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.