SpatiaLite will not return ASpatial layers

[josephramsay]

SpatiaLite will not return layers without a geometry

Details: It looks like SpatiaLite only returns layers appearing in its geometry columns table. With a null geometry (wkbNone) we don't create such an entry and GetLayer never returns the desired layer.

Workaround: For some operations we can use basic SQL calls to achieve the desired result e.g. delete layer. For others like update, we can only throw an exception.

TODO: GDAL Ticket

Result: Cannot do incremental updates on ASpatial layers

[palmerj]

I can confirm this:

ogr2ogr -f "SQLite" test.sqlite wfs:"http://wfs.data.linz.govt.nz/API-KEY/v/x1207/wfs" -dsco SPATIALITE=yes -nln asp_status_types

ogrinfo -so -al test.sqlite INFO: Open of test.sqlite' using driverSQLite' successful.

(no layers returned)

However a direct sql query works:

ogrinfo test.sqlite -sql 'SELECT * FROM asp_status_types' INFO: Open of test.sqlite' using driverSQLite' successful.

Layer name: SELECT Geometry: None Feature Count: 2 Layer SRS WKT: (unknown) gml_id: String (0.0) status: String (0.0) name: String (0.0) modified: String (0.0) id: Integer (0.0) OGRFeature(SELECT):0 gml_id (String) = x1207.1 status (String) = C name (String) = Current
modified (String) = 2004-03-29T03:44:00Z id (Integer) = 1

OGRFeature(SELECT):1 gml_id (String) = x1207.2 status (String) = H name (String) = Historical
modified (String) = 2004-05-24T02:52:00Z id (Integer) = 2

[palmerj]

Looked at the OGR code. Seems there was an undocumented config option required to list tables not in the geometry_columns table. If you set SQLITE_LIST_ALL_TABLES=YES then everything should work.

ogrinfo --config SQLITE_LIST_ALL_TABLES=YES -al test.sqlite asp_status_types

[palmerj]

ticket raised to document ogr better: http://trac.osgeo.org/gdal/ticket/4946

[josephramsay]

gdal.SetConfigOption('SQLITE_LIST_ALL_TABLES','YES')

Thanks. Like a charm