Uploading raster format (*.tif) to PostGIS through raster2pgsql

Question

I'm new to PostGIS and raster file format. I'm currently working on NetCDF files and I want to import them into PostGIS through curl command line tool but its failed for me. So I jumped on to GDAL v1.9.2. First I converted it into gtiff format and then upload it into PostGIS 2.0. I followed the steps in the below link: http://www.postgis.org/documentation/manualsvn/using_raster.xml.html#RT_Raster_Loader to upload it. But I got some issues in that.

I used the below command to convert from single NetCDF subdataset to single TIFF file.

gdal_translate -of Gtiff -a_srs EPSG:4326 NETCDF:"3z.nc":aabbc archv_4d.tif

It successfully created. now the problem starts, I used the below command to upload the .tiff file to PostGIS:

    \9.2\bin>raster2pgsql -I -C -F -s 4326
     -d -t 256x256 project/index_u.tif -b 1 public.td_test >test.sql

    \9.2\bin>psql -U postgres -d template_postgis_20 -f test.sql -h localhost -p 5432

The response was:

psql:test.sql:2: NOTICE:  CREATE TABLE will create implicit sequence "td_te
st_rid_seq" for serial column "td_test.rid"
psql:test.sql:2: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"td_test_pkey" for table "td_test"
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
CREATE INDEX
ANALYZE
psql:test.sql:11: 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
psql:test.sql:11: 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
psql:test.sql:11: 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
psql:test.sql:11: 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
psql:test.sql:11: 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
psql:test.sql:11: 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
psql:test.sql:11: 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
psql:test.sql:11: 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
psql:test.sql:11: 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
psql:test.sql:11: NOTICE:  Unable to add constraint: enforce_nodata_values_rast
CONTEXT:  PL/pgSQL function _add_raster_constraint_nodata_values(name,name,name)
 line 48 at RETURN
PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 94 at assignm
ent
PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,bo
olean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 a
t RETURN
psql:test.sql:11: NOTICE:  SQL used for failed constraint: ALTER TABLE public.td_test ADD CONSTRAINT enforce_nodata_values_rast CHECK (_raster_constraint_n
odata_values(rast)::numeric(16,10)[] = '{1.26765060022823e+030}'::numeric(16,10)
[])
CONTEXT:  PL/pgSQL function _add_raster_constraint_nodata_values(name,name,name)
 line 48 at RETURN
PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 94 at assignm
ent
PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,bo
olean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 a
t RETURN
psql:test.sql:11: NOTICE:  Returned error message: numeric field overflow
CONTEXT:  PL/pgSQL function _add_raster_constraint_nodata_values(name,name,name)
 line 48 at RETURN
PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 94 at assignm
ent
PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,bo
olean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 a
t RETURN
psql:test.sql:11: WARNING:  Unable to add constraint: 'nodata_values'.  Skipping

CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,
boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean)
 line 53 at RETURN
psql:test.sql:11: 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
psql:test.sql:11: 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)...

The PostGIS table look with only serial and rid [pk] values NO raster data.

Answer 1

I am not sure what your test.sql consists of but to upload a tif I ignored some of the flags you had and combined your two separate commands into one

raster2pgsql.exe -s 4326 -d -C -I -M -l 2,8,16,32 project/index_u.tif -F -t 256x256 public.td_test | psql -d template_postgis_20 -U postgres  -p 5432 -h localhost