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
_______________________________________________
gdal-dev mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/gdal-dev
