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

Traian Stanev traian.stanev at autodesk.com
Tue Mar 18 23:01:23 EDT 2008

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

If this is an interchange format, why do you need to store a spatial index in there? It would only make the file bigger? Spatial indexes are very fast to generate on the fly, so why carry one around with you? 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.

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


From: fdo-internals-bounces at lists.osgeo.org [fdo-internals-bounces at lists.osgeo.org] On Behalf Of Jason Birch [Jason.Birch at nanaimo.ca]
Sent: Tuesday, March 18, 2008 10:42 PM
To: FDO Internals Mail List; FDO Internals Mail List
Subject: RE: [fdo-internals] FDO RFC 16 - FDO Provider for SQLite

Mateusz wrote:

> IOW, the native format is defined per a database instance.

No, per table.  A single database can store multiple tables, each of which could (but probably shouldn't in most cases) have their own geometry type stored.

Actually, I would guess that a table could have multiple geometry columns, each of different type, as long as there were multiple rows in geometry_columns.  But I don't think I'd want to encourage or advertise that :)

> I don't understand what does the "would have to support these formats" mean?

In order for this to be a truly portable solution (just send someone the .db file and they get spatial data), then each implementation (FDO, GDAL, GeoTools) would have to implement support for all geometry types.  I want this to be a strong data access format, but also a powerful interchange format.

> There is possible situation in which B database is unusable with OGR
> driver, but the FDO Provider for SQLite can use both, and in A case can
> switch to WKB.

Or, better yet, OGR and FDO both support both data types, but with translation performance penalty for WKB under FDO.
> IOW, databases are not really portable.

I think that they can be, with proper implementation support in OGR and FDO and with use of the additional geometry type column in the geometry_columns table.

The use of custom functions in SQLite is certainly an option, but I'd really like to see the output-centric version of this "format" that Traian has outlined first.   I'd prefer to see a persisted, native, spatial index in SQLite also, but that seems like a fairly large-scope item, and would probably require coordination with the SQLite project to implement.


fdo-internals mailing list
fdo-internals at lists.osgeo.org

More information about the fdo-internals mailing list