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

[Traian Stanev]

So, I took a closer look at this... Figure 3 (section 6.2.1) seems more relevant. It's the one that describes the schema for "databases with geometry types". It does not have the joined tables. Is this what we are talking about? I really hopes so, because I think I saw a UML diagram further down in the document and was afraid to scroll further (I don't want to have nightmares tonight).

Traian

________________________________________
From: fdo-internals-bounces at lists.osgeo.org [fdo-internals-bounces at lists.osgeo.org] On Behalf Of Traian Stanev
Sent: Tuesday, March 18, 2008 9:20 PM
To: FDO Internals Mail List
Subject: RE: [fdo-internals] FDO RFC 16 - FDO Provider for SQLite

OK I read the spec (well, I looked at Figure 1). It seems workable to use spatial_ref_sys and geometry_columns for metadata. However, in my proposal, the geometry and other attributes are in the same table and not in two joined tables like in the spec. This is not really a problem, but it implies that in the sqlite case the F_ and G_ columns in the geometry_columns table will be identical. Are there any objections to that?

Traian

________________________________________
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 3:29 PM
To: FDO Internals Mail List
Subject: RE: [fdo-internals] FDO RFC 16 - FDO Provider for SQLite

The use of metadata views/tables is specified in the simple features
spec (SQL option):

http://www.opengeospatial.org/standards/sfs

and is pretty common among spatial databases such as PostGIS, Oracle,
etc.

If adding .prj file support is easy, then I guess I would generally be
in favour.  Doesn't this cause problems with the various platforms
"interpretation" of WKT for projections, or is this handled natively by
PROJ.4 and CSMap?

Your notes about persisting spatial indices are way over my head :)

Only supporting envelope-style queries with the initial implementation
is fine with me.  The key to this provider (for me) is doing as much as
possible in the database, and documenting explicitly any core
functionality that is done in the provider.  If this "format" is
supported across the board (FDO, OGR, and possibly GeoTools) then I
think it stands a good chance of becoming the defacto standard for open
source file-based geodatabase.  If this happens, then at some point
someone will step up to add R-tree (or something else) and geo
operations to SQLite.

I think that this format is more likely to take off than SDF would,
primarily because of the complexity of the features offered by SDF
(schema, etc).  I certainly would continue using SDF, but I can
certainly see a use case for a SQLite-based database, especially in
cases when I want to access the data separately through ODBC or OLEDB.
Also SQLite would make an exceptional data exchange format with none of
the drawbacks of SHP (DBF character column limits, multiple files, etc)
and could be easily supported in things like the OGR tools and FDO2FDO.

Are there concerns about FGF only supporting little-endian encodings of
the geometry ( http://tinyurl.com/3by8bd )?  Will this limit
implementations on unix systems, or does it not matter?

Jason

-----Original Message-----
From: Traian Stanev
Subject: [fdo-internals] FDO RFC 16 - FDO Provider for SQLite

I think my reply to RFC 16 questions got sent to the bit bucket, so here
it is, again:

Hi folks,

Let me try to respond to the questions that were raised while I was
having lunch in this single response. Have in mind that the RFC
partially reflects work I have already done and also I would like to
keep the first release tight and have it only support things that are
absolutely necessary.

* Use the spatial_ref_sys and geometry_columns standard metadata tables

    I have never heard of this before (just goes to show that I am not
into spatial databases). If this is simple to implement, and someone
points me to a site where I can convince myself, then yes, we can use
these tables to store coord sys.

* Store the spatial index in the database.

    This is specifically something I decided against for this provider,
at least for the first release. Spatial index table in the db would
fragment it and is too slow to query. It also needlessly increases the
file size. I am experimenting with a custom type of spatial index
structure that I cooked up a couple of days ago (not R-Tree, nor quad
tree) that I would like to try out. It is designed for fast build time
and also optimized for the use cases I care about (i.e. real world
performance over absolute accuracy). If this doesn't pan out, I will use
an in-memory R-Tree, which can be persisted in a similar way as SDF
currently does. Frank, I acknowledge that there may be issues with the
index getting stale if features are inserted or updated into the
database from another connection. I am thinking of ways to alleviate
that, but it will always be a problem, even with a persisted R-Tree,
just like it is a problem in SDF today.

* Use of FGF format

    Using FGF is done for performance. Support for WKB and WKT would be
trivial to add, but it would not perform as well. If enough people would
like to see WKB support in the first release, we can add it to the RFC,
since it doesn't seem like much work to implement a converter between
the two formats.

* About integrating spatial functions in the SQLite SQL engine

    I'm sure this is possible (I already modify SQLite sql engine byte
code on the fly in order to make it iterate through records more
quickly). However, integrating a spatial index in SQLite, which is
required for support of other spatial operations would require too much
work in the SQLite query optimizer . I'd rather not do this work
initially.

* PRJ file support

    Could go either way on that... Seemed useful to have in case one has
data files that should not be modified but wants to set a coordinate
system.

Frank, I have some files I can send you, but they are pretty simple
regular SQLite databases containing a geometry BLOB column. I have an
FDO-based tool that converts SHP and SDF files to SQLite databases, that
I can also share.


Traian
_______________________________________________
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
_______________________________________________
fdo-internals mailing list
fdo-internals at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-internals