D 2020-04-08T12:13:32.561
L TemporaryGeometries
U sandro
W 7176
Back to <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=5.0.0-doc">5.0.0-doc main page</a><hr><br>
<h2>About Temporary Geometries</h2>
Starting since version <b>5.0.0</b> SpatiaLite has the capability to fully support <i><b>read-write</b></i> <b>Geometry Columns</b> stored not only into the <b>MAIN DATABASE</b>, but also into any other <b>ATTACHED DATABASE</b> based on storage of the special <b>:memory:</b> type.
<h3>Quick recall</h3>
<verbatim>
ATTACH DATABASE './my_databases/db1.sqlite' AS a;
</verbatim>
A statement like the above one will <b>ATTACH</b> 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.
<verbatim>
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
</verbatim>
Each database attached to the same connection has its own independent <b>namespace</b>, so the same <b>table-name</b> can be safely used on different databases.<br>
In this case, in order to avoid any possible ambiguity, the <b>fully qualified table-name</b> must include a <b>db-prefix</b>, as e.g. <b><i>a.test</i></b> or <b><i>main.test</i></b><br><br>
<ul>
<li><b>Notes</b>: the database initially connected while opening the current connection will be always identified by the <b>MAIN</b> db-prefix.</li>
<li>Explicitly declaring the <b>MAIN</b> db-prefix is never strictly required, because SQLite will start first from the assumption that any table identified by an <b>unqualified table-name</b> will be located into the <b>MAIN</b> database.</li>
</ul>
<verbatim>
ATTACH DATABASE ':memory:' AS a;
</verbatim>
SQLite supports a very interesting capability; you can ATTACH one or more databases based on the special <b>:memory:</b> storage, each one identified by its own db-prefix.<br>
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.<br><br>
<table bgcolor="#ffffd0" cellspacing="4" cellpadding="8">
<td>
<b>Note</b>: being fully based on RAM, all <b>:memory:</b> databases are expected to be fast as hell, due to quicker I/O timings.<br><br>
However it should also be carefully considered that RAM is a limited resource to be very wisely consumed.
</td>
</table>
<h3>SpatiaLite and ATTACHED Geometries</h3>
All previous versions of SpatiaLite always considered all Geometry Columns stored into <b>ATTACHED DATABASES</b> as intrinsically subject to <b><i>read-only</i></b> limitations.<br>
The reason explaining for all this is to be found in the many <b>Triggers</b> required by SpatiaLite for checking Geometry constraints (SRID, Geometry Type and Dimension) and for correctly updating the <b>Spatial Index</b>.<br>
All these Triggers cannot work as expected on behalf of any <b>ATTACHED DATABASE</b>, thus causing the above stated read-only limitation.<br>
But starting since version <b>5.0.0</b> SpatiaLite is now fully able to safely support real Geometries even when stored in any <b>:memory:</b> based <b>ATTACHED DATABASE</b>, thanks to a brand new set of <b>Triggers</b> purposely intended for temporary storage.<br><br>
<hr>
<h2>A quick practical example</h2>
<verbatim>
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)
</verbatim>
<ul>
<li>the SQL function <b>AddTemporaryGeometryColumn()</b> plays the same identical role as the most usual <b>AddGeometryColumn()</b>.
<ul>
<li>the main difference is in that it requires to specify some <b>db-prefix</b> as its first argument.</li>
<li>it will successfully work only if <b>db-prefix</b> identifies some ATTACHED DATABASE of the <b>:memory:</b> type; it will always fail in any other case.</li>
<li>this function will take care to install the special <b>Triggers</b> safely supporting Temporary Geometries.</li>
</ul></li>
<li>the SQL function <b>CreateTemporarySpatialIndex()</b> is the strict equivalent of the most usual <b>CreateSpatialIndex()</b>
<ul>
<li>the main difference is in that it requires to specify some <b>db-prefix</b> as its first argument.</li>
<li>it will successfully work only if <b>db-prefix</b> identifies some ATTACHED DATABASE of the <b>:memory:</b> type; it will always fail in any other case.</li>
<li>this function will take care to install the special <b>Triggers</b> required in order to safely synchronize a <b>Spatial Index</b> supporting some Temporary Geometry Column.</li>
</ul></li>
<li>Querying the <b>Spatial Index</b> 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.</li>
</ul>
<h3>Behind the scenes</h3>
<ol>
<li>SpatiaLite will silently create both <b>geometry_columns</b> and <b>spatial_ref_sys</b> metatables into the ATTACHED DATABASE when required.<br>
<b>spatial_ref_sys</b> will be automatically be populated by copying the whole content of the same table from <b>MAIN</b></li>
<li>the SQL function <b>TemporaryRTreeAlign()</b> is only intended for supporting the <b>Triggers</b> installed by SpatiaLite, and is never intended to be explicitly called by users.</li>
</ol>
<table bgcolor="#d0ffd0" cellspacing="4" cellpadding="8">
<td>
<h3>Final hints</h3>
Any SQLite connection always supports its own <b>TEMPORARY DATABASE</b> identified by the <b>temp</b> db-prefix.<br><br>
Temporary Geometries will nicely work on <b>temp</b>, but you are strongly discouraged from doing such a thing, at least if you are planning to use before or after the <b>SpatiaLite GUI tool</b>.<br>
The GUI tool will fail to correctly identify Temporary Geometries stored into <b>temp</b>, and will consequently show a messy DB layout.
Always use a purposely created ATTACHED DATABASE based on <b>:memory:</b> is the suggested best practice for taking full profit from Temporary Geometries.
</td>
</table>
<br><hr><br>
Back to <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=5.0.0-doc">5.0.0-doc main page</a>
Z e748c1ed11563bff7a49837b90cb2d99