Should sql functions be part of geopackage spec?

[Pepijn Van Eeckhoudt]

This post is feedback on the vector portion of the draft GeoPackage spec. I'm posting it in the vector group since I think it makes more sense to discuss this here.

The draft spec contains a section listing common geometry related SQL functions. These functions are very useful, but in my opinion they're part of the data access layer, not really part of the file format itself. I think it would be better to leave out the functions and instead simply specify the geometry blob structure. In order to allow multiple geopackage reader / writer implementations I believe this is more important than the functions.

If the blob structure is specified, it is perfectly possible to read from / write to a geopackage/spatialite database without using any of the ST_* functions. This happens to be exactly what our LuciadMobile product is already doing ;) Rather than using AsBinary or AsText when querying from the database we query the raw blobs directly. Those are then converted to our internal in-memory object representation. When writing to the database we directly encode our objects to the spatialite geometry representation as well. The main reason to do this is efficiency. We wanted to avoid the additional step of converting the internal geometry representation to generic WKB/WKT.

Sincerely,

Pepijn Van Eeckhoudt

[Paul Daisey]

Pepjin:


On Wednesday, May 2, 2012 3:25:21 AM UTC-4, Pepijn Van Eeckhoudt wrote:This post is feedback on the vector portion of the draft GeoPackage spec. I'm posting it in the vector group since I think it makes more sense to discuss this here.

The draft spec contains a section listing common geometry related SQL functions. These functions are very useful, but in my opinion they're part of the data access layer, not really part of the file format itself. I think it would be better to leave out the functions and instead simply specify the geometry blob structure. In order to allow multiple geopackage reader / writer implementations I believe this is more important than the functions.

   "It's a feature, not a bug"  ;-}

    The OGC / ISO SF-SQL specs are focused on the Geometry information model and SQL access to it, require those to be implemented and behave as specified, and do not address the storage format details:  See section 1 of OGC 06-104r3, which says:

This standard
a) Establishes an architectural framework for the representation of feature,
b) Establishes a set of definitions for terms used within that framework,
c) Defines a simple geometric profile of ISO 19107 for the definition of the geometric attributes used in that
framework
d) Describes a set of SQL Geometry Types together with SQL functions on those types.
The Geometry Types and Functions described in this standard represent a profile of ISO 13249-3. This standard
does not attempt to standardize and does not depend upon any part of the mechanism by which Types are added
and maintained in the SQL environment including the following:
a) The syntax and functionality provided for defining types;
b) The syntax and functionality provided for defining SQL functions;
c) The physical storage of type instances in the database;
d) Specific terminology used to refer to User Defined Types, for example, UDT.
This standard does standardize:
a) Names and geometric definitions of the SQL Types for Geometry;
b) Names, signatures and geometric definitions of the SQL Routines for Geometry.

This has worked well enough in practice over more than 12 years in a number of RDBMS and GIS implementations, and with one open-source lib (GEOS) widely used under the hood of
many implementations, that I feel foolish trying to reinvent this wheel.

If the blob structure is specified, it is perfectly possible to read from / write to a geopackage/spatialite database without using any of the ST_* functions. This happens to be exactly what our LuciadMobile product is already doing ;) Rather than using AsBinary or AsText when querying from the database we query the raw blobs directly. Those are then converted to our internal in-memory object representation. When writing to the database we directly encode our objects to the spatialite geometry representation as well. The main reason to do this is efficiency. We wanted to avoid the additional step of converting the internal geometry representation to generic WKB/WKT.

With SpatiaLite as the initial reference implementation for vector data, the GeoPackage draft specification references http://www.gaia-gis.it/gaia-sins/BLOB-Geometry.html to document the BLOB structure.  It is perfectly possible for GeoPackage client to read and write these directly, at the risk that the physical format (and potentially the reference implementation) will change in the future.  So the design tradeoff is whether or not to sacrifice some performance by using the supplied SQL functions to get WKBs out of the stored BLOBs, and let the GeoPackage implementor worry about making sure those work if the physical format changes in the future, or getting maximum performance by bearing the future format compatibility risk yourself.  The draft GeoPackage specification is silent on the issue.  Future procurements of applications that load and use geopackages may have more to say on the issue.  Multi-version compatibility is more important to some customers (with large installed bases to manage over long deployment cycles) than others.

v/r
Paul

[Pepijn Van Eeckhoudt]

Hi Paul,

On 2012-05-11 22:06, Paul Daisey wrote:
> The OGC / ISO SF-SQL specs are focused on the Geometry information
> model and SQL access to it, require those to be implemented and
> behave
> as specified, and do not address the storage format details

That sentence is key. The way I see it sf-sql is a standardized
abstraction of the implementation details. The way I understood it,
geopackage is a storage format though. I'm not trying to say the you
should reinvent what's in sf-sql; quite the contrary. But in my opinion
API-level stuff doesn't belong in a format spec.

Pepijn