Opened 8 years ago
Closed 6 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 )
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)
Change History (14)
comment:1 by , 8 years ago
follow-up: 4 comment:2 by , 8 years ago
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 by , 8 years ago
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"
by , 8 years ago
Attachment: | attributeError.JPG added |
---|
Shows the overwritting of attribute values with a original attribute name
follow-up: 5 comment:4 by , 8 years ago
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 by , 8 years ago
follow-up: 7 comment:6 by , 8 years ago
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 by , 8 years ago
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?
comment:8 by , 8 years ago
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 by , 8 years ago
Description: | modified (diff) |
---|
comment:10 by , 8 years ago
Description: | modified (diff) |
---|
comment:12 by , 8 years ago
Milestone: | 2.1.0 |
---|
comment:13 by , 6 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
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))
Could you share such a file ? The problem seems more to be related to the sqlite side.