Opened 13 years ago

Closed 12 years ago

#2666 closed enhancement (fixed)

Add support for spatiLite extension in OGR SQLITE driver

Reported by: dron Owned by: dron
Priority: normal Milestone: 1.7.0
Component: OGR_SF Version: svn-trunk
Severity: normal Keywords:
Cc: warmerdam, jbirch

Description

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

Attachments (2)

0001-Added-support-for-SpatiaLite-extension.patch (46.7 KB) - added by dron 13 years ago.
Support for SpatiaLite?-compatible databases
0001-Use-the-ALTER-TABLE-command-when-adding-fields-inste.patch (8.9 KB) - added by dron 13 years ago.
Use the ALTER TABLE command when adding fields instead of the whole table rewriting.

Download all attachments as: .zip

Change History (15)

comment:1 Changed 13 years ago by warmerdam

Cc: jbirch added

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.

comment:2 Changed 13 years ago by dron

Status: newassigned

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.

comment:3 Changed 13 years ago by 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).

comment:4 Changed 13 years ago by 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.

Changed 13 years ago by dron

Support for SpatiaLite?-compatible databases

comment:5 Changed 13 years ago by 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.

Changed 13 years ago by dron

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

comment:6 Changed 13 years ago by 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?

comment:7 Changed 13 years ago by 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.

comment:8 Changed 13 years ago by 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.

comment:9 Changed 13 years ago by 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.

comment:10 Changed 12 years ago by 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

comment:11 Changed 12 years ago by Even Rouault

Milestone: 1.7.0

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

comment:12 Changed 12 years ago by 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.

comment:13 Changed 12 years ago by Even Rouault

Resolution: fixed
Status: assignedclosed

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

Note: See TracTickets for help on using tickets.