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 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 geotif 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?

share|improve this question
    
"Is there another method I should be trying" Do you have access to FME Desktop? FME 2013 has a writer for PostGIS 2.0 Raster –  Mapperz Sep 27 '12 at 15:43
    
No don't have access. Limited on funds so trying to use open source. Have heard good things about FME though so might be worth the investment. –  Matt T Sep 27 '12 at 17:55

3 Answers 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.

share|improve this answer

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.

share|improve this answer
    
Thanks dustymugs. I tried loading the geotiff from my home pc and it worked fine. I'll have to check what memory I have at work. I have managed to load the geotiff now but there is an issue with the cartographic text now. It isn't as clear as it was in the origianl tif. I might just stick to keeping the rasters outside of PostGIS for now. –  Matt T Sep 30 '12 at 12:48

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)

share|improve this answer
    
Thanks for the advice U2ros. However the raster I created was grey when viewed in QGIS. –  Matt T Sep 30 '12 at 12:49
    
Well, with erdas format you can (usually) apply custom color palette/gradient. In your case a default grayscale was chosen i think. –  U2ros Sep 30 '12 at 21:06

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.