Opened 4 years ago

Closed 2 years ago

#6447 closed defect (fixed)

ogr2ogr does not find table when using "§", "ß", "ö", "ä" or "ü" characters in the qualified identifier

Reported by: hansw Owned by: warmerdam
Priority: normal Milestone:
Component: default Version: svn-trunk
Severity: major Keywords: ogr2ogr ISO-8859-1 characters table qualified identifier
Cc:

Description (last modified by hansw)

Hello,

I'm using GDAL 2.1.0beta1, (released 2016/04/01) under Windows 7 x64. Source

I'm exporting geodata from from a Spatialite table to a Schapfile using the command line. It usually works fine except when there is a "§", "ß", "ö", "ä" or "ü" in the table name.
I created a test Spatialite (sqlite) DB.
As the attached file upload size is under 1MB I uploaded the file to: ​http://www.file-upload.net/download-11474575/importTest.sqlite.html
To see the table name error use this command line.

ogr2ogr.exe -f "ESRI Shapefile" "C:\exportTest.shp" "C:\importTest.sqlite" -lco "ENCODING=ISO-8859-1" -lco "SPATIAL_INDEX=YES" -sql "SELECT \"straßenExport\".\"the_geom\", \"straßenExport\".\"id\" AS \"ident\" FROM \"straßenExport\";" -t_srs "EPSG:4326"

Gives following error:

ERROR 1: In ExecuteSQL(): sqlite3_prepare(SELECT "stra+âenExport"."the_geom", "stra+âenExport"."id" AS "ident" FROM "stra+âenExport";):  no such table: stra+âenExport

When using "§", "ö", "ä" or "ü" in the column name the export of this column does not work properly too. In my case the values of this attribute are overwritten with the original column name. (see picture attached to the ticket)
To see the column error use this command line.

ogr2ogr.exe -f "ESRI Shapefile" "C:\exportTest.shp" "C:\importTest.sqlite" -lco "ENCODING=ISO-8859-1" -lco "SPATIAL_INDEX=YES" -sql "SELECT \"strassenExport\".\"the_geom\", \"strassenExport\".\"idä\" AS \"id\" FROM \"strassenExport\";" -t_srs "EPSG:4326"

Gives following error:

ERROR 1: In ExecuteSQL(): sqlite3_prepare(SELECT "strassenExport"."the_geom", "strassenExport"."id+±" AS "id" FROM "strassenExport";):
  no such column: strassenExport.id+±


They are valid ISO-8859-1 characters and not UTF-8 only. I don't understand why they create this issues. As I used qualified identifiers the use of an them in the table name should not be a problem. GDAL > 2.0.0 supports qualified identifiers.
I have problems believing it is a windows command line encoding problem as I already used this characters in filenames and GDAL has no problem reading them.

(Updated the ticket according to the discussion below)

Attachments (1)

attributeError.JPG (14.4 KB) - added by hansw 4 years ago.
Shows the overwritting of attribute values with a original attribute name

Download all attachments as: .zip

Change History (14)

comment:1 Changed 4 years ago by Even Rouault

Could you share such a file ? The problem seems more to be related to the sqlite side.

comment:2 Changed 4 years ago by Even Rouault

Hum, I think that might be a Windows specific issue where, when typed in the shell, the ß characters are not converted to UTF-8.

Ah and regarding "In my case the values of this attribute are overwritten with the original column name", this is intended. A shapefile doesn't have an explicit place where to store a layer name, so the layer name is the filename of the shapefile.

comment:3 Changed 4 years ago by hansw

I created a test Spatialite (sqlite) DB

As the attached file upload size is under 1MB I uploaded the file to:
http://www.file-upload.net/download-11474575/importTest.sqlite.html

This comandline can be used to recreate the described table name error.

ogr2ogr.exe -f "ESRI Shapefile" "C:\exportTest.shp" "C:\importTest.sqlite" -lco "ENCODING=ISO-8859-1" -lco "SPATIAL_INDEX=YES" -sql "SELECT \"straßenExport.the_geom\" AS \"the_geom\", \"straßenExport.id\" AS \"id\" FROM \"straßenExport\";" -t_srs "EPSG:4326"

This comandline can be used to recreate the silent column name error. (a faulty Shapefile will be exported)

