[gdal-dev] GDAL SQLITE ID reserved column issue

[Even Rouault]

Hi,

> My Test json file is having ID property -
> 
> {"type":"Feature","id":"XX","geometry":{"type":"Point","coordinates":[-99444
> 78.9237651918,5330546.6922583049]},"properties":{*"ID"*:"NorthWest
> Territory","ToolTip":"NorthWest","Label":"Corporate Operations Boundary
> 7679755"}}}
> 
> My VRT file is -
> 
> <?xml version="1.0" encoding="utf-8"?>
> <OGRVRTDataSource>
>   <OGRVRTLayer name="Schematic">
>     <SrcDataSource>C:\Test.json</SrcDataSource>
>     <SrcSQL dialect="sqlite">SELECT * FROM "Test"</SrcSQL>
>   </OGRVRTLayer>
> </OGRVRTDataSource>
> 
> SQLITE is unable to create a virtual table with "ID" property column since
> "id" is already there as reserved index column hence the problem.
> 
> How to overcome this issue?

I couldn't really come with a workaround with the existing state of the code.

I've added improvements in https://github.com/OSGeo/gdal/pull/2756 that will make it 
possible to deal with that situation:

$ ogrinfo test.json -sql "select * from test" -dialect sqlite -q

Layer name: SELECT
OGRFeature(SELECT):0
  id (String) = XX
  ID2 (String) = NorthWest Territory
  ToolTip (String) = NorthWest
  Label (String) = Corporate Operations Boundary 7679755
  POINT (-9944478.92376519 5330546.6922583)

==> due to SQLite not allowing field names differing by case, one is obliged to rename fields.

Even

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20200710/76d14a33/attachment.html>