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

[Jason Birch]

Frank,
 
Obviously (?) we're not shooting for SFSQL certification for this format, and I'm definitely not a stickler for standards, but I would feel better matching the current OGC spec than older the PostGIS schema.  Especially given that we are potentially setting a template for future spatial storage in non-spatial formats.
 
There are some problems that mean that we can't just adopt the spec wholesale though.
 
First, the geometry_type field in SFSQL1.2 is very close to TYPE in PostGIS, but it uses integer-based lookup.  This looks awfully Oracle-ish; not surprising given the editor I guess.  It does support curve strings and more (pp 39-41), just as the new WKB spec does.
 
Second, storage_type in SFSQL1.2 is very close to what we want to accomplish with geometry_format, but again is integer-based, and only flags whether the geometry is normalised (ick - does anyone actually do this?) or WKB (pp 28-29).  We would have to extend this to support WKT and FGF.
 
As far as self-descriptive values go, I agree with you.  Being a database geek, I'd personally prefer to see the use of integer codes and an FK table of all of the values and their english equivalents (which your code would ignore), but since we'd have to mangle the specification anyway I guess that I'm not opposed to using varchar in these places...
 
Of course, I'm not building it, so take this as specifications wankery if you want.  I should also admit to being almost as annoyed at seeing "type" as a field name as I am when I get an Access database with "date" as a field name, so the rest of my comments are probably a bit bent. ;)
 
Jason

________________________________

From: Frank Warmerdam
Subject: Re: [fdo-internals] FDO RFC 16 - FDO Provider for SQLite

The "type" field is based on what I saw in PostGIS.  I'm not sure if it is
described in the SF spec or not.  But, without a fairly specific reason to
be different, I'd prefer to match what PostGIS does so it is easy for folks
who already work with it to understand what is being done in SQLite.  So,
based on that I'm not *keen* on changing the field name or the contents.

There are various reasons I don't like using integer codes in the field.
First, it is different than PostGIS.  Second, I don't know what sorts of
values to use for odd things like FGF curves.  Third, I find the strings
more self-descriptive than integer codes.

I didn't really understand why you preferred storage_type to geometry_format.
The geometry_format is unique to the sqlite proposal as far as I know, so
if there is a reason for storage_type instead, I'd be quite open to a
change.  Does this align better with some other format?

BTW, the naming, etc proposed for stuff in sqlite could just as easily
be applied to any other non-spatial RDBMS type environment.