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

[Traian Stanev]

Hello,

I have checked in the initial implementation for the SQLite provider described in RFC 16.
The code is still pretty raw and mostly (well, entirely) untested, but has been known to display stuff when asked nicely.

Here are some initial release notes:

- The FDO commands that are known to work: CreateDataStore, CreateSpatialContext, GetSpatialContexts, ApplySchema (adding new classes only), DescribeSchema, Select, SelectAggregates (incomplete, but SpatialExtent, Count should work), Insert. Update is implemented, but not run yet. The Insert command has a somewhat special transaction behavior in order to make it faster. This is documented in the code.

- The experimental spatial index is implemented, and BBOX queries during Select work. The spatial index is built on the fly during the first Select. This is not as bad as it sounds -- I timed it at 0.29 seconds for a 2 million linestring Navteq street data file. The efficiency of the spatial index is strongly dependent on the spatial coherency of the features in the data set (see the section about the converter tool on how to make it so).

- I took some liberties in deviating from my own spec when implementing support for custom FDO functions. I said that none of those would be supported, but instead I implemented sin, cos, tan, asin, atan, atan2, acos, pow, exp, ln, log10, floor, ceil, round, mod, trunc, sign, stdev (aggregate), median (agg), variance (agg). These are implemented as runtime extensions to SQLite, and are therefore available with the plain SQL command as well. In fact that is the only way I tested them, and I only tested the sin function -- I assumed the rest work by mathematical induction. :) They do not do error checking or type checking, except for null value checks.

- I also implemented all the spatial operations, not just BBOX. These are also implemented as SQLite extension functions and available to arbitrary SQL (with some caveats). To make these available to FDO Select, I implemented a rudimentary query translator that converts FDO filters to SQL where clauses that incorporate the spatial op functions. The actual implementation of the spatial ops uses FDO's SpatialUtility (so the same code as SDF).

- Implemented a GeomFromText SQL function, which can be used to make FGF blobs for use with the above functions. The query translation code doesn't use it though -- it uses binhex encoded FGF blobs directly in the SQL query instead.

- The sources include a converter tool that can be used to convert SHP and SDF files to SQLite databases conforming to RFC16. The converter uses the FDO API to the provider and also includes an experimental spatial optimization option, which can rearrange the features in such a way that an optimal spatial index can be generated later when the data is read. Details about the algorithm are in comments in the source code for the converter. Note that using the spatial optimization option will make conversion take considerably longer than without the option.

- The sources include the source for SQLite. This is because I made some performance mods to SQLite. Currently, the provider can only compile against this build of SQLite, until I introduce a #define in the source code to make it possible to build against a generic SQLite source tree. Note that those changes in no way affect the database format. They are there for performance reasons only.

- Some more disclaimers: You need an SSE2 capable CPU to run the standard build of the provider. This is because certain logic in the spatial index and geometry extents code is implemented using SSE intrinsics. There is a preprocessor macro that lets you disable that if necessary. I have not had time to create Linux makefiles yet. Most of the stuff I described has not been tested. Most of it has not even executed yet. Batteries not included. May cause drowsiness etc...:)


Cheers,
Traian




> -----Original Message-----
> From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-
> bounces at lists.osgeo.org] On Behalf Of Greg Boone
> Sent: Thursday, April 03, 2008 1:59 PM
> To: FDO Internals Mail List
> Subject: RE: [fdo-internals] Vote: FDO RFC 16 - FDO Provider for SQLite
>
> Hi All,
>
> Based on a unanimous vote, I declare the motion carried,
>
> Greg
>
> -----Original Message-----
> From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-
> bounces at lists.osgeo.org] On Behalf Of Jason Birch
> Sent: Monday, March 31, 2008 1:51 PM
> To: FDO Internals Mail List
> Subject: [fdo-internals] Vote: FDO RFC 16 - FDO Provider for SQLite
>
> Given the modifications to the RFC to address questions raised on this
> list, and a lack of further discussion I hereby move to approve FDO RFC
> 16 - FDO Provider for SQLite.
>
> https://trac.osgeo.org/fdo/wiki/FDORfc16
>
> +1 from me
>
> Jason
>
> -----Original Message-----
> From: Traian Stanev
> Subject: RE: [fdo-internals] FDO RFC 16 - FDO Provider for SQLite
>
> I think it has enough stuff in it to vote on.
>
> _______________________________________________
> 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