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

[Traian Stanev]

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.

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



> -----Original Message-----
> From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-
> bounces at lists.osgeo.org] On Behalf Of Mateusz Loskot
> Sent: Tuesday, March 18, 2008 1:52 PM
> To: FDO Internals Mail List
> Subject: Re: [fdo-internals] FDO RFC 16 - FDO Provider for SQLite
>
> Jason Birch wrote:
> >  - Store the spatial index in the database.  I don't really
> understand
> > this but maybe something from this discussion is useful:
> >    http://readlist.com/lists/sqlite.org/sqlite-users/0/1896.html
>
> Indeed, there are 3 or 4 more threads with Noel about implementing
> spatial feature to SQLite.
>
> Two years ago I worked together with Noel. He implemented spatial
> indexing for SQLite but hasn't chance to publish it but there was some
> plan. Unfortunately, year ago he sold his company GisTek and he is not
> on the market anymore.
>
> > - Although I like the FGF format, I'd be worried that embedding it as
> > blob data would close the format.  OGR has an interesting convention
> for
> > using WKT columns with SQLite.  It would be nice if the FDO provider
> > could support that approach in addition to the default approach, and
> > maybe WKB too?:
> >    http://www.gdal.org/ogr/drv_sqlite.html
>
> WKB is not sufficient for that purpose. What is needed here is so
> called
> "spatial native format" similar to EWKB (EWKT), binary format used
> natively in MySQL. I think, WKT is out of consideration due to
> performance weakness of analysing plain text data.
>
> Summarizing, FGF seems to be good choice as native format for
> spatial types.
>
> Greetings
> --
> Mateusz Loskot
> http://mateusz.loskot.net
> _______________________________________________
> fdo-internals mailing list
> fdo-internals at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-internals