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

[Traian Stanev]

> -----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 11:34 PM
> To: FDO Internals Mail List
> Subject: Re: [fdo-internals] FDO RFC 16 - FDO Provider for SQLite
>
> 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.

I was replying to Jason who was hoping to use it as an interchange format.

>
> > 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.

It depends on the quality of the index and the amount of preprocessing
you have done on the arrangement of the features in the table to exploit
spatial locality.

> 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.
>

Yes, a spatial index would make sense if SQLite's engine was extended to
support spatial operations. I am not attempting to do this in this RFC
but the possibility will be there to do this in the future. Making SQLite
spatial would involve work with the SQLite author, since it would need to be
integrated into the SQLite codebase.


> > 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.
>

OK.


Traian

> 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