FDO RFC 16 - FDO Provider for SQLite

This page contains a request for comments document (RFC) for the FDO Open Source project. More FDO RFCs can be found on the RFCs page.

Status

RFC Template Version	(1.0)
Submission Date	2008-03-18
Last Modified	Jason Birch 2008-03-24
Author	Traian Stanev
RFC Status	draft
Implementation Status	under development
Proposed Milestone
Assigned PSC guide(s)	(when determined)
Voting History	(vote date)
+1
+0
-0
-1

Overview

This RFC proposes a new FDO provider for accessing (spatial) data stored in SQLite database files.

Motivation

SQLite is an efficient and popular way to store data. Support for accessing SQLite databases via FDO would expand the range of data sources supported by FDO. A SQLite format for storing spatial data would make a good interchange format, similar to, but more flexible than Shapefiles.

What makes a valid spatial SQLite database?

The goal of this simple specification is to allow access to SQLite databases in as open and standard way as possible. This document does not conform to the OGC Simple Features for SQL implementation specification (​06-104r3), but it does leverage the specification where practical.

Here is the minimum set of characteristics expected in SQLite database:

• well-defined subset of the geometry_columns table (as per SF spec, section 7.1.3.2, 7.1.3.3); see below for specific column requirements,
• spatial_ref_sys table (as per SF spec, section 7.1.2.2, 7.1.2.3)
• geometry_format column extending the geometry_columns table, set to one of WKB, WKT, WKB12, WKT12 or FGF. If not present, WKB will be assumed.
• one or more tables to store the geometry and associated features. Although in general ​type does not matter in SQLite, BLOB type is recommended at least for WKB and FGF formats as it does not perform character translation on the data.

Note that this implementation does not currently include support for the enhanced multi-dimensional WKB and WKT formats that are defined in the OGC Simple Feature Access specification ​06-103r3. The tags WKB12 and WKT12 are reserved for future use in support of this enhanced format. FGF refers to the FDO Binary Geometry Format.

geometry_columns Subset

SQLite databases require the following fields in their geometry_columns table. Other fields are permitted, but will not affect data access. For detailed explanations of all of the standard fields (everything but geometry_format), see the SFSQL ​06-104r3 document, section 7.1.3.3.

Column Name	Type	Notes
f_table_name	VARCHAR	name of a feature table with a geometry column.
f_geometry_column	VARCHAR	name of a geometry column in the feature table
geometry_type	INTEGER	integer value representing the form of geometry (1=POINT, 2=LINESTRING, etc). See Table 4 on Page 29-31 of the SFSQL ​06-104r3 spec for a full list.
coord_dimension	INTEGER	dimensionality of the geometric features: 2, 3 or 4
srid	INTEGER	foreign key into the spatial_ref_sys table
geometry_format	VARCHAR	One of "WKB", "WKT", "WKB12", "WKT12", "FGF"

The suggested definition is:

CREATE TABLE geometry_columns (
            f_table_name VARCHAR, 
            f_geometry_column VARCHAR, 
            geometry_type INTEGER, 
            coord_dimension INTEGER, 
            srid INTEGER,
            geometry_format VARCHAR )

Proposed FDO Provider

The proposed SQLite provider will have the following features:

• Data stored and consumed in native SQLite format, and therefore accessible to other SQLite-based applications;
• Geometry will be serialized in FDO geometry format (FGF), WKB, or WKT and stored in a BLOB column. The type of geometry encoding will be specified in the geometry_columns table in a column named geometry_format. In addition, the provider may support point geometry with coordinates stored in non-geometric columns, via a connection string setting.
• A non-persistent spatial index usable for BBOX queries, built on the fly and valid for the duration of the FDO connection;
• Execution of arbitrary SQL commands;
• Metadata about feature geometry and coordinate systems will be stored in OGC geometry_columns and spatial_ref_sys tables;
• A tentative list of supported FDO commands : DescribeSchema, Select, SelectAggregates, Insert, Update, Delete, GetSpatialContexts, arbitrary SQL, ApplySchema, CreateDataStore;

FDO features the provider will initially *not* support:

• Feature class inheritance;
• Geometry filters other than BBOX;
• FDO Transactions -- but you can still manually execute BEGIN/COMMIT around other FDO commands;
• Associations;
• Constraints;
• Any other exotic feature you can think of.

Implications

This is a new provider, so there will be no issues with backwards compatibility. We need to coordinate with the GDAL/OGR project (and any other data access libraries that express an interest) to make sure the metadata and geometry formats are usable cross-platform.

Test Plan

There will be a small test suite that tests often used functionality. Arbitrary SQL execution will not be tested extensively.

Funding/Resources

References

• ​SQLite
• ​SpatialLite - a spatial extension for SQLite
• FGF Binary Specification
• ​OGC Simple Features for SQL (see 06-104r3)
• ​OGC Simple Feature Access (see 06-103r3)