10

I'm trying to load Census county level TIGER/line spatial layers into PostGIS using ogr2ogr in Windows.

Is there anyway to configure the encoding to LATIN1?

example:

ogr2ogr -f "PostgreSQL" PG:"user=postgres dbname=universe" . -lco PRECISION=NO -lco SCHEMA=census -nlt PROMOTE_TO_MULTI -lco ENCODING=LATIN1

error received:

ERROR 1: ERROR:  invalid byte sequence for encoding "UTF8": 0xf3 0x76 0x61 0x6e

ERROR 1: INSERT command for new feature failed.
ERROR:  invalid byte sequence for encoding "UTF8": 0xf3 0x76 0x61 0x6e
whyzar
  • 12,053
  • 23
  • 38
  • 72
Jess
  • 155
  • 7
  • I don't see an input specified. – klewis Nov 20 '16 at 16:26
  • Do you mean that you use LATIN1 encoding in your PostGIS? Or that you use UTF8 in PostGIS but your input data are in LATIN1? – user30184 Nov 20 '16 at 16:29
  • According to ACS Tiger/line: "Our products through 2014, use the ISO-8859-1 character encoding." Without trying to configure the encoding, ogr2ogr gets an error because of special characters. How do I not run into this error? – Jess Nov 20 '16 at 17:39
  • Thus the latter alternative. Option -lco is for sure wrong because PostGIS driver does not support it http://www.gdal.org/drv_pg.html. If your dataset is not very big try with -skipfailures. Also adding --debug on into your command may print more info about the error. – user30184 Nov 20 '16 at 18:17
  • Maybe related: http://gis.stackexchange.com/questions/15912/how-to-encode-shapefiles-from-latin1-to-utf-8 and http://gis.stackexchange.com/questions/142358/converting-shapefile-from-shift-jis-to-utf-8-when-the-usual-methods-fail although very old. – AndreJ Nov 21 '16 at 06:41

2 Answers2

5

There is.

If on windows simply run:

set PGCLIENTENCODING=LATIN1

In the command line before running your ogr2ogr command.

If your on Linux, you need to run:

export PGCLIENTENCODING=LATIN1;
HeikkiVesanto
  • 16,433
  • 2
  • 46
  • 68
  • Perfect answer. Additionally, beware of unused codes (in latin1/win1252) in your input, like 0x8f. If these are present, you will still receive errors in spite of encoding translation being set up correctly. Since the row number will be present in the error message, removing should be doable using libre office calc. If there is a lot of such pollution, use a hex editor. – Jan Nov 26 '22 at 09:29
3

Unfortunately it cannot be done with GDAL, you will have to set the PGCLIENTENCODING environment variable to LATIN1 in your operating system instead.
(e.g. in windows it can be done under System--> Advanced system settings--> Environment variables -->New system variable)

The GDAL documentation on PostgreSQL driver mentions this issue:

"By default it is assumed that text being sent to Postgres is in the UTF-8 encoding. This is fine for plain ASCII, but can result in errors for extended characters (ASCII 155+, LATIN1, etc).
While OGR provides no direct control over this, you can set the PGCLIENTENCODING environment variable to indicate the format being provided. For instance, if your text is LATIN1 you could set the environment variable to LATIN1 before using OGR and input would be assumed to be LATIN1 instead of UTF-8. An alternate way of setting the client encoding is to issue the following SQL command with ExecuteSQL() : "SET client_encoding TO encoding_name" where encoding_name is LATIN1, etc."

ylka
  • 1,077
  • 9
  • 22