I am having problems using the SQLite SQL dialect to read a CSV file using ogrinfo, but only if I also specify a geometry column using the -oo GEOM_POSSIBLE_NAMES open option. Suppose file test.csv contains:
id, geom 1, 000000000140000000000000004010000000000000
Then this simple SQL works fine:
$ ogrinfo -GEOM=yes -sql 'select id from test' test.csv INFO: Open of `test.csv' using driver `CSV' successful. Layer name: test Geometry: None Feature Count: 1 Layer SRS WKT: (unknown) id: String (0.0) OGRFeature(test):1 id (String) = 1
But now I wish to reference the geometry, so I use the -oo GEOM_POSSIBLE_NAMES open option:
$ ogrinfo -geom=YES -sql 'select id from test' -oo GEOM_POSSIBLE_NAMES=geom test.csv INFO: Open of `test.csv' using driver `CSV' successful. Layer name: test Geometry: Unknown (any) Feature Count: 1 Extent: (2.000000, 4.000000) - (2.000000, 4.000000) Layer SRS WKT: (unknown) Geometry Column = geom id: String (0.0) OGRFeature(test):1 id (String) = 1 POINT (2 4)
So far, so good. Now suppose that I am doing something more complicated in my SQL (like CASTing or using a CASE), so I specify the SQLite dialect. Now the wheels come flying off:
$ ogrinfo -geom=YES -dialect sqlite -sql 'select id from test' -oo GEOM_POSSIBLE_NAMES=geom test.csv INFO: Open of `test.csv' using driver `CSV' successful. ERROR 1: Cannot create virtual table for layer 'test' : CREATE VIRTUAL: invalid SQL statement : CREATE TABLE "test"("id" VARCHAR,"geom" VARCHAR,OGR_STYLE VARCHAR HIDDEN,"geom" BLOB_GEOMETRY_XY_0, OGR_NATIVE_DATA VARCHAR, OGR_NATIVE_MEDIA_TYPE VARCHAR) ERROR 1: In ExecuteSQL(): sqlite3_prepare(select id from test): no such table: test
Notice that I can specify the sqlite dialect by itself just fine, so long as I leave off the "-oo GEOM_POSSIBLE_NAMES" open option:
/ # ogrinfo -GEOM=yes -dialect sqlite -sql 'select id from test' test.csv INFO: Open of `test.csv' using driver `CSV' successful. Layer name: SELECT Geometry: None Feature Count: 1 Layer SRS WKT: (unknown) id: String (0.0) OGRFeature(SELECT):0 id (String) = 1
I noticed that when I try both the dialect and open options together, the error arises from GDAL's attempt to create and load a temporary SQLIte table with the contents of the CSV -- a table that is later accessed by the SQL. The creation of this temporary table fails, possibly because the column name: "geom" is being specified twice. That sort of makes sense as the open option causes GDAL to add the specify column as a geometry column, but it appears to have also been added as a regular column as well. I've tried this on several different Linux flavors, and with varying GDAL versions. Here's a specific crime scene:
/ # uname -a Linux 1c20000629d8 4.9.0-6-amd64 #1 SMP Debian 4.9.88-1+deb9u1 (2018-05-07) x86_64 Linux / # gdal-config --dep-libs -ljson-c -lsqlite3 -L/usr/lib -lpq -lz -lpthread -lm -lrt -ldl / # gdal-config --version 2.1.3 / # ls -la /usr/lib/libsqlite3.so.0.8.6 -rwxr-xr-x 1 root root 837024 May 20 21:06 /usr/lib/libsqlite3.so.0.8.6
I'm sure there other ways to manipulate CSV via GDAL with SQLite SQL, such as the OGR Virtual Format driver, but I'm really curious to learn what I'm doing wrong here. Specifically, why is that specifying the SQLite dialect is incompatible with the use of -oo GEOM_POSSIBLE_NAMES?
Do I need to somehow exclude the geometry column as a "regular" column in the temporary table so that it is treated exclusively as a geometry column?