[gdal-dev] ogr2ogr and reserved words

[Rahkonen Jukka]

Even Rouault wrote:
Selon Jukka Rahkonen <jukka.rahkonen at mmmtike.fi>:

>> Ivan Lucena <ivan.lucena <at> princeton-ma.us> writes:
>>
>> >
>> > Hi there,
>> >
>> > Is there any trick to avoid creating field names with reserved words? For
>> example, a DBF from a shapefile can
>> > have field names like "long" and other formats may not like it.
>>
>> Just remembered another example: having an attribute named "ROWID" will make
>> spatial index of a Spatialite table unusable.

> Having an attribute named "ROWID" will likely make it being used as the primary
> key. Not sure why it would break spatial index, but I somehow remember having
> skimmed through Sandro's lengthy explanations about that ;-)

Break in actually not the right word.  Triggers may prevent the creation of spatial index totally which is happy situation for the user because nothing bad happens. In a unhappy situation creation of spatial index is successful but search from the index gives wrong results. The Spatialite discussion group thread is
https://groups.google.com/forum/?fromgroups#!topic/spatialite-users/QwRdCiWoVKw

>> Perhaps Spatilite driver could
>> try
>> to sanitize it while reading data in?

> Yes, perhaps. Just curious : how did you get in the situation where a ROWID
> field was found ? Was it with another sqlite/spatialite table in input, or with
> a datasource from another driver ?

I used Spatialite-gui for making a view and next transferred the view into a new table with ogr2ogr. It is easy to repeat
- Create a view into Spatialite database with the Query/View composer
and select ROWID among other attributes
- Use ogr2ogr for transferring data from this view into another
Spatialite database as
 ogr2ogr -f SQlite -append target.sqlite source.sqlite -sql "select *
from view_with_rowid" 

I am not totally sure but I have a feeling that ROWID also must be selected into Spatialite spatial view for making the spatial index to work (view_rowid in the views_geometry_columns table seems to refer to ROWID of the main table). However, I must admit that I do not deeply understand how this new virtual spatial index system works.

-Jukka-