Home > Uncategorized > Using spatial features in SQLite to implement a writable custom dataset for Carmenta Engine

Using spatial features in SQLite to implement a writable custom dataset for Carmenta Engine

SQLite supports spatial indexing using R*-trees. I wanted to try it out so I set out to implement a custom dataset for Carmenta Engine entirely based on SQLite. Again, I will use System.Data.SQLite in order to access SQLite from .NET and C#. Carmenta Engine has built in support for Oracle and MySQL databases which is nice indeed for many scenarios. However, for (desktop) applications that simply want to persist geographic application data this is often overkill and you would probably turn to the Shapefile dataset for help. Shapefiles are however not as nice to work with as databases in many aspects and this is where a SQLite dataset would come handy. So… what is a custom dataset anyway?

A custom dataset must implement the ICustomDataset interface which is defined in the custom objects module.

namespace Carmenta.SpatialAce.Interop.CustomObjects


    public interface ICustomDataSet


        void InitNew(IDataSetContext context);


        RefSystem RefSys { get; }

        bool CreatesReferences { get; }


        Rect Bounds { get; }

        RectArea get_DataCoverage(Rect roi);


        GeoObject Get(int id);

        IQueryResult Query(Rect roi, DataSetQuery query, object ids, IGeoInfo info);


        void FlushCache();

        ICustomDataSet Clone();




More over, a custom dataset can also implement ICustomWritableDataSet, if it supports data modification.

namespace Carmenta.SpatialAce.Interop.CustomObjects


    public interface ICustomWritableDataSet


        void Clear();

        int Insert(GeoObject geoObject);

        void InsertDone();

        bool Remove(int id);




In short the most important methods for ICustomDataSet are:

  • InitNew – called to initialize the dataset in a given context (a context created when the custom dataset is attached to a dataset proxy)
  • RefSys – the reference system (or coordinate system) that the dataset prefers
  • Bounds – returns a bounding rectangle for the data contained in the dataset
  • Get – retrieve an object by its identity number
  • Query – retrieve objects within a specified rectangle of interest (roi)

and for ICustomWritableDataSet:

  • Clear – remove all objects
  • Insert – insert a new object
  • InsertDone – called to complete one or several consecutive insert calls
  • Remove – remove an object

Please refer to the manual for more information and detailed explaination for each method. Ok, lets move on. We need to be able to store geoobjects in our SQLite database. This can be solved using two tables; one table for the attributes (one column for each attribute) along with a enumeration value (1=point, 2=line, 3=polygon) and one table for the coordinates. Additionally, we will create a spatial index were the rows define the bounding boxes for the objects. Technically speaking, a spatial index is implemented as a virtual table in SQLite.

Here is the code for creating the objects table:

private void CreateObjectTable()


    var createTableCmd = new SQLiteCommand();

    createTableCmd.Connection = connection;

    var cmdText = new StringBuilder();

    cmdText.Append("CREATE TABLE IF NOT EXISTS ").Append(objectTableName).AppendLine(" (");

    cmdText.Append(" id integer primary key, type integer");

    foreach (var attribute in attributes)


        cmdText.AppendLine(",  ").Append(attribute.Key).Append(‘ ‘).Append(attribute.Value);




    createTableCmd.CommandText = cmdText.ToString();




The code will generate a sql command similiar to:

CREATE TABLE IF NOT EXISTS Object (id integer primary key, type integer, stringAttribute nvarchar(255), intAttribute int)

assuming that two attributes, stringAttribute of type string and intAttribute of type int, have been defined. The code for creating the coordinates table are more or less the same. Slightly more interesting is to code for creating the spatial index:

private void CreateSpatialIndex()


    // Check for existance. We check the sqlite_master table since sqlite does not support ‘CREATE VIRTUAL TABLE IF NOT EXISTS’

    var checkForIndexCmd = new SQLiteCommand();

    checkForIndexCmd.Connection = connection;

    checkForIndexCmd.CommandText = string.Format("SELECT 1 FROM sqlite_master WHERE type=’table’ and name=’{0}’", spatialIndexName);

    object checkForIndexResult =  checkForIndexCmd.ExecuteScalar();


    // Create index if it does not exist

    if (checkForIndexResult == null)


        var createTableCmd = new SQLiteCommand();

        createTableCmd.Connection = connection;

        var cmdText = new StringBuilder();

        cmdText.Append("CREATE VIRTUAL TABLE ").Append(spatialIndexName).AppendLine(" USING rtree(");


        cmdText.AppendLine("minX, maxX,");

        cmdText.AppendLine("minY, maxY");


        createTableCmd.CommandText = cmdText.ToString();





The first thing to notice is that SQLite virtual tables does not support ‘CREATE … IF NOT EXISTS’ so I use a workaround by quering the master table. A little bit more verbose but it gets the job done. The resulting sql command will look something like:

CREATE VIRTUAL TABLE SpatialIndex USING rtree(id, minX, maxX, minY, maxY)

All the tables are created (if not they do not exist ofcourse) from within the InitNew method in the CustomDataSet. So, here is our database schema:


Instead of using a custom schema like this it could be nice to use the same schema as defined by the OGR SQLite driver but… not today. Once the database is setup we can start adding data to it. This is done from the insert method. The insert method is rather verbose so i chose not to display the source code here but the sql it generates is rather compact so lets have a look at it:

INSERT INTO Object (id, type, stringAttribute, intAttribute) VALUES (?, ?, ?, ?)
INSERT INTO Coordinate (object_id, x, y, z) VALUES (?, ?, ?, ?)
INSERT INTO Coordinate (object_id, x, y, z) VALUES (?, ?, ?, ?)

INSERT INTO SpatialIndex VALUES (?, ?, ?, ?, ?)

Once we have data in our database we can also start making queries, either by the Get(id) method or by the Query(roi) method. The query method first uses the spatial index to find out which objects that overlap the region of interest. Here is the sql:

SELECT id FROM SpatialIndex WHERE maxX>=? AND minX<=? AND maxY>=? AND minY<=?

The result is an enumeration of object ids which is passed to the Get(id). The Get(id) method extracts data from the Object table and from the Coordinate table using the id:

SELECT * FROM Coordinate WHERE object_id=? ORDER BY id

Using this information we can create a new geoobject. One thing to note when creating geoobjects from a custom dataset (or a custom operator) is that the context object, provided to the InitNew method, have methods for this purpose (Context.NewGeoPoint, Context.NewGeoLine, ….). The reason for this is that, in order to support selection properly, we want to be able to set GeoObject.Id. However, the property is readonly but using the utility methods on the context method we can specify the id as a parameter. I do not show the code here since, again, it is rather verbose.

One nice property of the spatial index, besides from beeing a spatial index ofcourse, is that we can use it to implement the bounds property getter using the following sql statement

SELECT MIN(minX), MAX(maxX), MIN(minY), MAX(maxY) FROM SpatialIndex

There are a couple of things left such as the implementation of Clear and Remove but it is rather straight forward and you should be able to, I hope, figure out the remaining details from the source code. The dataset currently has a the following limitations:

  • No object cache
  • Only string and integer attributes are supported
  • Only points, lines and polygons (without holes) are supported
  • Insert of geoobjects defined in other reference systems
  • Not much of error handling (i.e.  changing the attributes definition string without deleting the database will cause problems)
  • Thread safety?
  • Poor unit tests

Still, I think it is, at least, a good starting point. Also, I hope, it is a good sample of how to use the spatial index feature of SQLite. To conclude this very long and verbose article I must say; SQLite rocks!

Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: