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	Traian Stanev 2008-03-19
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 provider is to allow access to SQLite databases in as open and standard way as possible. Here is the minimum set of characteristics the provider will expect in SQLite database:

• geometry_columns table, as per SF spec, Part 2, SQL Option, section 6.2.1;
• spatial_ref_sys table as per SF spec;
• g_geometry_format column in geometry_columns table, set to one of WKB. FGF or WKT. If not present, WKB will be assumed as geometry format.

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 as BLOBs in FDO geometry format (FGF), WKB , or WKT 1.2 format. The type of geometry encoding will be specified in the geometry_columns table (see below) in a column named g_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, according to Section 6.2.1 of the OGC Simple Feature spec, Part 2;
• 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.

geometry_columns

Column Name	Type	Notes
f_table_catalog	VARCHAR	blank
f_table_schema	VARCHAR	blank
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
f_geometry_format	VARCHAR	One of "WKT", "WKB", "FGF", ... (should we differentiate between 1.0 and 1.2 geometries?)
coord_dimension	INTEGER	2, 3 or 4
srid	INTEGER	shared key with spatial_ref_sys table
type	VARCHAR	geometry class name (eg. "POINT")

Implications

This is a new provider, so there will be no issues with backwards compatibility. We need to coordinate with the GDAL/OGR project to make sure the metadata and geometry formats are usable by both FDO and OGR.

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)