[postgis-users] more memory issues with raster2pgsql

[georgew]

Hi, I am trying to convert a raster from SAGA GIS to Postgis using
raster2pgsql.The command line and its output is:
C:\\Progra~1\\PostgreSQL\\9.3\\bin\\raster2pgsql.exe -F -d -
I -C -M -s 2193 I:\cb09\cb09_3dem.sdat cb09_3 | psql -d NZTPU

Processing 1/1: I:\cb09\cb09_3dem.sdat

BEGIN
NOTICE: table "cb09_3" does not exist, skipping
DROP TABLE
CREATE TABLE
ERROR: could not load library "C:/Program
Files/PostgreSQL/9.3/lib/rtpostgis-2.1.dll": unknown error 998
LINE 1: INSERT INTO "cb09_3" ("rast","filename") VALUES ('0100000100...
^
ERROR: current transaction is aborted, commands ignored until end of
transactio
n block
.....

I then ran again but just to create the output sql and dumped the first few
lines of the output as follows:

>C:\\Progra~1\\PostgreSQL\\9.3\\bin\\raster2pgsql.exe -F -d -
I -C -M -s 2193 I:\cb09\cb09_3dem.sdat cb09_3 > c:\cb09.sql
Processing 1/1: I:\cb09\cb09_3dem.sdat

C:\WINDOWS\system32>more < c:\cb09.sql
BEGIN;
DROP TABLE IF EXISTS "cb09_3";
CREATE TABLE "cb09_3" ("rid" serial PRIMARY KEY,"rast" raster,"filename"
text);
INSERT INTO "cb09_3" ("rast","filename") VALUES
('010000010000000000000014400000
0000000014C0000000801D5F3241000000A0BC3F5341000000000000000000000000000000009108
0000C112211C4A804FC3C788A5B3446B9CB3440C9BB34412AAB3447FFFB3449DB9B444D679B54413
06B6446859B6441C52B6441037B6446788B64468C9B64401CCB644EDAAB644797FB64458BCB64417
95B744E348B844A0C6B8447C1CB944A13AB944DE0EB944D3E0B8445E14B9445B90B9442619BA443D
.....

Finally executed the sql:

C:\WINDOWS\system32>psql -U postgres -d NZTPU -f c:\cb09.sql
BEGIN
psql:c:/cb09.sql:2: NOTICE: table "cb09_3" does not exist, skipping
DROP TABLE
CREATE TABLE
psql:c:/cb09.sql:4: ERROR: out of memory
DETAIL: String of 276576218 bytes is too long for encoding conversion.
psql:c:/cb09.sql:5: ERROR: current transaction is aborted, commands ignored
unt
il end of transaction block
........
I assume the DLL not found is due to the lack of memory (because the file is
where it should be)
Is that assumption correct?

All my databases and templates are UTF8 as shown here:

<http://postgis.17.x6.nabble.com/file/n5006000/postgres.png>

So why is the encoding conversion necessary?
And more importantly how do I get around this problem?
I am on Windows 8.1 Pro, and the very latest versions of Postgres (9.3) and
Postgis (2.1.1)
Many thanks for your help




--
View this message in context: http://postgis.17.x6.nabble.com/more-memory-issues-with-raster2pgsql-tp5006000.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

[Bborie Park]

You should tell raster2pgsql to tile the raster into smaller bite-size chucks, 256x256 is a safe value. It sounds like the raster is too big for what memory is available.

-bborie

[georgew]

Thanks bborie, however from the error message I received the problem seems
due to the need to convert the original raster to utf-8. But the original
raster is in utf-8 already.
If I follow your suggestion (and after this mystery conversion is done) will
I be able to recombine the tiles to create a single raster, to be just like
the original?




--
View this message in context: http://postgis.17.x6.nabble.com/more-memory-issues-with-raster2pgsql-tp5006000p5006002.html

[Bborie Park]

How big is the original? Numbers of pixels width and height? Number of bands? Pixel types of the bands? I just want to know so that I can tell you if its even possible to store that raster as one database value.

Utf-8 shouldn't be an issue as the raster data itself isn't in any character set encoding. They're almost always numerical values.

All my production and dev postgresql clusters are in utf8 and I've never seen a raster encoding issue.

-bborie

[Bborie Park]

Wait a minute. I wonder what your psql's client encoding is. This seems familiar...

-bborie

[Bborie Park]

Found the answer.

http://postgis.net/docs/RT_FAQ.html#qa_raster_fails_encoding_conversion

Got to love robe2 and her due diligence.

-bborie

On Mar 28, 2014 5:30 PM, "georgew" <gws...@hotmail.com> wrote:

[georgew]

thanks again bborie, my original raster is only 135MB in size, the generated
sql is 270MB in size. As for the FAQ, I had looked at it, but could not work
out how to find out how my client encoding is set and where. Also where is
the postgresql load script for windows?



--
View this message in context: http://postgis.17.x6.nabble.com/more-memory-issues-with-raster2pgsql-tp5006000p5006006.html

[Bborie Park]

Assuming you are running psql at the terminal/console, you should run "set PGCLIENTENCODING=UTF8" before running psql.

I can't say I can be of much more help as I don't have a windows box handy.

-bborie

[georgew]

Thanks bborie, this is what I did, but no improvement:

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\WINDOWS\system32>set PGCLIENTENCODING=UTF8

C:\WINDOWS\system32>psql -U postgres -d NZTPU -f c:\cb09.sql
BEGIN
psql:c:/cb09.sql:2: NOTICE: table "cb09_3" does not exist, skipping
DROP TABLE
CREATE TABLE

psql:c:/cb09.sql:4: ERROR: could not load library "C:/Program

Files/PostgreSQL/
9.3/lib/rtpostgis-2.1.dll": unknown error 998
LINE 1: INSERT INTO "cb09_3" ("rast","filename") VALUES ('0100000100...
^

psql:c:/cb09.sql:5: ERROR: current transaction is aborted, commands ignored
unt
il end of transaction block

psql:c:/cb09.sql:6: ERROR: current transaction is aborted, commands ignored

unt
il end of transaction block

psql:c:/cb09.sql:7: ERROR: current transaction is aborted, commands ignored

unt
il end of transaction block

ROLLBACK
psql:c:/cb09.sql:9: ERROR: relation "cb09_3" does not exist
........
I tried again and the second time got the out of memory error.
What can I try next? I have searched everywhere but have not found a
suitable answer.
But as a start how can I determine what the default character set encoding
is for the server and for the client?



--
View this message in context: http://postgis.17.x6.nabble.com/more-memory-issues-with-raster2pgsql-tp5006000p5006008.html

[Bborie Park]

I think you can just connect to the database with psql and issue the following:

postgres=# SHOW client_encoding;

It should return something like...

 client_encoding 

-----------------

 UTF8

(1 row)

If your database doesn't return UTF8, you can forcefully set client_encoding

ALTER DATABASE mydb

  SET client_encoding = UTF8

-bborie