7

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.

3
  • This issue is related to gis.stackexchange.com/questions/118120/… As a workaround you can try to run raster2pgsqlwithout the -C param.
    – rafatower
    Dec 1, 2014 at 17:38
  • Could you give more details about this answer, instead of just a link? How would raster2pgsql specifically be applied for OP's problem?
    – Simbamangu
    Dec 1, 2014 at 18:17
  • Without -C param data can be added in to postgis without raster constraints. But without raster constraints how it can be published in GeoServer and MapServer ? Can you explain please? Jun 28, 2017 at 5:01

1 Answer 1

0

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
1
  • Could you give additional information on how this solves the question? Jan 22, 2021 at 13:54

Your Answer

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

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