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 (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL);
INSERT INTO main.test VALUES ( 1, 'one' );
SELECT * FROM main.test;
------------
1    one



CREATE TABLE a.test (
    id INTEGER PRIMARY KEY,
    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

• Notes: the database initially connected while opening the current connection will be always identified by the MAIN db-prefix.
• Explicitly declaring the MAIN db-prefix is never strictly required, because SQLite will start first from the assumption that any table identified by an unqualified table-name will be located into the MAIN database.

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 (
    id INTEGER PRIMARY KEY,
    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
WHERE id IN (
    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)

• the SQL function AddTemporaryGeometryColumn() plays the same identical role as the most usual AddGeometryColumn().
  • the main difference is in that it requires to specify some db-prefix as its first argument.
  • it will successfully work only if db-prefix identifies some ATTACHED DATABASE of the :memory: type; it will always fail in any other case.
  • this function will take care to install the special Triggers safely supporting Temporary Geometries.
• the SQL function CreateTemporarySpatialIndex() is the strict equivalent of the most usual CreateSpatialIndex()
  • the main difference is in that it requires to specify some db-prefix as its first argument.
  • it will successfully work only if db-prefix identifies some ATTACHED DATABASE of the :memory: type; it will always fail in any other case.
  • this function will take care to install the special Triggers required in order to safely synchronize a Spatial Index supporting some Temporary Geometry Column.
• Querying the Spatial Index supporting a Temporary Geometry Column does not requires any special attention; it exactly works in the same way of any other Spatial Index located into some ATTACHED DATABASE.

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