5

I am exporting my Finnish (Finland) data from PostGIS to esri shapefile. I am using the PostGIS Shapefile Import/Export Manager and I don't see any way to specify the encoding of the shapefile's attributes in the .dbf file. My PostGIS table contains umlauts, but they are lost when the data is exported to the shapefile.

The following address is in my PostGIS table: 16 Itälahdenkatu.

The above address is changed to this in my shapefile (.dbf): 16 Itälahdenkatu

  • What encoding does your postgis data have? What tool did you use to populate the database? – AndreJ Nov 21 '14 at 13:13
  • My postgis database is UTF8. I used the PostGIS Shapefile Import/Export Manager to import the data from shapefile to Postgres. The original shapefile has umlauts, and the PostGIS data has umlauts. – Frank Matzen Nov 21 '14 at 13:28
  • If you are talking about pgsql2shp, you can use the -W switch to set encoding. Try using LATIN1 – John Powell Nov 21 '14 at 14:55
  • Only shp2pgsql supports the -W switch. Pgsql2shp does not support -W. – Frank Matzen Nov 21 '14 at 15:15
  • I also ran set client_encoding = 'UTF-8', before exporting the data from PostGIS to shapefile. This didn't solve my problem. – Frank Matzen Nov 21 '14 at 15:23
1

The umlauts are not lost. You are looking inside your Postgis table with UTF-8 encoding, and into the .dbf table with System encoding.

If you open the .dbf file in Libre Office, you will be asked for the encoding. Select UTF-8 and the content is readable.

You can load the shapefile (or the Postgis table directly) into QGIS, setting encoding to UTF-8 or System, and check the attribute table if the content is correct. Then save the shapefile to another name, selecting encoding to UTF-8 and you should have the shapefile encoding you want.

Once you got it, you can think of using ogr2ogr for reading Postgis and writing to shapefile.

1

You can try with ogr2ogr, if your db encoding is UTF8 use this command :

ogr2ogr -f "ESRI Shapefile" c:\shape.shp PG:"dbname=db_name user=user_name password=xxxx host=serveur_192.168.1.1 port=5432" -sql "select table_id, table_name, geom from table" -overwrite -lco ENCODING=UTF-8

You just need to change these variables : db_name, user_name, xxxx, serveur_192.168.1.1, 5432

and the SQL query

I hope it will be usefull

1

you could also load your postgis-Layer into qgis and save it within qgis itself as a shapefile (right-mouse-click on the layer in the TOC --> save as. Then you can choose the encoding: enter image description here ( If you choose something else than "system" as encoding you will probably have to choose the encoding while loading the shapefile into qgis in order to get all your special characters displayed correctly. )

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.