Data Access → Drivers → SQLite

The SQLite native data access driver allows applications to access spatial data stored in a SQLite database. It can access data stored as WKB/WKT in a pure SQLite database (compatible with OGR format) ¹⁾ or in the format of the extension SpatiaLite.

This section describes the design and implementation issues of this driver and how it can be used by application developers.

The SQLite driver implements all the data access foundation classes as showned in the following class diagram.

The above implementation allows one to obtain a SQLite data source from the abstract factory using the “SQLITE” identifier as is shown in the code snippet bellow:

te::da::DataSource* ds = te::da::DataSourceFactory::make("SQLITE");
 
ds->open("SQLITE_FILE=/data/sqlite/brasil.sqlite");

or you can also use a std::map with the database information:

std::map<std::string, std::string> dsInfo;
 
dsInfo["SQLITE_FILE"] = "/data/sqlite/brasil.sqlite";
 
te::da::DataSource* ds = te::da::DataSourceFactory::open("SQLITE", dsInfo);

or in a more simplified way:

te::da::DataSource* ds = te::da::DataSourceFactory::open("SQLITE", "SQLITE_FILE=/data/sqlite/brasil.sqlite");

The following set of parameters can be used when accessing data on a SQLite database through the Data Access classes:

• SQLITE_FILE: this is a mandatory parameter that specifies the database file name. Besides a regular file name (with full path if needed), there is two special values:
  • “:memory:“ → creates a temporary in-memory database that is vanished when the database is closed.
  • if it is an empty string, then a temporary on-disk database will be created and automatically removed as soon as the database is closed.
• SQLITE_OPEN_READONLY: if true the database is opened in read-only mode and if it doesn't exist an exception is raised. (Optional)
• SQLITE_OPEN_READWRITE: if true the database is opened for reading and writing if possible, or reading only if the file is write protected by the operating system. In either case the database must already exist, otherwise an exception is raised. (Optional)
• SQLITE_OPEN_CREATE: if this parameter is true and SQLITE_OPEN_READWRITE is true then the database is created if it doesn't exist yet. (Optional)
• SQLITE_CREATE_INTERMEDIATE_DIR: if this parameter is true when creating a new database all the intermediate directories will be also created otherwise an exception may be throw if intermediate directories doesn't exist. (Optional)
• SQLITE_CREATE_OGR_METADATA_TABLES: if this parameter is true and the database doesn't have the OGC simple feature spatial metadata tables it creates the OGC schema supported by the native OGR and FDO formats. It is also needed to have SQLITE_OPEN_READWRITE set to true. (Optional)
• SQLITE_CREATE_SPATIALITE_METADATA_TABLES: if this parameter is true and the database doesn't have the OGC simple feature spatial metadata tables it creates the OGC schema supported by SpatiaLite. (Optional)
• SQLITE_CREATE_TERRALIB_RASTER_METADATA_TABLES: if this parameter is true and the database doesn't have the TerraLib raster support schema it will create this schema. This schema has several benefits over RasterLite, see the raster support using TerraLib format for a detailed discussion. (Optional)
• SQLITE_HIDE_RASTER_TABLES: if TRUE the catalog loader will skip raster tables from the list of available datasets. (Optional)
• SQLITE_HIDE_SPATIAL_METADATA_TABLES: if TRUE the catalog loader will skip spatial metadata tables from the list of available datasets. (Optional)
• SQLITE_HIDE_TABLES: it is possible to provide a list of tables to be hidden from the catalog loader operations. It must be a comma separated list. (Optional)
• SQLITE_VFS: the name of a VFS object that provides the operating system interface to be used to access the database file on disk. If this option is set to an empty string or it is not present the default VFS object is used. (Optional)
• SQLITE_OPEN_NOMUTEX: TODO (Optional)
• SQLITE_OPEN_FULLMUTEX: TODO (Optional)
• SQLITE_OPEN_SHAREDCACHE: TODO (Optional)
• SQLITE_OPEN_PRIVATECACHE: TODO (Optional)
• SQLITE_OPEN_URI: TODO - this causes file name to be interpreted as an URI. See http://www.sqlite.org/uri.html for more details. (Optional)

