[fdo-internals] FDO RFC 16 - FDO Provider for SQLite

[Traian Stanev]

I think my reply to RFC 16 questions got sent to the bit bucket, so here it is, again:

Hi folks,

Let me try to respond to the questions that were raised while I was having lunch in this single response. Have in mind that the RFC partially reflects work I have already done and also I would like to keep the first release tight and have it only support things that are absolutely necessary.

* Use the spatial_ref_sys and geometry_columns standard metadata tables

    I have never heard of this before (just goes to show that I am not into spatial databases). If this is simple to implement, and someone points me to a site where I can convince myself, then yes, we can use these tables to store coord sys.

* Store the spatial index in the database.

    This is specifically something I decided against for this provider, at least for the first release. Spatial index table in the db would fragment it and is too slow to query. It also needlessly increases the file size. I am experimenting with a custom type of spatial index structure that I cooked up a couple of days ago (not R-Tree, nor quad tree) that I would like to try out. It is designed for fast build time and also optimized for the use cases I care about (i.e. real world performance over absolute accuracy). If this doesn't pan out, I will use an in-memory R-Tree, which can be persisted in a similar way as SDF currently does. Frank, I acknowledge that there may be issues with the index getting stale if features are inserted or updated into the database from another connection. I am thinking of ways to alleviate that, but it will always be a problem, even with a persisted R-Tree, just like it is a problem in SDF today.

* Use of FGF format

    Using FGF is done for performance. Support for WKB and WKT would be trivial to add, but it would not perform as well. If enough people would like to see WKB support in the first release, we can add it to the RFC, since it doesn't seem like much work to implement a converter between the two formats.

* About integrating spatial functions in the SQLite SQL engine

    I'm sure this is possible (I already modify SQLite sql engine byte code on the fly in order to make it iterate through records more quickly). However, integrating a spatial index in SQLite, which is required for support of other spatial operations would require too much work in the SQLite query optimizer . I'd rather not do this work initially.

* PRJ file support

    Could go either way on that... Seemed useful to have in case one has data files that should not be modified but wants to set a coordinate system.

Frank, I have some files I can send you, but they are pretty simple regular SQLite databases containing a geometry BLOB column. I have an FDO-based tool that converts SHP and SDF files to SQLite databases, that I can also share.


Traian