ogr2ogr.exe -f "ESRI Shapefile" "C:\exportTest.shp" "C:\importTest.sqlite" -lco "ENCODING=ISO-8859-1" -lco "SPATIAL_INDEX=YES" -sql "SELECT \"strassenExport.the_geom\" AS \"the_geom\", \"strassenExport.idä\" AS \"id\" FROM \"strassenExport\";" -t_srs "EPSG:4326"

Changed 4 years ago by hansw

Attachment: attributeError.JPG added

Shows the overwritting of attribute values with a original attribute name

comment:4 in reply to:  2 ; Changed 4 years ago by hansw

Replying to rouault: I don't understand how it could be normal that attribute values are overwritten with a attribute name. To illustrate I appended a picture.

comment:5 in reply to:  4 Changed 4 years ago by Even Rouault

Replying to hansw:

Replying to rouault: I don't understand how it could be normal that attribute values are overwritten with a attribute name. To illustrate I appended a picture.

Sorry disregard this comment. I had read too fast what you wrote.

comment:6 Changed 4 years ago by Even Rouault

One issue is in your SQL query. "straßenExport.the_geom" in SQLite SQL has not the effect on selecting the_geom column of table straßenExport, but as no such column exists, to create a text literal. You would need to quote the table name and layer name separately : "straßenExport"."the_geom". And actually there's no table ambiguity since there's only one table selected, so you can just specify the column name.

comment:7 in reply to:  6 Changed 4 years ago by hansw

Replying to rouault:

You are absolutely right with the query. This was a copy and paste error (censorship reasons)
To see the table name error use this comandline.

ogr2ogr.exe -f "ESRI Shapefile" "C:\exportTest.shp" "C:\importTest.sqlite" -lco "ENCODING=ISO-8859-1" -lco "SPATIAL_INDEX=YES" -sql "SELECT \"straßenExport\".\"the_geom\", \"straßenExport\".\"id\" AS \"ident\" FROM \"straßenExport\";" -t_srs "EPSG:4326"

Gives following error:

ERROR 1: In ExecuteSQL(): sqlite3_prepare(SELECT "stra+âenExport"."the_geom", "stra+âenExport"."id" AS "ident" FROM "stra+âenExport";):  no such table: stra+âenExport

To see the column error use this comandline.

ogr2ogr.exe -f "ESRI Shapefile" "C:\exportTest.shp" "C:\importTest.sqlite" -lco "ENCODING=ISO-8859-1" -lco "SPATIAL_INDEX=YES" -sql "SELECT \"strassenExport\".\"the_geom\", \"strassenExport\".\"idä\" AS \"id\" FROM \"strassenExport\";" -t_srs "EPSG:4326"

Gives following error:

ERROR 1: In ExecuteSQL(): sqlite3_prepare(SELECT "strassenExport"."the_geom", "strassenExport"."id+±" AS "id" FROM "strassenExport";):
  no such column: strassenExport.id+±

I'm not sure if that is a windows comandline encoding problem? If yes what would the solution be?

Last edited 4 years ago by hansw (previous) (diff)

comment:8 Changed 4 years ago by hansw

I can't realy belive it is a windows comandline encoding problem as I already used this characters in filenames and GDAL has no problem reading them.

comment:9 Changed 4 years ago by hansw

Description: modified (diff)

comment:10 Changed 4 years ago by hansw

Description: modified (diff)

comment:11 Changed 3 years ago by Even Rouault

Removing target milestone as it corresponds to a now closed milestone.

comment:12 Changed 3 years ago by Even Rouault

Milestone: 2.1.0

comment:13 Changed 2 years ago by Jukka Rahkonen

Resolution: fixed
Status: newclosed

Seems to work now with GDAL 2.3dev due to fix make for #7065. Both with qualified or non-qualified identifiers.

ogrinfo ä.sqlite -sql "select \"geometry\" as \"ö\" from ä "
INFO: Open of `ä.sqlite'
      using driver `SQLite' successful.

Layer name: SELECT
Geometry: Unknown (any)
Feature Count: 1
Extent: (474.000000, 280.000000) - (620.000000, 380.000000)
Layer SRS WKT:
(unknown)
Geometry Column = ├Â
OGRFeature(SELECT):0
  POLYGON ((474 371,620 380,520 280,474 371))
Note: See TracTickets for help on using tickets.