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

[Mateusz Loskot]

Traian Stanev wrote:
> The conversion between WKB and FGF is not expensive at all, but it
> does add up if you are reading a million features in a hurry.
> 
> Adding spatial index to SQLite would be like jamming a helicopter
> into the proverbial Miata, and expecting some sort of an improvement.
> :-)

I'm not that sure. SQLite does support B-Tree indexing, pretty useless 
for spatial applications. It's possible to extend SQLite with R-Tree
and get real spatial features.

Another question is if it makes sense put efforst in adding R-Tree 
support to SQLite for our purposes. I'm quite sure it doesn't but we 
still can discuss its pros/cons :-)

> If this is an interchange format, why do you need to store a spatial
> index in there? 

I've probably missed the "interchange" word in the RFC.
I understood the RFC is about using SQLite in similar manner as MySQL or 
Shapefiles, etc.

> It would only make the file bigger? Spatial indexes
> are very fast to generate on the fly, so why carry one around with
> you?

I'm not sure it's really fast, but won't argue.
The advantage of built-in index is potentially faster querying
if the index is bound to SQL engine.
I see another advantage in integrity of the overall solution:

SQLite + spatial functions + spatial index

and implementation of drivers and providers may be simpler.

> It would also force a single spatial index algorithm on people,
> which they have to implement on clients that wish to use it -- using
> a spatial index implies quite a bit of client code that knows how to
> make sense out of it. It makes more sense to have one persisted when
> the database is not as mobile as SQlite is. I'm not trying to be a
> naysayer, just want to understand the reasoning behind having an
> integrated spatial index.

Yes, I can see your points. They are reasonable to me.
I'm just sharing mine :-)

> Oh, I just updated the RFC to reflect the geomtry_columns and
> spatial_ref_sys thing.

Perhaps it would be good to emphasize "exchangability" of a database, as 
a reason of not building fully feature spatial database
upon the SQLite engine.

Greetings
-- 
Mateusz Loskot
http://mateusz.loskot.net