I tried to import a dem.tif in a postgres database. I use postgres 9.5. First step was to create file via raster2pgsql. This file dem.sql (800.000kb), I'd like to import via psql into the database. The process start running, create sequence, and primary key, then CREATE TABLE...stop with ERROR out of memory character string with 859735225 Bytes to long for transformation ERROR Transaktion stopped etc. 
What can I do to bypass this problem?
-
post full error– ziggyCommented Sep 5, 2017 at 13:50
-
1its in german: 3: out of memory detail character string to long for code transformation 4: Error transaction stopped commands until end of transaction ignored 5: Error transaction stopped commands until end of transaction ignored 6: Error transaction stopped commands until end of transaction ignored– oppia04Commented Sep 5, 2017 at 14:03
-
Try the suggested steps outlined in the PostGIS Raster FAQ section 10.15 postgis.net/docs/…– AliCommented Sep 5, 2017 at 14:16
-
I changed database encoding with: ALTER DATABASE mydb SET client_encoding = 'WIN1252'; as suggested in the sql tool in pg admin. was no problem. Import failed afterwards with the same errors...– oppia04Commented Sep 5, 2017 at 14:37
-
@oppia04 what does SHOW server_encoding return?– AliCommented Sep 5, 2017 at 15:26
|
Show 6 more comments
1 Answer
As discussed in the comments/chat, you ran into two errors here. Firstly, the conversion between the client and the server encoding, and secondly, a limitation of raster size.
Encoding conversion
The encoding conversion issue has been documented in the PostGIS Raster FAQ
If your database has set an explicit client encoding different from your database encoding, then when loading large raster files (above 30 MB in size), you may run into a bytes is too long for encoding conversion.
It is important here to note the difference between server_encoding and client_encoding. Server encoding is what is used within the database and can be viewed using SHOW server_encoding; the client encoding setting is the encoding that is used when connecting from a client to the PostgreSQL server e.g., via psql in your case. As with the server_encoding, you can query the database for the current client encoding with SHOW client_encoding.
To solve this first issue it is recommended to explicitly set the client_encoding to match that of the database (the server_encoding). There are several methods to change the client encoding which you can find in the PostgreSQL documentation.
Raster size
When working with larger rasters it is recommended to include the -t widhthxheight switch within raster2pgsql. You can alternatively use -t auto to have raster2pgsql compute an appropriate tile size.
As user dustymugs (a PostGIS developer) mentions in their answer to a previous question, the maximum dimensions allowed for a single raster are 65535 x 65535 and the size limit for a field in PostgreSQL is 1GB. Therefore, if your raster exceeds either limitation you will have to tile it.
