Add support for spatiLite extension in OGR SQLITE driver

[dron]

Frank,

I see there is some preliminary work you have done in order to support SpatiaLite extension in SQLite driver. I am working towards the same goal so I am creating this ticket to avoid efforts duplication. At the moment I've fully succeeded in reading of SpatiaLite databases and working on creation and feature inserting. My main goal is to utilize spatial indexes, because plain SQLite is too slow on my huge databases.

As soon as the code will be ready fro inclusion I will attach a patch here for review.

Best regards,
Andrey

[warmerdam]

Andrey,

OK, well I'm going to turn the ticket over to you and just be a cc:

Note it is also my goal that the sqlite driver support non-spatialite sqlite databases, including those with geometry in Autodesk FGF format per:

​http://trac.osgeo.org/fdo/wiki/FDORfc16

I would note that I hope to avoid actually loading the sqlite extension shared library as I think this would complicate things a lot.

[dron]

I will keep already working functionality intact.

In regard of loadable extensions I am afraid I won't achieve my goal without SpatiaLite module. Anyway, it can be done in incremental manner. The first patch will do as much as possible without external modules and afterwards we can discuss how to deal with the loadable extension.

[warmerdam]

Andrey,

Ok, well I can live with using the loading module as long as things still function reasonably well without it (perhaps lower performance or no feature creation/update in spatialite tables).

[dron]

There is the first patch to support SpatiaLite extension (SL). Added support for reading SL variant of metadata tables (SL databases are automatically detected), ability to create new SL compatible feature tables, support for writing out geometry BLOBs in internal SL format and support for creation of new SL-compatible databases from the scratch.

No SL functions were used, so no loadable modules required.

The next step is to utilize those functions to speed-up geometry search and retrieval.

[dron]

Support for SpatiaLite-compatible databases

[dron]

One more patch: use the ALTER TABLE ADD COLUMN command instead of the current complicated logic to duplicate the data via temporary table. Can be applied independently from the first patch and required for further work.

[dron]

Use the ALTER TABLE command when adding fields instead of the whole table rewriting.

[crschmidt]

Andrey,

sqlite2 doesn't support ALTER TABLE, right? So this patch would remove support for adding columns to sqlite2 tables? Or am I misunderstanding something?

[dron]

sqlite2 does not support ALTER TABLE, all right. But support for sqlite2 was dropped a long time ago. See http://trac.osgeo.org/gdal/changeset/13276. ALTER TABLE ADD COLUMN is available in sqlite3 since 2005, so it will be aged when the GDAL 1.7 will come out :-)

BTW, the way it was done is quite unsafe in regard to user's data. The old function copies the table records only, dropping possible triggers or indexes. Maybe there is a better way to emulate ALTER TABLE using pure SQL.

[warmerdam]

I think it is fine for GDAL 1.6 to only support sqlite3 though it might be worth making this explicit in the documentation and the NEWS file.

[dron]

I've created a page documenting the SQLite 3 requirement: http://trac.osgeo.org/gdal/wiki/SQLite. There will be more information on that page as soon as the re will be support for SpatiaLite extension.

[Even Rouault]

In r16847, I've commited a variation of "0001-Added-support-for-SpatiaLite-extension.patch". I've completely rewritten the code to serialize/deserialize SpatiaLite geometry blobs, as it turns out that we cannot reuse existing WKB serialization/deserialization code in the case of geometry collections (so GDAL 1.6.0 cannot read them properly). I've also added a workaround when we do a SQL request on a spatialite table : the type for the geometry column is returned as a blob. But we cannot know if it is WKB or SpatiaLite format. So we first try as WKB, and if it fails (the first time) we retry as SpatiaLite.

Test added in r16848

[Even Rouault]

In r16849, add compatibility for older version of sqlite3 that don't support COLLATE NOCASE (like the one used on telascience slavebot)

[Even Rouault]

In r17083, I've added support for linking against libspatialite instead of libsqlite3. This enables the user to use spatial functions from SpatiaLite. Of course, creation and reading of SpatiaLite dababases still works with regular sqlite3.

Tests added in r17084.

Note : Windows support is not done. I don't think it would be hard to add, but have no way to do it myself.

[Even Rouault]

nmake.opt contains directions in r17892 how to build with spatialite on win32