5

I'm trying to import some spatial tables from Oracle Spatial to PostGIS using ogr2ogr, but I can't figure out why the special characters on the attribute tables get messed up while I'm doing it.

My environment is:

  • Oracle Database 10g / Oracle Spatial 10.2.0.4 NLS_CHARACTERSET WE8ISO8859P1, NLS_LANGUAGE AMERICAN
  • PostgreSQL 9.5 / PostGIS 2.2 installed on Windows 8.1 Pro PT-BR. Database encoding is UTF8, character type IS Portuguese_Brazil.1252

My ogr2ogr command is:

ogr2ogr -progress -f "PostgreSQL" -nln schema.table -skipfailures -overwrite -lco FID=ID_COLUMN -lco GEOMETRY_NAME=geom PG:"host=localhost user=postgres password=yadayada dbname=database" OCI:username/password@server:port/database:schema.table

The table is imported perfectly, except for the special characters like "~". All the "ã" are replaced by "?", for example. I've already tried to use SET PGCLIENT ENCODING=LATIN 1 and SET PGCLIENTENCODING=WIN1252 without success.

I don't know if it helps, but if I try to open the tables directly from Oracle into QGIS the characters are displayed properly.

  • You might have better luck moving this question to dba.stackexchange.com – Mike T Apr 15 '16 at 6:38
  • The real question is: what is the character set in your Oracle database ? And what is the character set in your PostGIS database ? Setting the client encoding to whatever is pointless if you do not know what you are coming form and going to. – Albert Godfrind Apr 15 '16 at 23:50
  • @Albert Godfrind I guess I did that on the original question, didn't I? Aren't they the same as the PostgreSQL database, in the case of PostGIS? If they are not, I'm sorry, I'm still learning about PostGIS and I did not know that. Where can I find it?About Oracle, what are the values of the NLS_DATABASE_PARAMETERS table that I should post? – Rafael Apr 18 '16 at 11:13
  • Just to clarify, it says you tried SET PGCLIENT ENCODING=LATIN 1 but did you actually try SET PGCLIENTENCODING=LATIN1 (notice the lack of spaces)? – SaultDon May 18 '17 at 3:19
2

Try this Oracle environment variable:

NLS_LANG=AMERICAN_AMERICA.UTF8

  • Can you please edit your answer to expand on how to do this and explain what this does. See How to Answer – Midavalo Apr 16 '16 at 18:48
  • That is one of my problems: I have no control on the Oracle side. I have to read it as it is and and, if possible, make it work on the PostgreSQL/PostGIS side. – Rafael Apr 18 '16 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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