Not logged in

Back to 5.0.0-doc main page

About Temporary Geometries

Starting since version 5.0.0 SpatiaLite has the capability to fully support read-write Geometry Columns stored not only into the MAIN DATABASE, but also into any other ATTACHED DATABASE based on storage of the special :memory: type.

Quick recall

ATTACH DATABASE './my_databases/db1.sqlite' AS a;
A statement like the above one will ATTACH a further SQLite database file to the current connection. If the database file does not yet already exist, it will be created on-the-fly.
CREATE TABLE main.test (
    name TEXT NOT NULL);
INSERT INTO main.test VALUES ( 1, 'one' );
SELECT * FROM main.test;
1    one

    name TEXT NOT NULL);
INSERT INTO a.test VALUES ( 2, 'two' );
SELECT * FROM a.test;
2    two
Each database attached to the same connection has its own independent namespace, so the same table-name can be safely used on different databases.
In this case, in order to avoid any possible ambiguity, the fully qualified table-name must include a db-prefix, as e.g. a.test or main.test

ATTACH DATABASE ':memory:' AS a;
SQLite supports a very interesting capability; you can ATTACH one or more databases based on the special :memory: storage, each one identified by its own db-prefix.
In this case a new independent database will be immediately created in RAM and it will be intrinsically of transient nature, because it will be automatically destroyed when the connection will terminate, or when the database will be explicitly DETACHED.

Note: being fully based on RAM, all :memory: databases are expected to be fast as hell, due to quicker I/O timings.

However it should also be carefully considered that RAM is a limited resource to be very wisely consumed.

SpatiaLite and ATTACHED Geometries

All previous versions of SpatiaLite always considered all Geometry Columns stored into ATTACHED DATABASES as intrinsically subject to read-only limitations.
The reason explaining for all this is to be found in the many Triggers required by SpatiaLite for checking Geometry constraints (SRID, Geometry Type and Dimension) and for correctly updating the Spatial Index.
All these Triggers cannot work as expected on behalf of any ATTACHED DATABASE, thus causing the above stated read-only limitation.
But starting since version 5.0.0 SpatiaLite is now fully able to safely support real Geometries even when stored in any :memory: based ATTACHED DATABASE, thanks to a brand new set of Triggers purposely intended for temporary storage.

A quick practical example

ATTACH DATABASE ':memory:' AS mem_db;

CREATE TABLE mem_db.test (
    name TEXT NOT NULL);

SELECT AddTemporaryGeometryColumn ( 'mem_db', 'test', 'geom', 4326, 'POINT', 'XY' );

SELECT CreateTemporarySpatialIndex ( 'mem_db', 'test', 'geom' );

INSERT INTO mem_db.test VALUES ( NULL, 'one', MakePoint( 1.1, 1.5, 4326 ));
INSERT INTO mem_db.test VALUES ( NULL, 'two', MakePoint( 1.3, 2.7, 4326 ));
-- as many other INSERTs as required 
INSERT INTO mem_db.test VALUES ( NULL, 'thousand', MakePoint( 179.9, 89.7, 4326 ));

SELECT id, name, AsText(geom) FROM mem_db.test
    SELECT rowid FROM SpatialIndex WHERE f_table_name = 'DB=mem_db.test' AND search_frame = BuildMbr ( 1.2, 2.5, 1.7, 2.9, 4326 )
2    two    POINT(1.3 2.7)

Behind the scenes

  1. SpatiaLite will silently create both geometry_columns and spatial_ref_sys metatables into the ATTACHED DATABASE when required.
    spatial_ref_sys will be automatically be populated by copying the whole content of the same table from MAIN
  2. the SQL function TemporaryRTreeAlign() is only intended for supporting the Triggers installed by SpatiaLite, and is never intended to be explicitly called by users.

Final hints

Any SQLite connection always supports its own TEMPORARY DATABASE identified by the temp db-prefix.

Temporary Geometries will nicely work on temp, but you are strongly discouraged from doing such a thing, at least if you are planning to use before or after the SpatiaLite GUI tool.
The GUI tool will fail to correctly identify Temporary Geometries stored into temp, and will consequently show a messy DB layout. Always use a purposely created ATTACHED DATABASE based on :memory: is the suggested best practice for taking full profit from Temporary Geometries.

Back to 5.0.0-doc main page