2

I am trying to import a floating point GeoTIFF image into PostGIS (2.1), using the following command:

raster2pgsql -s 4326 -I -M -F -C tif_dir/*.tif public.map | psql -U postgres -h localhost -d opengeo

If I omit the -C option, then there everything is OK. However, when -C is used, I get errors about numeric field overflow and Unable to add constraint: 'nodata_values'. Skipping.

In either case, select * from raster_columns returns empty.

The nodata value as reported by gdalinfo at the bottom of this question is -3.4028234663852886e+38 (, which is the most negative value of single precision floating point numbers). Is floating point value of this size (32-bit) not supported by PostGIS.

How should I fix this issue here associated with nodata?

raster2pgsql output:

Processing 1/1: tif_dir/prec1_nr.tif
BEGIN
CREATE TABLE

...
NOTICE:  SQL used for failed constraint: ALTER TABLE public.map ADD CONSTRAINT enforce_nodata_values_rast CHECK (_raster_constraint_nodata_values(rast)::numeric(16,10)[] = '{-3.40282346638529e+38}'::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 98 at assignment
PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
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 98 at assignment
PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
WARNING:  Unable to add constraint: 'nodata_values'.  Skipping
...
COMMIT
VACUUM

And, gdalinfo gives the following description of the GeoTIFF in question:

gdalinfo prec1_nr.tif
Driver: GTiff/GeoTIFF
Files: prec1_nr.tif
       prec1_nr.tif.ovr
       prec1_nr.tif.ovr.aux.xml
       prec1_nr.tfw
       prec1_nr.tif.aux.xml
Size is 7200, 3600
Coordinate System is:
GEOGCS["WGS 84",
    DATUM["WGS_1984",
        SPHEROID["WGS 84",6378137,298.257223563,
            AUTHORITY["EPSG","7030"]],
        AUTHORITY["EPSG","6326"]],
    PRIMEM["Greenwich",0],
    UNIT["degree",0.0174532925199433],
    AUTHORITY["EPSG","4326"]]
Origin = (-30.000000000000000,30.000000000000000)
Pixel Size = (0.008333333333333,-0.008333333333333)
Metadata:
  AREA_OR_POINT=Area
Image Structure Metadata:
  COMPRESSION=LZW
  INTERLEAVE=BAND
Corner Coordinates:
Upper Left  ( -30.0000000,  30.0000000) ( 30d 0' 0.00"W, 30d 0' 0.00"N)
Lower Left  ( -30.0000000,   0.0000000) ( 30d 0' 0.00"W,  0d 0' 0.01"N)
Upper Right (  30.0000000,  30.0000000) ( 30d 0' 0.00"E, 30d 0' 0.00"N)
Lower Right (  30.0000000,   0.0000000) ( 30d 0' 0.00"E,  0d 0' 0.01"N)
Center      (   0.0000000,  15.0000000) (  0d 0' 0.01"E, 15d 0' 0.00"N)
Band 1 Block=128x128 Type=Float32, ColorInterp=Gray
  Min=0.000 Max=337.000 
  Minimum=0.000, Maximum=337.000, Mean=6.705, StdDev=18.508
  NoData Value=-3.4028234663852886e+38
  Overviews: 3600x1800, 1800x900, 900x450, 450x225
  Metadata:
    STATISTICS_COVARIANCES=342.561389035033
    STATISTICS_MAXIMUM=337
    STATISTICS_MEAN=6.7054569494428
    STATISTICS_MINIMUM=0
    STATISTICS_STDDEV=18.508414006474
  • The nodata value is being cast to numeric(16,10), which -3.40282346638529e+38 will overflow. Clearly, -3.40282346638529e+38 is supposed to be 0, but presumably during some translation of this GeoTiff previously, got encoded due to floating point error as this crazy value. I would suggest reloading without -C and then setting appropriate values using RT_AddRasterConstraints, possibly after running an update query to convert your -3.402...+38 to 0 or some other sensible value. – John Powell Oct 16 '14 at 12:46
  • @JohnBarça Thanks, I'll try change no_data. What's the range for numeric(16,10). Is it possible to tell raster2pqsql to use a larger range than numeric(16,10). – tinlyx Oct 16 '14 at 13:19
  • @JohnBarça -3.40282346638529e+38 is the smallest (most negative value) single precision number. It's very far from 0, and I guess the data provide chose this negative value because zero is a valid value in this GeoTIFF. Do you know what's the most negative value of numeric(16,10)? – tinlyx Oct 16 '14 at 13:19
  • Sorry, yes, very, very far from 0, but way beyond the precision of numeric(16,10), which means 16 total digits and up to 10 after the decimal. I have no idea if you can change this in raster2pgsql and having browsed the source can't even see where it is set. – John Powell Oct 16 '14 at 13:44
  • yes, -999999.9999999999. Try inserting -1000000 and you will get, ERROR: numeric field overflow DETAIL: A field with precision 16, scale 10 must round to an absolute value less than 10^6 – John Powell Oct 16 '14 at 13:46

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.