[gdal-dev] GeoJSON with "id" in properties and SQLite dialect

[Scott]

As a work around, this seems to work:

ogr2ogr -preserve_fid idtest1.geojson idtest.json
ogrinfo -dialect sqlite -sql "select * from main" idtest1.geojson

On 4/7/25 13:43, Rahkonen Jukka via gdal-dev wrote:
> Hi,
> 
> I notised this question on gis.stackexchange https://gis.stackexchange.com/questions/491741/gdal-sqlite-no-such-table-when-id-in-properties
> 
> I tried to make as simple as possible test case and here is the GeoJSON:
> 
> {
>      "type": "FeatureCollection",
>      "name": "main",
>          "features": [
>          {
>              "type": "Feature",
>              "properties": {
>                  "id": 9794
>              },
>              "geometry": {
>                  "type": "Point",
>                  "coordinates": [
>                   45.14755797662297,
>                  -12.770174793784179
>                  ]
>             }
>          }
>      ]
> }
> 
> Is there any workaround for using SQLite dialect with this dataset? What goes wrong is that an attribute "id" appears inside properties where it actually does not belong. GDAL interprets that it should be used as promary key in the virtual SQLite table that gets created, but it also tries to create another attribute with the same name.
> 
> ogrinfo -dialect sqlite -sql "select geometry from main" idtest.json
> INFO: Open of `idtest.json'
>        using driver `GeoJSON' successful.
> ERROR 1: Cannot create virtual table for layer 'main' : CREATE VIRTUAL: invalid SQL statement : CREATE TABLE "main"("id" INTEGER HIDDEN PRIMARY KEY NOT NULL,"id" INTEGER,OGR_STYLE VARCHAR HIDDEN,"GEOMETRY" BLOB_POINT_XY_4326, OGR_NATIVE_DATA VARCHAR, OGR_NATIVE_MEDIA_TYPE VARCHAR)
> ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(select geometry from main):
>    no such table: main
> 
> -Jukka Rahkonen-
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev