[GIS] How to control encodings in pgsql2shp

encodingpgsql2shp

I have a database and exporting with pgsql2shp various shapefiles. But when I open the dbf the characters are displayed wrong. Example: BroeksterwÔld and this should be Broeksterwäld.

This probably has to do with encoding. The database is UTF-8. I tried to export the shapefiles as LATIN1, but I can't figure out how to do so.

UPDATE

I solved this problem by adding 'set pgclientencoding=latin1' (without quotes) to the batch script.

Best Answer

I solved this problem by adding 'set pgclientencoding=latin1' (without quotes) to the batch script.

Related Solutions

QGIS – How to Fix Shapefile’s Character Encoding Using ogr2ogr on the Command Line

Quoting from the GDAL documentation for the Esri Shapefile driver:

An attempt is made to read the LDID/codepage setting from the .dbf file and use it to translate string fields to UTF-8 on read, and back when writing. LDID "87 / 0x57" is treated as ISO8859_1 which may not be appropriate. The SHAPE_ENCODING configuration option may be used to override the encoding interpretation of the shapefile with any encoding supported by CPLRecode or to "" to avoid any recoding. (Recoding support is new for GDAL/OGR 1.9.0)

Depending on which GDAL/OGR version you are using, ogr2ogr may be trying to translate your data to UTF-8 or not doing nothing at all.

So you would do either:

ogr2ogr --config SHAPE_ENCODING "UTF-8" -f "MySql" MySql:"basemap,host=127.0.0.1,user=myUser,password=myPass,port=3306" -lco engine=MYISAM "C:/path/to/data/tl_2012_us_county.shp" -nln county -nlt "geometry" -s_srs EPSG:4269 -t_srs EPSG:3857

ogr2ogr --config SHAPE_ENCODING "" -f "MySql" MySql:"basemap,host=127.0.0.1,user=myUser,password=myPass,port=3306" -lco engine=MYISAM "C:/path/to/data/tl_2012_us_county.shp" -nln county -nlt "geometry" -s_srs EPSG:4269 -t_srs EPSG:3857

Finally, check that your MySQL database is using UTF-8 and not Latin-1.

[GIS] GeoJSON attributes encoding limitation

GeoJSON is an extension of JSON, and you will find in RFC7159

JSON text SHALL be encoded in UTF-8, UTF-16, or UTF-32.  The default
encoding is UTF-8, and JSON texts that are encoded in UTF-8 are
interoperable in the sense that they will be read successfully by the
maximum number of implementations; there are many implementations
that cannot successfully read texts in other encodings (such as
UTF-16 and UTF-32).

For that reason, nobody has implemented latin encoding within OGR.

Best Answer

Related Solutions

QGIS – How to Fix Shapefile’s Character Encoding Using ogr2ogr on the Command Line

[GIS] GeoJSON attributes encoding limitation

Related Question