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

Wed Apr 9 09:00:47 EDT 2008

Hi Kenneth,

Some of the include/lib directories were wrong in the Debug configuration (since I only do Release builds). The /SDX directory is now called /Provider.
I fixed it in the vault.

Traian

From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Kenneth, GEOGRAF A/S
Sent: Wednesday, April 09, 2008 3:53 AM
To: FDO Internals Mail List
Subject: Re: [fdo-internals] FDO RFC 16 - FDO Provider for SQLite

The SVN trunk seems to miss the SDX folder, should I download it somewhere, or can you add it?
(Is it the Provider folder that was renamed?)

Regards, Kenneth, GEOGRAF A/S

Traian Stanev skrev:

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> [mailto:fdo-internals-

bounces at lists.osgeo.org<mailto: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> [mailto:fdo-internals-

bounces at lists.osgeo.org<mailto: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<mailto:fdo-internals at lists.osgeo.org>

http://lists.osgeo.org/mailman/listinfo/fdo-internals

_______________________________________________

fdo-internals mailing list

fdo-internals at lists.osgeo.org<mailto:fdo-internals at lists.osgeo.org>

http://lists.osgeo.org/mailman/listinfo/fdo-internals

_______________________________________________

fdo-internals mailing list

fdo-internals at lists.osgeo.org<mailto:fdo-internals at lists.osgeo.org>

http://lists.osgeo.org/mailman/listinfo/fdo-internals

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/fdo-internals/attachments/20080409/5ddc1804/attachment.html