[gdal-dev] GDAL 1.7 issues with SQLite creation from source without geometries

[Roald de Wit]

Hi Even,

Thanks for the verbose response. Option 2 (SQLITE_LIST_ALL_TABLES=YES) 
is the easiest and best one for my use case : importing data from both 
spatial and non-spatial sources into one SQLite file).

Realising I had not read the release notes [1] properly and having read 
them again, I just noticed that they mention SQLITE_LIST_ALL_LAYERS 
(does not work) instead of SQLITE_LIST_ALL_TABLES (does work).
It looks like I have editing rights for that page. Are you OK with me 
changing it to SQLITE_LIST_ALL_TABLES?

Regards, Roald

[1] http://trac.osgeo.org/gdal/wiki/Release/1.7.0-News

On 20/02/10 11:38, Even Rouault wrote:
> Roald,
>
> Several points :
>
> * I've tried with 1.6 branch, and even if it can list the tasmania_cities
> layer, it doesn't seem to be able to read the features in it, as a dumb
> geometry column name '(null)' was inserted by ogr2ogr into the
> geometry_columns table. Thus when reading the definition of the
> tasmania_cities layer, it will stop because it can't find the '(null)'
> column. See the output :
>
> $  ogrinfo  -ro -al tasmania.sqlite
> INFO: Open of `tasmania.sqlite'
>        using driver `SQLite' successful.
>
> Layer name: tasmania_cities
> Geometry: None
> Feature Count: 1
> Layer SRS WKT:
> (unknown)
> city_name: String (0.0)
> admin_name: String (0.0)
> cntry_name: String (0.0)
> status: String (0.0)
> pop_class: String (0.0)
> ERROR 1: Unable to find Geometry column '(null)'.
>
> So 1.6 seems to be broken wit respect to non spatial databases.
>
> * With OGR 1.7, no entry is added in the geometry_columns table for
> non-spatial tables (http://trac.osgeo.org/gdal/changeset/17990). But as the
> databases are created by default for spatial table support, ogrinfo won't
> list non spatial databases, which is a "specified" behaviour to avoid
> reporting tables such as geometry_columns or spatial_ref_sys.
>
> To make things clearer, I've found at least 3 ways of making OGR 1.7 work with
> non spatial databases.
>
> -->  The cleanest one : Create a non-spatial databases that has no
> geometry_column table. You must pass the -dsco METADATA=NO option on the
> ogr2ogr command line. This option was broken before 1.7.0 - see
> http://trac.osgeo.org/gdal/ticket/2879 - , but databases created with -dsco
> METADATA=NO with 1.7.0 will be readable with 1.6.0
>
> -->  If your database contains the geometry_column table, only spatial tables
> are listed by default, unless you set the SQLITE_LIST_ALL_TABLES environment
> variable to YES when opening it with ogrinfo - or any other OGR utility.
> (this option is new in OGR 1.7.0)
>
> -->  Rather dirty. Pretend that your non spatial table is a spatial table with
> POINT geometry type for example. Add the -nlt POINT option on the ogr2ogr
> command line for that. The table will be registered as begin a POINT table,
> but no feature will have a geometry. Works with both 1.6.0 and 1.7.0
>
> So, all in all, I think OGR 1.7 should work rather well when dealing with non
> spatial sqlite databases.
>
> Best regards,
>
> Even
>
> Le Friday 19 February 2010 23:47:54 Roald de Wit, vous avez écrit :
>    
>> Hi list,
>>
>> I have the following issue: OGR 1.7.x cannot read the SQLite files it
>> creates from a source *without geometries*.
>>
>> I used the following GDAL:
>> - from http://vbkto.dyndns.org/sdk/
>>     (release-1310-gdal-1-7-mapserver-5-6.zip, based on r9799, r18849)
>> - on a WinXP machine
>> - having no other GDAL in my path
>>
>> Simple example, try the following with attached CSV file.:
>>
>>       ogr2ogr -F "SQLite" tasmania.sqlite tasmania_cities.csv
>>
>> and then:
>>
>>       ogrinfo tasmania.sqlite
>>       INFO: Open of `tasmania.sqlite'
>>              using driver `SQLite' successful.
>>
>> No layers are found. With GDAL 1.6.x there is no problem.
>>
>> I'm not sure if this is an issue with this specific build of GDAL or
>> with GDAL in general (since I'm not aware of another recent GDAL with
>> modern SQLite support built-in on Windows).
>>
>> I contacted Tamas Szekeres off-line about the issue. He suggested it
>> might be a problem with the SQLite driver, therefore I'm taking it to
>> the gdal-dev list.
>>
>> Is this a bug? If so, I'm happy to create a ticket in trac.
>>
>> Regards,
>>
>> Roald
>>      
>
>