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

[Mateusz Loskot]

Mateusz Loskot wrote:
> Jason Birch wrote:
>> - Although I like the FGF format, I'd be worried that embedding it as
>> blob data would close the format.  OGR has an interesting convention for
>> using WKT columns with SQLite.  It would be nice if the FDO provider
>> could support that approach in addition to the default approach, and
>> maybe WKB too?:
>>    http://www.gdal.org/ogr/drv_sqlite.html
> 
> WKB is not sufficient for that purpose. What is needed here is so called 
> "spatial native format" similar to EWKB (EWKT), binary format used 
> natively in MySQL. I think, WKT is out of consideration due to 
> performance weakness of analysing plain text data.

I've discussed with Frank about FGF vs WKB/WKT and I think I owe some 
clarifications.

My positions are:

1. If we focus only on implementation of the FDO Provider for SQLite, 
then I think that FGF is a better choice than WKT/WKB because it will 
not require FGF<->WKB translations. Format translations are potential 
performance bottlenecks.

2. If we came to the conclusion that it's better to implement more 
universal solution, then WKB format is reasonable choice.

Potentially, there is a chance for development of universal solution 
without loosing max of performance. The option is to follow OGC SFS and 
common implementations like PostGIS or MySQL. Simply, we should be able 
to extend SQLite with OGC SFS functions: AsBinary and AsText
Then, following schemes would be possible:

1. Return FGF as native format, equivalent to PostGIS EWKB

SELECT the_geom FROM table:

2. Request WKB

SELECT AsBinary(the_geom) FROM table;

3. Request WKT

SELECT AsText(the_geom) FROM table;

Following this concept, it could be even possible to implement AsEWKT 
and AsEWKB to gain some level of compatibility with PostGIS.

Why I'm saying such option is possible?

 From version 3.3.6, SQLite supports loadable extension modules 
providing additional SQL functions:

http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions

"Additional functions may be written in C and added to the database 
engine using the sqlite3_create_function() API."

http://www.sqlite.org/lang_expr.html
http://sqlite.org/capi3ref.html#sqlite3_create_function

Short example about creating custom functions:

http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html
http://souptonuts.sourceforge.net/code/myfuncpp.cc.html

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