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?
Best 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?
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.