[postgis-users] Are there limitations using a PostGIS out-db raster?

[Bborie Park]

Hey Philip,

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

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

On 08/21/2012 03:36 PM, Philip D'Rozario wrote:
> I hope someone has gone through this problem and can shed some light into this
> problem :)
> 
> 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 (http://trac.osgeo.org/postgis/ticket/1839) and using
> gdalinfo I found that the raster exceeded the maximum allowed size of 65535 x 65535.
> 
> 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 (http://svn.osgeo.org/gdal/trunk/gdal/swig/python/samples/val_at_coord.py)
> 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?
> 
> Tangentially: another option would be for me to import the raster as tiles, but
> searching these (even with an index) takes more than a minute. Is there an
> optimal tile size I can use for quick querying?
> 
> Thanks for your help
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkpark at ucdavis.edu