8

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?

2
  • "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, 2012 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, 2012 at 17:55

3 Answers 3

4

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.

1
  • 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, 2012 at 12:48
1

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)

2
  • Thanks for the advice U2ros. However the raster I created was grey when viewed in QGIS.
    – Matt T
    Sep 30, 2012 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, 2012 at 21:06
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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