Are there limitations using a PostGIS out-db raster?

Question

I have been trying to import a large raster into a PostGIS 2.0 database and hit a few stumbling blocks.

Initially when I tried to import the raster using raster2pgsql, I would receive the following error: rt_band_set_pixel_line: Coordinates out of range

After finding this page, and using gdalinfo I found that the raster exceeded the maximum allowed size of 65535 x 65535.

gdalinfo output:

$ gdalinfo ari100.tif
Driver: GTiff/GeoTIFF
... Size is 42971, 77138
Coordinate System is:
GEOGCS["WGS 84",
...
AUTHORITY["EPSG","4326"]] Origin = (152.741676637167842,-26.215302802012008)
Pixel Size = (0.000009239757419,-0.000009239757419)
Metadata:
AREA_OR_POINT=Area
Image Structure Metadata:
INTERLEAVE=BAND
Corner Coordinates:
Upper Left ( 152.7416766, -26.2153028) (152d44'30.04"E, 26d12'55.09"S)
Lower Left ( 152.7416766, -26.9280392) (152d44'30.04"E, 26d55'40.94"S)
Upper Right ( 153.1387183, -26.2153028) (153d 8'19.39"E, 26d12'55.09"S)
Lower Right ( 153.1387183, -26.9280392) (153d 8'19.39"E, 26d55'40.94"S)
Center ( 152.9401974, -26.5716710) (152d56'24.71"E, 26d34'18.02"S)
Band 1 Block=42971x1 Type=Float32, ColorInterp=Gray
...

So I decided to use the out-db functionality by using the -R flag in raster2pgsql.

From the documentation: Register the raster as a filesystem (out-db) raster, Only the metadata of the raster and path location to the raster is stored in the database (not the pixels).

I imported the raster using the following command:

raster2pgsql /gis/Flood/ari100.tif -R | psql -U username database

But when I tried a query:

SELECT ST_Value(rast, ST_PointFromText('POINT(152.9632 -26.4878)')) FROM ari100

I get the following error:

NOTICE:  Attempting to get pixel value with out of range raster coordinates: (23975, 29491)  
CONTEXT:  PL/pgSQL function "st_value" line 13 at RETURN  

However, if I use GDAL (through Python) to query the raster directly using this script

python val_at_coord.py 152.9632 -26.4878

I can retrieve the value correctly.

I decided to find the extents of the raster in the database:

SELECT ST_Height(rast) As rastheight, ST_Width(rast) As rastwidth from ari100;

rastheight | rastwidth  
11602      | 42971  

The height is completely incorrect (should be 77138).

So my question is: did I do something wrong (in using raster2pgsql) or is there a limitation in using out-db rasters with PostGIS?

Thanks

Answer 1

You will definitely want to tile your raster. The maximum width x height permitted for the PostGIS raster type is 65535 x 65535, regardless of whether or not the raster is in-db or out-db. The other reason to tile your raster (though this may not apply in your case) is that the maximum field size permitted by PostgreSQL is 1 GB [1].

As for optimal tile size, I can only suggest two things.

1. Tile sizes <= 100 x 100 are best. smaller is faster but consumes more storage space.

2. If possible, find a tile size that is cleanly divisible from the raster's dimensions. So for a raster of 42971 x 77138, no tile size <= 100 x 100 works cleanly. In these situations, I usually just go 50 x 50 or something in that neighborhood.

-bborie

[1] http://www.postgresql.org/about/