A complete and update list of supported parameters can be retrieved in code through the getConnectionParameters method in the SQLite concrete data source factory.

In order to create a new SQLite database you can use the same parameters as showned above or just inform the file name.

// let's give the minimal database information needed to create it
std::map<std::string, std::string> newLiteRepositoryInfo;
 
newLiteRepositoryInfo["SQLITE_FILE"] = "./data/sqlite/teste.db";
 
// create a repository using data source static method
te::da::DataSource::create("SQLITE", newLiteRepositoryInfo);

To check if a database already exists you must specify just the file nameas showned in the following code snippet:

std::map<std::string, std::string> dsInfo;
 
dsInfo["SQLITE_FILE"] = "/mydir/mygisdb.sqlite";
dsInfo["SQLITE_OPEN_READONLY"] = "TRUE";
 
if(te::da::DataSource::exists("SQLITE", dsInfo))
  std::cout << "yes! it exists!";
else
  std::cout << "no! it doesn't exist!";

This driver will make the following mapping between SQLite data types and TerraLib data types:

• INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, INT2, BIGINT, UNSIGNED BIG INT, INT8 → TE_INT64_DT;
• FLOAT, DOUBLE, REAL, DOUBLE PRECISION → TE_DOUBLE_DT;
• NUMERIC, DECIMAL → TE_NUMERIC_DT;
• TEXT, CLOB, CHAR, VARCHAR → TE_STRING_DT (subtype: STRING);
• BLOB → TE_BYTE_ARRAY_DT (if it is not a geometric column);
• DATE → TE_DATETIME_DT (subtype DATE);
• DATETIME → TE_DATETIME_DT (subtype DATE);
• GEOMETRY, POINT, POINTZ, POINTM, POINTZM, LINESTRING, LINESTRINGZ, LINESTRINGM, LINESTRINGZM, POLYGON, POLYGONZ, POLYGONM, POLYGONZM, GEOMETRYCOLLECTION, GEOMETRYCOLLECTIONZ, GEOMETRYCOLLECTIONM, GEOMETRYCOLLECTIONZM, MULTIPOINT, MULTIPOINTZ, MULTIPOINTM, MULTIPOINTZM, MULTILINESTRING, MULTILINESTRINGZ, MULTILINESTRINGM, MULTILINESTRINGZM, MULTIPOLYGON, MULTIPOLYGONZ, MULTIPOLYGONM, MULTIPOLYGONZM → TE_GEOMETRY_DT with the appropriated subtype;
• BOOL, BOOLEAN → TE_BOOLEAN_DT;
• Any other data type will be mapped to TE_UNKNOWN_DT.

The reverse mapping:

• TE_CHAR_DT, TE_UCHAR_DT, TE_INT16_DT, TE_INT32_DT, TE_INT64_DT → INTEGER
• TE_FLOAT_DT, TE_DOUBLE_DT → REAL
• TE_STRING_DT (any subtype) → TEXT
• TE_NUMERIC_DT → NUMERIC
• TE_BYTE_ARRAY_DT → BLOB
• TE_DATETIME_DT (TIME_INSTANT) → DATETIME
• TE_DATETIME_DT (any other subtype) → DATE
• TE_GEOMETRY_DT → Corresponding geometry type in SpatiaLite or a BLOB/TEXT column when using OGR format

The SQLite SQLVisitor class re-implements some methods from te::da::SQLVisitor class in order to deal with SQLite specific types: blob and geometries.

When using the SpatiaLite extension the driver will attempt to use spatial index when one is available. In range queries with spatial relationships we will explore the index for the following spatial relations:

• te::gm::INTERSECTS, te::gm::TOUCHES, te::gm::OVERLAPS, te::gm::CROSSES, te::gm::EQUALS → RTreeIntersects
• te::gm::WITHIN → RTreeWithin
• te::gm::CONTAINS → RTreeContain

The driver uses just one database handle and the SQLite library must be compiled using SQLITE_THREADSAFE=2 if you intend to use it in multi-thread applications.

This driver supports …

