gdalinfo gives no georeferencing information for a PostGIS raster

Question

I have a PostGIS raster dataset which I am trying to access via GDAL. I've had various problems with making this work, so thought I'd try running the gdalinfo command to check that GDAL can actually make sense of the raster - and it seems to be giving very strange results.

When I run the command

gdalinfo  "PG:host=localhost port=5432 dbname='testgis'
     user='postgres' password='' schema='ch13' table=kauai" 

I get a long response which ends like:

 user=postgres password=  schema=ch13 table=kauai column=rast where='rid = 544'
  SUBDATASET_544_DESC=PostGIS Raster at ch13.kauai (rast), rid = 544
  SUBDATASET_545_NAME=PG:host=localhost port=5432 dbname=testgis
 user=postgres password=  schema=ch13 table=kauai column=rast where='rid = 545'
  SUBDATASET_545_DESC=PostGIS Raster at ch13.kauai (rast), rid = 545
  SUBDATASET_546_NAME=PG:host=localhost port=5432 dbname=testgis
 user=postgres password=  schema=ch13 table=kauai column=rast where='rid = 546'
  SUBDATASET_546_DESC=PostGIS Raster at ch13.kauai (rast), rid = 546
Corner Coordinates:
Upper Left  (   0.0000000,   0.0000000) 
Lower Left  (   0.0000000,   0.0000000) 
Upper Right (   0.0000000,   0.0000000) 
Lower Right (   0.0000000,   0.0000000) 
Center      (   0.0000000,   0.0000000) 

(The long list of SUBDATASET lines are because my raster is tiled in PostGIS)

It seems to think that my upper left, lower left etc are all 0 - which is not correct! For example, when I run the SELECT ST_UpperLeftX(rast) FROM ch13.kauai; query in PostGIS I get a list of outputs like:

 st_upperleftx 
---------------
        418205
        420205
        422205
        424205
        426205
        428205
        430205
        432205
        432205
        434205
        436205
        438205
        440205

Obviously something is going wrong here, but I'm not sure what. I'm a beginner with PostGIS, so I may be doing something really silly.

Does anyone have any idea what's going wrong, or what I should do next to try and debug this problem?

Answer 1

I have now found the answer to this question, thanks to Bborie Park on the PostGIS-Users mailing list.

Basically, by default when communicating with PostGIS, GDAL uses a setting called mode with a value of 1. This setting, called ONE_RASTER_PER_ROW means that each table is considered to be a collection of many different raster files, and thus you need to select a specific row to get the details on one raster.

If you set this setting manually to 2, known as ONE_RASTER_PER_TABLE, it will treat the whole table as one raster file, which is the correct thing to do when rasters are tiled - as they are here.

Thus, adding a mode=2 statement to the PostGIS connection field makes this work properly.