Shp2pgsql encoding issue / error. Special latin /utf characters (áéíóú) are wrong

Question

I am not able to load accented vowels coming from a Shapefile, to a PostGis geodatabase.

This is my information: Operative System: Windows 10. PostgreSql Database: postgresql-10.5-2-windows-x64.exe. Database encoding: UTF8. Database collation: English United States 1252. Database character type: English United States 1252. Sql client in Windows: psql. Windows CMD default code page: 437. Tool to create SQL from Shapefile: shp2pgsl.

1) Commands that I have ran to create the Sql:

shp2pgsql -s 21892 -c -g geom -D -k -i -I -W "UTF-8" municipios2.shp public.municipios2 > mun1.sql

shp2pgsql -s 21892 -c -g geom -D -k -i -I -W "LATIN1" municipios2.shp public.municipios2 > mun2.sql

On top of mun1.sql and mun2.sql (first line) reads:

SET CLIENT_ENCODING TO UTF8;

2) This is the encoding that Notepad++ shows for those sql files:

UCS-2 LE BOM

3) In Notepadd ++, next sample words, are inside those sql files:

CI├ëNAGA (must be CIÉNAGA) UNI├ôN (must be UNIÓN)

4) I tried in Notepad ++ to open those sql files with a different encoding or convert to a different encoding (UTF-8) but it does nothing or shows worst results:

CI├â┬ëNAGA (must be CIÉNAGA). UNI├â┬ôN (must be UNIÓN)

5) This is the procedure that I did to load some of those files inside Postgis - In windows command prompt (before use shp2psql of psql) I changed the code page:

chcp 1252

Inside psql, before load, I changed the client encoding to one of those options:

SET client_encoding = 'UTF8';
SET client_encoding = 'LATIN1';

I load (with psql) those files to the geodatabase:

\i 'D:/mun1.sql'

with this error ERROR: invalid byte sequence for encoding "UTF8": 0xff psql end.

Note: If I convert those sql files to UTF8, they will load to PostGis, but with wrong words (CIÉNAGA, UNIÓN)

Of course if I have tested "Postgis 2.0 Shapefile Loader", and it works very well with UTF8. But I need shp2psql to use bath load of many shapefiles with specific sql sentence to load.

What should I do to solve the problem?

I have ogr2ogr option (I have not work with that), but I have been using shp2psql and I want to know what is the problem.

Answer 1

Probably your shapefile is neither in LATIN1 or UTF8 but in something like cp1252. An easy way to check this is open the shapefile in a software like QGIS, open the attribute table and check if there are encoding issues. Then check the the "source" tab in the properties of the shapefile and check the "data source encoding" value.

If there are encoding issues just change the "data source encoding" and open the table again until finally you find the correct encoding.

You can do something similar open directly the .dbf file with LibreOffice Calc.

Then use the shapefile encoding for the -W parameter

shp2pgsql -s 21892 -c -g geom -D -k -i -I -W cp1252 municipios2.shp public.municipios2 > mun2.sql

Answer 2

I had issues even using that -W directive. Instead i found that some utility present in the Application Stack Builder helps with this process and inserts directly the records in database. I'm talking about "PostGIS Shapefile Import/Export Manager", an utility that allows precisely that: Importing the records from some SQL files, and also configure the encoding of the records.

After hitting the application we should configure

• the encoding:

• The db connection:

• And the SHP file to be imported