6

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.

4
  • 2
    +1 good (first?) question info body. seems pedantic, but, as per the rules, only one actual and distinct question per post is allowed on this board...could you rephrase? an equally good answer to this should naturally include info on your different sub-questions I think. btw., it is also discouraged to leave 'thanks' and include your name...seems uncharming at times, but keeps the focus on the issue. – geozelot Dec 1 '18 at 10:32
  • 1
    please re-read my comment. it's a friendly one! I acknowledged your very good question! I refered to me citing the rules as pedantic, not to you! – geozelot Dec 3 '18 at 12:17
  • I am so sorry. The meaning of pendantic is different to the similar word in Spanish - pedante. I apologize you. – Alexys Herleym Rodriguez Avell Dec 3 '18 at 13:14
  • 1
    Did you check that the collation you are using exists? There are several ideas here:stackoverflow.com/a/49446727/7788694 – NettaB Jun 21 '19 at 12:29
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

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.