D 2014-09-22T22:55:15.194
L VirtualElementary
U sandro
W 4127
<h2>about VirtualElementary</h2>
<b>VirtualElementary</b> is a new <b>Virtual Table</b> driver introduced starting since SpatiaLite version <b>4.2.1</b><br>
The intended scope of this virtual module is the one to separate complex geometries (as e.g. <i>MultiLinestring</i> or <i>MultiPolygon</i>) into many distinct elements, so that each single row returned into the resultset will always correspond to an elementary Geometry.<br>
Anyway the origin for every elementary Geometry will be always carefully preserved, so to make easy re-aggregating yet again the original complex Geometries if and when required.
<h2>the ElementaryGeometries table</h2>
Any new DB-file created using version <b>4.2.1</b> (or any later) will always include a table named <b>ElementaryGeometries</b> corresponding to an instance of <b>VirtualElementary</b>.<br>
Such table will obviously miss in all DB-files created using any earlier version; anyway adding the <b>VirtualElementary</b> support in such DB-files is really simple.<br>
Just execute the following SQL statement:
<verbatim>
CREATE VIRTUAL TABLE ElementaryGeometries USING VirtualElementary();
</verbatim>
<h2>how it works - a practical example</h2>
<verbatim>
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT);
SELECT AddGeometryColumn('test', 'geom', 4326, 'MULTILINESTRING', 'XY');
INSERT INTO test (id, name, geom) VALUES (NULL, 'one',
ST_GeomFromText('MULTILINESTRING((0 0, 0 1))', 4326));
INSERT INTO test (id, name, geom) VALUES (NULL, 'two',
ST_GeomFromText('MULTILINESTRING((0 0, 0 1), (1 0, 1 1))', 4326));
INSERT INTO test (id, name, geom) VALUES (NULL, 'three',
ST_GeomFromText('MULTILINESTRING((0 0, 0 1), (1 0, 1 1), (2 0, 2 1))', 4326));
</verbatim>
Since now we've simply created and populated a table containing MultiLinestrings; now we'll test the ElementaryGeometries table.
<verbatim>
SELECT * FROM ElementaryGeometries
WHERE f_table_name = 'test' AND origin_rowid = 2;
----------------
main test geom 2 0 BLOB sz=80 GEOMETRY
main test geom 2 1 BLOB sz=80 GEOMETRY
SELECT origin_rowid, item_no, ST_AsText(geometry)
FROM ElementaryGeometries
WHERE db_prefix = 'main' AND f_table_name = 'test'
AND f_geometry_column = 'geom' AND origin_rowid = 3;
----------------
3 0 LINESTRING(20 20, 20 21)
3 1 LINESTRING(21 20, 21 21)
3 2 LINESTRING(22 20, 22 21)
</verbatim>
<ul>
<li>the <b>ElementaryGeometries</b> table provides the following columns:
<ul>
<li><b>db_prefix</b>: the symbolic prefix identifying each <a href="http://www.sqlite.org/lang_attach.html">attached database</a>; the DB-file directly associated to the current connection always corresponds to the <b>main</b> prefix.</li>
<li><b>f_table_name</b>: the name of the target table.</li>
<li><b>f_geometry_column</b>: the name of the target geometry.</li>
<li><b>origin_rowid</b>: the corresponding ROWID into the origin table.</li>
<li><b>item_no</b>: progressive number individually identifying each elementary Geometry within its ROWID family.</li>
<li><b>geometry</b>: an elementary Geometry item.</li>
</ul></li>
<li>every <b>SELECT</b> statement referencing <b>ElementaryGeometries</b> must include a <b>WHERE</b> clause specifying at least the intended <b>f_table_name</b> and <b>origin_rowid</b> values.<br>
The <b>db_prefix</b> element if not specified always implies the <b>main</b> DB.<br>
Specifying <b>f_geometry_column</b> is optional, except when the target table supports more than a single Geometry column.</li>
</ul>
<verbatim>
SELECT t.id, e.item_no, t.name, ST_AsText(e.geometry)
FROM test AS t
JOIN ElementaryGeometries AS e ON (e.f_table_name = 'test' AND e.origin_rowid = t.id);
---------------------
1 0 one LINESTRING(0 0, 0 1)
2 0 two LINESTRING(10 10, 10 11)
2 1 two LINESTRING(11 10, 11 11)
3 0 three LINESTRING(20 20, 20 21)
3 1 three LINESTRING(21 20, 21 21)
3 2 three LINESTRING(22 20, 22 21)
</verbatim>
This final example shows how you can construct a SQL query dynamically resolving
all complex geometries form a whole table into individual elementary items.
Z d492df97151ca328165e49951c0d6efd