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@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users