Loading geotiffs into PostGIS using raster2pgsql

Question

I have an issue with loading some Ordnance Survey raster data into PostGIS. I have been using the following raster2pgsql command to load a tif that I have placed in my C:\Program Files\PostgreSQL\9.1\bin folder into PostGIS:

raster2pgsql -s 27700 -t 5000x5000 -I -C -M  *.tif  > os_strview.sql

I then execute the follwing psql command:

psql -h localhost -U postgres -d raster_test -f os_strview.sql

This works fine but when I tried to view the data in QGIS I could only see the raster_columns table in the selection box when trying to add a PostGIS layer.

I tried using the plugin Load raster to PostGIS instead. This works but my raster image had no colour. I then saw this post. I used the Raster > Convert > PCT to RGB technique to convert a single tif to a geotif.

Unfortunately though when running the psql command above I get this error message:

C:\Program Files\PostgreSQL\9.1\bin>psql -h localhost -U postgres -d raster_test -f os_strview.sql BEGIN psql:os_strview.sql:2: NOTICE: CREATE TABLE will create implicit sequence "sx03 nw-geo_rid_seq" for serial column "sx03nw-geo.rid" psql:os_strview.sql:2: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "sx03nw-geo_pkey" for table "sx03nw-geo" CREATE TABLE psql:os_strview.sql:3: ERROR: could not load library "C:/Program Files/PostgreS QL/9.1/lib/rtpostgis-2.0.dll": Invalid access to memory location.

LINE 1: INSERT INTO "sx03nw-geo" ("rast") VALUES ('01000003000000000... ^ ROLLBACK

I take it this has something to do with the size of the geotiff I'm trying to load into PostGIS - which is 71 MB!

Is there another method I should be trying? What is the limit on the size of a single image you can load into PostGIS?

Answer 1

There is no limit on the size of a single image in PostGIS raster but you will be limited by other factors, such as how much memory you have in your computer. PostgreSQL does impose a 1GB limit on any one field PostgreSQL limits.

If you have the GDAL utility gdalinfo, could you run gdalinfo on one of your gtiff files and post the output here? Or if the data is publicly available (I assume the Ordinance Survey data is), can you provide a pointer to which tifs you are attempting to load so that I can test it?

gdalinfo MYGEOTIFF.tiff

The file size 71MB is tiny and should not be an issue. I currently load 10m elevation datasets (~500MB and 10812x10812 each) and 1m aerial imagery (~170MB and 5820x7575 each) without any issues.

Answer 2

I also had similar problems when importing data via Geotiff. My suggestion to you is, translate the file into Erdas Imagine file, and it should work. (recreate the import script, run it like you did already)

Answer 3

1) As stated in PostGIS Raster Frequently Asked Questions, raster2pgsql "rtpostgis.so/dll is built with dependency on libgdal.dll/so. Make sure for Windows you have libgdal-1.dll in the bin folder of your PostgreSQL install. For Linux libgdal has to be in your path or bin folder".

2) On the other hand, in a bug ticket related to the same error appears, they say that "Everything of raster2psql makes use of the functions provide in rt_api, where all functions operate in memory. This loader it is dependent on the memory available on one machine".

That means using another machine, when operating with big rasters in database with raster2pgsql, could solve the problem. Or, try to use smaller rasters.

In my case, a 35 MB raster failed on a laptop with 4GB RAM and 32 bits OS. Switching to a 16 GB RAM machine with a 64 bits OS did the job ok.