SQLite / Spatialite RDBMS

OGR optionally supports spatial and non-spatial tables stored in SQLite 3.x database files. SQLite is a "light weight" single file based RDBMS engine with fairly complete SQL semantics and respectible performance.

The driver can handle "regular" SQLite databases, as well as Spatialite databases (spatial enabled SQLite databases).

The SQLite database is essentially typeless, but the SQLite driver will attempt to classify attributes field as text, integer or floating point based on the contents of the first record in a table. None of the list attribute field types existing in SQLite. Starting with OGR 1.10, datetime field types are also handled.

SQLite databases often due not work well over NFS, or some other networked file system protocols due to the poor support for locking. It is safest to operate only on SQLite files on a physical disk of the local system.

SQLite is an optionally compiled in driver. It is not compiled in by default.

By default, SQL statements are passed directly to the SQLite database engine. It's also possible to request the driver to handle SQL commands with OGR SQL engine, by passing "OGRSQL" string to the ExecuteSQL() method, as name of the SQL dialect.

Starting with OGR 1.8.0, the OGR_SQLITE_SYNCHRONOUS configuration option has been added. When set to OFF, this issues a 'PRAGMA synchronous = OFF' command to the SQLite database. This has the advantage of speeding-up some write operations (e.g. on EXT4 filesystems), but at the expense of data safety w.r.t system/OS crashes. So use it carefully in production environments and read the SQLite related documentation.

"Regular" SQLite databases

The driver looks for a geometry_columns table layed out as defined loosely according to OGC Simple Features standards, particularly as defined in FDO RFC 16. If found it is used to map tables to layers.

If geometry_columns is not found, each table is treated as a layer. Layers with a WKT_GEOMETRY field will be treated as spatial tables, and the WKT_GEOMETRY column will be read as Well Known Text geometry.

If geometry_columns is found, it will be used to lookup spatial reference systems in the spatial_ref_sys table.

While the SQLite driver supports reading spatial data from records, there is no support for spatial indexing, so spatial queries will tend to be slow (use Spatialite for that). Attributes queries may be fast, especially if indexes are built for appropriate attribute columns using the "CREATE INDEX ON ( )" SQL command.

Tables with multiple geometries

Starting with OGR 1.10, tables that have multiple geometry columns registered in geometry_columns can be used by OGR. For such tables, there are as many OGR layers exposed as there are geometry columns. They are named "table_name(geometry_column_name)".

Note: this support is limited to read-only operations.

REGEXP operator

By default, the REGEXP operator has no implementation in SQLite. With OGR >= 1.10 built against the PCRE library, the REGEXP operator is available in SQL statements run by OGR.

VSI Virtual File System API support

(Require OGR >= 1.9.0 and SQLite >= 3.6.0)

The driver supports reading and writing to files managed by VSI Virtual File System API, which include "regular" files, as well as files in the /vsimem/ (read-write), /vsizip/ (read-only), /vsigzip/ (read-only), /vsicurl/ (read-only) domains.

Note: for regular files, the standard I/O operations provided by SQLite are used, in order to benefit from its integrity guarantees.

Using the SpatiaLite library (Spatial extension for SQLite)

(Starting with GDAL 1.7.0)

The SQLite driver can read and write SpatiaLite databases. Creating or updating a spatialite database requires explicit linking against SpatiaLite library (version >= 2.3.1). Explicit linking against SpatiaLite library also provides access to functions provided by this library, such as spatial indexes, spatial functions, etc...

A few examples : 

# Duplicate the sample database provided with SpatiaLite
ogr2ogr -f SQLite testspatialite.sqlite test-2.3.sqlite  -dsco SPATIALITE=YES

# Make a request with a spatial filter. Will work faster if spatial index has
# been created and explicit linking against SpatiaLite library.
ogrinfo testspatialite.sqlite Towns -spat 754000 4692000 770000 4924000

Opening with 'VirtualShape:'

(Require OGR >= 1.9.0 and Spatialite support)

It is possible to open on-the-fly a shapefile as a VirtualShape with Spatialite. The syntax to use for the datasource is "VirtualShape:/path/to/shapefile.shp" (the shapefile must be a "real" file).

This gives the capability to use the spatial operations of Spatialite (note that spatial indexes on virtual tables are not available).

