Opened 10 years ago

Closed 10 years ago

#4736 closed defect (fixed)

PostGIS raster driver: stats reporting no data

Reported by: dzwarg Owned by: dzwarg
Priority: highest Milestone:
Component: GDAL_Raster Version: svn-trunk
Severity: critical Keywords: postgisraster, postgis_raster
Cc:

Description (last modified by dustymugs)

gdalinfo reports "The block (0,0) is empty", and gdal_translate succeeds, but exports rasters with all zero values.

The test raster can be downloaded at:

http://dev.calsurv.org/sft00.zip

The archive is too big to attach to this ticket. Sorry.

The raster was loaded into PostgreSQL using

raster2pgsql -s 4326 -t 10x10 -I -C sft00.tif test_1808 | psql -d test

If we focus on just one tile (rid=1108)...

gdalinfo --debug on -stats "PG:host=localhost dbname=test port=5432 user=postgres table=test_1808 where='rid=1108'"

The output...

PostGIS_Raster: PostGISRasterDataset::GetConnectionInfo(): Mode: 1
Schema: public
Table: test_1808
Column: rast
Where: rid=1108
Host: localhost
Port: 5432
User: postgres
Password: 
Connection String: host=localhost dbname=test port=5432 user=postgres 
PostGIS_Raster: PostGISRasterDataset::SetRasterProperties(): Query: select cols.column_name from information_schema.constraint_column_usage as cols join information_schema.table_constraints as constr on constr.constraint_name = cols.constraint_name where cols.table_schema = 'public' and cols.table_name = 'test_1808'
PostGIS_Raster: PostGISRasterDataset::SetRasterProperties(): Query: select (foo.md).*, foo.rast from (select rast, st_metadata(rast) as md from public.test_1808 where rid=1108) as foo
PostGIS_Raster: PostGISRasterDataset::SetRasterProperties(): adfGeoTransform = {-75.784302, 0.204545, 0.000000, 77.680834, 0.000000,-0.204423}
PostGIS_Raster: PostGISRasterDataset::SetRasterProperties(): Raster size = (10, 10)
PostGIS_Raster: PostGISRasterDataset::SetRasterProperties(): Block dimensions = (10 x 10)
PostGIS_Raster: PostGISRasterRasterBand constructor: Band created (srid = 4326)
GDAL: GDALOpen(PG:host=localhost dbname=test port=5432 user=postgres table=test_1808 where='rid=1108', this=0x62f650) succeeds as PostGISRaster.
Driver: PostGISRaster/PostGIS Raster driver
Files: none associated
Size is 10, 10
Coordinate System is:
GEOGCS["WGS 84",
    DATUM["WGS_1984",
        SPHEROID["WGS 84",6378137,298.257223563,
            AUTHORITY["EPSG","7030"]],
        AUTHORITY["EPSG","6326"]],
    PRIMEM["Greenwich",0,
        AUTHORITY["EPSG","8901"]],
    UNIT["degree",0.0174532925199433,
        AUTHORITY["EPSG","9122"]],
    AUTHORITY["EPSG","4326"]]
Origin = (-75.784301562500005,77.680833536363593)
Pixel Size = (0.204545041250000,-0.204422967727273)
OGRCT: PROJ >= 4.8.0 features enabled
OGRCT: Source: +proj=longlat +datum=WGS84 +no_defs 
OGRCT: Target: +proj=longlat +datum=WGS84 +no_defs 
Corner Coordinates:
Upper Left  ( -75.7843016,  77.6808335) ( 75d47' 3.49"W, 77d40'51.00"N)
Lower Left  ( -75.7843016,  75.6366039) ( 75d47' 3.49"W, 75d38'11.77"N)
Upper Right ( -73.7388512,  77.6808335) ( 73d44'19.86"W, 77d40'51.00"N)
Lower Right ( -73.7388512,  75.6366039) ( 73d44'19.86"W, 75d38'11.77"N)
Center      ( -74.7615764,  76.6587187) ( 74d45'41.67"W, 76d39'31.39"N)
Band 1 Block=10x10 Type=Float32, ColorInterp=Undefined
PostGIS_Raster: PostGISRasterRasterBand::IReadBlock: The query = select rid, rast from public.test_1808 where rast ~ st_setsrid(st_makebox2d(st_point(-75.784302, 75.636604), st_point(-73.738851,77.680834)),4326) and rid=1108
PostGIS_Raster: PostGISRasterRasterBand::IReadBlock: The block (0, 0) is empty
  Minimum=0.000, Maximum=0.000, Mean=0.000, StdDev=0.000
  Overviews: arbitrary
  Metadata:
    STATISTICS_MAXIMUM=0
    STATISTICS_MEAN=0
    STATISTICS_MINIMUM=0
    STATISTICS_STDDEV=0
GDAL: GDALClose(PG:host=localhost dbname=test port=5432 user=postgres table=test_1808 where='rid=1108', this=0x62f650)

The stats are incorrect as...

psql -d test -c "SELECT (ST_SummaryStats(rast)).* FROM test_1808 WHERE rid = 1108;"

 count |        sum        |        mean        |      stddev       |        min         |        max        
-------+-------------------+--------------------+-------------------+--------------------+-------------------
   100 | -16.9827570720809 | -0.169827570720809 | 0.449019653323118 | -0.888983130455017 | 0.503015160560608

This is on GDAL -trunk r24660, PostgreSQL 9.1.4 and PostGIS -trunk r10037

Attachments (1)

rounding_error.patch (4.9 KB ) - added by dzwarg 10 years ago.
Force format of floating point numbers to full, reproducible precision when making bounding box queries.

Download all attachments as: .zip

Change History (4)

comment:1 by dustymugs, 10 years ago

Description: modified (diff)

by dzwarg, 10 years ago

Attachment: rounding_error.patch added

Force format of floating point numbers to full, reproducible precision when making bounding box queries.

comment:2 by dzwarg, 10 years ago

Status: newassigned

comment:3 by dzwarg, 10 years ago

Resolution: fixed
Status: assignedclosed

Fixed in r24672

Note: See TracTickets for help on using tickets.