VirtualElementary
Not logged in

about VirtualElementary

VirtualElementary is a new Virtual Table driver introduced starting since SpatiaLite version 4.2.1
The intended scope of this virtual module is the one to separate complex geometries (as e.g. MultiLinestring or MultiPolygon) into many distinct elements, so that each single row returned into the resultset will always correspond to an elementary Geometry.
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.

the ElementaryGeometries table

Any new DB-file created using version 4.2.1 (or any later) will always include a table named ElementaryGeometries corresponding to an instance of VirtualElementary.
Such table will obviously miss in all DB-files created using any earlier version; anyway adding the VirtualElementary support in such DB-files is really simple.
Just execute the following SQL statement:
CREATE VIRTUAL TABLE ElementaryGeometries USING VirtualElementary();

how it works - a practical example

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));
Since now we've simply created and populated a table containing MultiLinestrings; now we'll test the ElementaryGeometries table.
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)
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)
This final example shows how you can construct a SQL query dynamically resolving all complex geometries form a whole table into individual elementary items.