The SQLite SQL dialect

Starting with OGR 1.10, the SQLite SQL engine can be used to run SQL queries on any OGR datasource if using the SQLite SQL dialect.

The VirtualOGR SQLite extension

Starting with OGR 1.10, the GDAL/OGR library can be loaded as a SQLite extension. The extension is loaded with the load_extension(gdal_library_name) SQL function, where gdal_library_name is typically libgdal.so on Unix/Linux, gdal110.dll on Windows, etc..

After the extension is loaded, a virtual table, corresponding to a OGR layer, can be created with one of the following SQL statement : 

CREATE VIRTUAL TABLE table_name USING VirtualOGR(datasource_name);
CREATE VIRTUAL TABLE table_name USING VirtualOGR(datasource_name, update_mode);
CREATE VIRTUAL TABLE table_name USING VirtualOGR(datasource_name, update_mode, layer_name);
CREATE VIRTUAL TABLE table_name USING VirtualOGR(datasource_name, update_mode, layer_name, expose_ogr_style);
where : Note: layer_name does not need to be specified if the datasource has only one single layer.

From the sqlite3 console, a typical use case is : 

sqlite> SELECT load_extension('libgdal.so');

sqlite> SELECT load_extension('libspatialite.so');

sqlite> CREATE VIRTUAL TABLE poly USING VirtualOGR('poly.shp');

sqlite> SELECT *, ST_Area(GEOMETRY) FROM POLY;
215229.266|168.0|35043411||215229.265625
247328.172|179.0|35043423||247328.171875
261752.781|171.0|35043414||261752.78125
547597.188|173.0|35043416||547597.2109375
15775.758|172.0|35043415||15775.7578125
101429.977|169.0|35043412||101429.9765625
268597.625|166.0|35043409||268597.625
1634833.375|158.0|35043369||1634833.390625
596610.313|165.0|35043408||596610.3359375
5268.813|170.0|35043413||5268.8125

Alternatively, you can use the ogr_datasource_load_layers(datasource_name[, update_mode[, prefix]]) function to automatically load all the layers of a datasource. 

sqlite> SELECT load_extension('libgdal.so');

sqlite> SELECT load_extension('libspatialite.so');

sqlite> SELECT ogr_datasource_load_layers('poly.shp');
1
sqlite> SELECT * FROM sqlite_master;
table|poly|poly|0|CREATE VIRTUAL TABLE "poly" USING VirtualOGR('poly.shp', 0, 'poly')
Refer to the SQLite SQL dialect for an overview of the capabilities of VirtualOGR tables.

Creation Issues

The SQLite driver supports creating new SQLite database files, or adding tables to existing ones. Note that a new database file cannot be created over an existing file.

Database Creation Options

Layer Creation Options

Performance hints

SQLite is a Transactional DBMS; while many INSERT statements are executed in close sequence, BEGIN TRANSACTION and COMMIT TRANSACTION statements have to be invoked appropriately in order to get optimal performance. The default OGR behavior is to COMMIT a transaction every 200 inserted rows. This value is surely too low for SQLite; and closing too much frequently the current transaction causes severe performance degradation. The -gt argument allows to explicitly set the number of rows for each transaction. Explicitly defining -gt 1024 usually ensures a noticeable performance boost; defining an even bigger -gt 65536 ensures optimal performance while populating some table containing many hundredth thousand or million rows.

SQLite usually has a very minimal memory foot-print; just about 20MB of RAM are reserved to store the internal Page Cache [merely 2000 pages]. This value too may well be inappropriate under many circumstances, most notably when accessing some really huge DB-file containing many tables related to a corresponding Spatial Index. Explicitly setting a much more generously dimensioned internal Page Cache may often help to get a noticeably better performance. Starting since GDAL 1.9.0 you can explicitly set the internal Page Cache size using the configuration option OGR_SQLITE_CACHE value [value being measured in MB]; if your HW has enough available RAM, defining a Cache size as big as 512MB (or even 1024MB) may sometimes help a lot in order to get better performance.

Setting the OGR_SQLITE_SYNCHRONOUS configuration option to OFF might also increase performance when creating SQLite databases (altough at the expense of integrity in case of interruption/crash ).

Credits

Links