std::map<std::string, std::string> dsInfo;
 
dsInfo["file"] = "C:/Users/gribeiro/Documents/data/sqlite/brasil.db";
 
dsInfo["SQLITE_OPEN_READONLY"] = "true";
 
dsInfo["SQLITE_HIDE_SPATIAL_METADATA_TABLES"] = "true";
 
dsInfo["SQLITE_HIDE_TABLES"] = "idx_munic_2001_using_rtree_the_geom_rowid";
 
std::auto_ptr<te::da::DataSource> ds(te::da::DataSourceFactory::open(datasourceType, connInfo));
 
std::auto_ptr<te::da::DataSourceTransactor> transactor(ds->getTransactor());
 
std::auto_ptr<te::da::DataSourceCatalogLoader> cloader(transactor->getCatalogLoader());
 
std::vector<std::string*> datasets;
 
cloader->getDataSets(datasets);
 
for(std::size_t i = 0; i < datasets.size(); ++i)
{
  std::auto_ptr<te::da::DataSet> dataset(transactor->getDataSet(*datasets[i]));
 
  while(dataset->moveNext())
  {
    std::auto_ptr<te::gm::Geometry> geom(dataset->getGeometry());
 
    std::cout << geom->asText() << std::endl;
  }
}
 
te::common::FreeContents(datasets);

You can store spatial data in a SQLite database without using the SpatiaLite extension. This code snippet shows how to use the TerraLib copy command line tool to copy a shapefile with polygon data to a SQLite database and store the data in a table having a BLOB column with the spatial component encoded in WKB.

This code snippet shows hopw to do the same operation above but keeping the data ina text column encoded in WKT.

-------------------------------------------------------------------------------
Language          files     blank   comment      code    scale   3rd gen. equiv
-------------------------------------------------------------------------------
C++                  23      1454       623      4326 x   1.51 =        6532.26
C/C++ Header         24       921      1013      1002 x   1.00 =        1002.00
XML                   3       123        13       371 x   1.90 =         704.90
SQL                   1         1         0        16 x   2.29 =          36.64
-------------------------------------------------------------------------------
SUM:                 51      2499      1649      5715 x   1.45 =        8275.80
-------------------------------------------------------------------------------

When you create a new SQLite database and no spatial format is informed (SpatiaLite or OGC WKB/WKT) the driver will prefer to create the spatial metadata tables for the SpatiaLite extension and it will use this extension to manipulate the data.

The SQLite library used to link against the driver must be built with:

• SQLITE_ENABLE_COLUMN_METADATA
• SQLITE_ENABLE_RTREE

The cancel method is not suited for multiple threads as it will cancel all queries in progress. So, take care when using this method with the SQLite driver.

If you want to enable the spatialite support in the driver build with: TE_ENABLE_SPATIALITE.

If you want to enable the rasterlite support in the driver build with: TE_ENABLE_RASTERLITE.

Additional efforts may be taken to make the driver more flexible:

• The next release of this driver will add support for the OGR format. It is inted to use a facade pattern in order to provide a transparent implementation of the two formats (OGR or SpatiaLite).
• EWKBReader:
  • we need to validate it.
  • IMHO compressed geometries as implemented in SpatiaLite has no use, we would add code for parsing compressed geometries.
• EWKBWriter:
  • we need to validate it.
• DataSourceCatalogLoader:
  • auto-increment → we could create this concept with triggers and regular tables.
  • check-constraints → we need to parse schema.
• DataSetTypePersistence:
  • when rename a dataset we must also rename spatial-index also because there isn't associations between them!
  • we can implement rename/update/drop for attributes but we will need to recreate the table!
  • add support for sequence and check-constraints
• DataSet:
  • DataSet doesn't support date/time encoded as double. This will be added in the next release of the driver.
• We will add a special version of compressed geometries that preserve the original data.
• We need to add provision to multiple databases (as one logical database).
• We can have atabase without spatial metadata tables and the user will want to use tables, informing this on connection time! We need to review open and create methods in this case.
• RasterLite needs more parameters:
  • mbr: to constraint the image to a certain extent
  • level: the level of the pyramid to be used