4

I am trying to upload data of MULTIPOINTS for listed buildings in Wales in shapefile format to a PostgreSQL database I have run the command below:

ogr2ogr -progress -update -append -f PostgreSQL "PG:dbname=dbtest2 host=localhost port=5432 user=user password=password" -lco LAUNDER=YES -lco GEOMETRY_NAME=geom -nln $LAYER -nlt MULTIPOINT -sql "select test_SHP_COLUMNS from test_SHP_LAYER" -dialect sqlite --config PG_USE_COPY YES -gt 655360 test_SHP

having in return this errors:

ERROR 1: COPY statement failed.
ERROR:  invalid byte sequence for encoding "UTF8": 0xe7 0x61 0x64
CONTEXT:  COPY listed_buildings, line 2

I have used the same code for a similar layer of Multipoints and it works perfectly, but some rows in the table of the layer I am trying to upload with the previous command.

Do you know how can I deal with this?

Improve this question
5
  • 2
    this is similar to stackoverflow.com/questions/1377662/…
    – sys49152
    Commented May 18, 2021 at 8:54
  • 1
    and this is not ogr2ogr related but could clarify if its database setup in question stackoverflow.com/questions/4867272/…
    – sys49152
    Commented May 18, 2021 at 8:54
  • 1
    I suppose that the database side is OK and tables are configured to use UTF-8 because that tends to be the default. Probably GDAL/ogr2ogr does not interpret right the encoding that is used in the shapefile. You can try a) rename the codepage file .cst into .cpg and hope that ogr2ogr reads it right or b) forget the shapefile and download the GeoJSON version of the data lle.gov.wales/catalogue/item/ListedBuildings.json.
    – user30184
    Commented May 18, 2021 at 9:14
  • 1
    GeoJSON file seems to have a bit different attributes than shapefile.
    – user30184
    Commented May 18, 2021 at 9:42
  • 1
    I believe that the shapefile is also corrupted. The field "Designatio" is shortened from "DesignationDate" and it should contain datetimes. Shapefile does not support that so datetimes should be converted into strings but now the field contains some rubbish. Use GML or GeoJSON instead.
    – user30184
    Commented May 18, 2021 at 9:52

1 Answer 1

Reset to default
3

It is not always easy to interpret what character encoding is used in the shapefile. What GDAL tries to do is documented in https://gdal.org/drivers/vector/shapefile.html#encoding.

In your case the shapefile comes with a .cst file (means perhaps "character set"). The content of it is ISO-8859-1. However, GDAL does not read .cst file so it believes that the data are already in UTF-8 but it is wrong. Some strings in the field "designatio" cannot be written as is into PostGIS because they are not valid UTF8 strings.

The easiest fix is to rename the .cst file into .cpg because GDAL does interpret the .cpg (codepage) file. Or you can use the open option

ogr2ogr -f gpkg cymru.gpkg  Cadw_ListedBuildingsMPoint.shp -oo encoding=ISO-8859-1

I can't say if the Cymraeg characters are right in the source shapefile but at least you should get the data inserted into PostGIS.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.