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

[Even Rouault]

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