|Version 17 (modified by warmerdam, 5 years ago)|
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.
|RFC Template Version||(1.0)|
|Last Modified||Traian Stanev 2008-03-19|
|Implementation Status||under development|
|Assigned PSC guide(s)||(when determined)|
|Voting History||(vote date)|
This RFC proposes a new FDO provider for accessing (spatial) data stored in SQLite database files.
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;
- Any other exotic feature you can think of.
|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, one of GEOMETRY, GEOMETRYCOLLECTION, POINT, MULTIPOINT, POLYGON, MULTIPOLYGON, LINESTRING, or MULTILINESTRING|
The above definition is related to the SFSQL specification from OGC and closely matches the PostGIS definition (with the addition of the f_geometry_format field). The suggested definition is:
CREATE TABLE geometry_columns ( f_table_catalog VARCHAR, f_table_schema VARCHAR, f_table_name VARCHAR, f_geometry_column VARCHAR, f_geometry_format VARCHAR, type VARCHAR, coord_dimension INTEGER, srid INTEGER )
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.
There will be a small test suite that tests often used functionality. Arbitrary SQL execution will not be tested extensively.
- SpatialLite - a spatial extension for SQLite
- FGF Binary Specification
- OGC Simple Features for SQL (see 06-104r3)