Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
4  
Just a note: it looks like it overflowed nicely, since 77138 = 65536+11602. –  lynxlynxlynx Aug 21 '12 at 16:27
add comment

1 Answer 1

up vote 2 down vote accepted

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/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.