Storing map data in SQL

There is an SQL map data format intended mainly for data added and modified at run time. CartoType's main map data format is CTM1, which is versatile, compact and fast, but not modifiable: every time you need a new CTM1 file you have to create it from scratch - usually from OSM data.

The standard simple way to add data at run-time is to use the in-memory format. Every time you create a CartoType framework object you get one of these, and you can add pushpins and any other data you like, and this data is displayed on the map, just like data loaded from the CTM1 file. For medium-sized data sets you can conveniently save the data to a CTMS (CartoType map data, serialized) or KML (Keyhole Map Language) file.

The CTSQL format uses the SQLite library to store map data. You can save any map data to a CTSQL file using this function in the C++ API, and similar ones in the other APIs:

/**
Save a map identified by its handle by writing it the specified format.
Only writable map databases may be saved in this way.
The CTMS (CartoType Map Serialized),
CTSQL (CartoType SQL format), and
Keyhole Markup Language (KML) formats are supported.
*/
TResult CFramework::SaveMap(uint32 aHandle,const MString& aFileName,TFileType aFileType)

why use CTSQL?

There are two reasons to use CTSQL:

If you have too much modifiable data to keep in memory. CTSQL uses the SQLite file storage system, which can handle terabytes of data.
If you need to access the data outside CartoType using SQL. You can access a CTSQL file in the normal way using any SQLite tool, such as the SQLite command-line tool, or using your own app, via the SQLite library.

If neither of these conditions holds, you may find it easier to stay with the default system: use the standard in-memory database and save it to CTMS format if you need to make it persistent.

the schema

Here is the schema used for CTSQL files:

CREATE TABLE cartotype_global ( global_attrib_name TEXT PRIMARY KEY NOT NULL, global_attrib_value TEXT );
CREATE TABLE cartotype_layer ( layer_id INTEGER PRIMARY KEY NOT NULL, layer_name TEXT NOT NULL );
CREATE TABLE cartotype_object ( id INTEGER PRIMARY KEY NOT NULL, layer_id INTEGER NOT NULL REFERENCES layer_id(cartotype_layer), object_type INTEGER NOT NULL, int_attrib INTEGER NOT NULL, geometry BLOB NOT NULL );
CREATE VIRTUAL TABLE cartotype_bounds USING rtree_i32 (id,min_x,max_x,min_y,max_y);
CREATE TABLE "cartotype_bounds_node"(nodeno INTEGER PRIMARY KEY, data BLOB);
CREATE TABLE "cartotype_bounds_rowid"(rowid INTEGER PRIMARY KEY, nodeno INTEGER);
CREATE TABLE "cartotype_bounds_parent"(nodeno INTEGER PRIMARY KEY, parentnode INTEGER);
CREATE TABLE cartotype_attrib ( id INTEGER NOT NULL REFERENCES id(cartotype_object), attrib_name TEXT NOT NULL, attrib_value TEXT NOT NULL, PRIMARY KEY(id,attrib_name) );

As you can see, there's an R-Tree table for efficient spatial indexing.

geometry

For reasons of speed, geometry is stored in blobs, not analytically as points, lines and polygons. It's early days, so that decision may change, if enough people want to be able to get at the geometry.

limitations

Array objects - that is, the textures used for hill shading and urban areas - are not yet stored in CTSQL files. That limitation will probably be lifted soon.

file size

CTSQL files are roughly the same size as CTM1 files, or smaller if the CTM1 file contains extra low-resolutiuon layers.

CartoType Articles