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

[Traian Stanev]

Hi Jason,

Yes, I'd like to keep it as simple as possible so that OGR can consume the same data. That is why I specifically stated that advanced features are out of the scope. Basically the format consists of regular SQLite tables with an extra BLOB column for geometry. Nothing more than that, apart from the coord sys stuff, which would not be explicitly required.

I agree that doing as much as possible in the database is good in this case -- that's why I will leave things like WHERE clause (FdoFilter) execution, indexing of particular columns, aggregate selects, etc to SQLite's engine. Extra FDO functions can be implemented as plugin functions to the SQLite SQL engine (called VDBE).

The reason I am not doing R-Tree this time is because I'd like to try something else. SDF already has an R-Tree that is persisted in SQlite, so it is not hard to have one for this provider also. However, as I said, I am convinced that in most cases an *in-memory* index would be a better fit, especially with SQLite, which is in a local file and a single table scan to generate the spatial index is very quick.


Traian


> -----Original Message-----
> From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-
> bounces at lists.osgeo.org] On Behalf Of Jason Birch
> Sent: Tuesday, March 18, 2008 3:30 PM
> To: FDO Internals Mail List
> Subject: RE: [fdo-internals] FDO RFC 16 - FDO Provider for SQLite
>
> The use of metadata views/tables is specified in the simple features
> spec (SQL option):
>
> http://www.opengeospatial.org/standards/sfs
>
> and is pretty common among spatial databases such as PostGIS, Oracle,
> etc.
>
> If adding .prj file support is easy, then I guess I would generally be
> in favour.  Doesn't this cause problems with the various platforms
> "interpretation" of WKT for projections, or is this handled natively by
> PROJ.4 and CSMap?
>
> Your notes about persisting spatial indices are way over my head :)
>
> Only supporting envelope-style queries with the initial implementation
> is fine with me.  The key to this provider (for me) is doing as much as
> possible in the database, and documenting explicitly any core
> functionality that is done in the provider.  If this "format" is
> supported across the board (FDO, OGR, and possibly GeoTools) then I
> think it stands a good chance of becoming the defacto standard for open
> source file-based geodatabase.  If this happens, then at some point
> someone will step up to add R-tree (or something else) and geo
> operations to SQLite.
>
> I think that this format is more likely to take off than SDF would,
> primarily because of the complexity of the features offered by SDF
> (schema, etc).  I certainly would continue using SDF, but I can
> certainly see a use case for a SQLite-based database, especially in
> cases when I want to access the data separately through ODBC or OLEDB.
> Also SQLite would make an exceptional data exchange format with none of
> the drawbacks of SHP (DBF character column limits, multiple files, etc)
> and could be easily supported in things like the OGR tools and FDO2FDO.
>
> Are there concerns about FGF only supporting little-endian encodings of
> the geometry ( http://tinyurl.com/3by8bd )?  Will this limit
> implementations on unix systems, or does it not matter?
>
> Jason
>
> -----Original Message-----
> From: Traian Stanev
> Subject: [fdo-internals] FDO RFC 16 - FDO Provider for SQLite
>
> 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
> _______________________________________________
> fdo-internals mailing list
> fdo-internals at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-internals
> _______________________________________________
> fdo-internals mailing list
> fdo-internals at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-internals