Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Artifact ID:	d6069e7bf8c7f124a0618f3a52b3facd2bc4b786
Page Name:	VirtualElementary
Date:	2014-09-22 22:55:15
Original User:	sandro

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)

• the ElementaryGeometries table provides the following columns:
  • db_prefix: the symbolic prefix identifying each attached database; the DB-file directly associated to the current connection always corresponds to the main prefix.
  • f_table_name: the name of the target table.
  • f_geometry_column: the name of the target geometry.
  • origin_rowid: the corresponding ROWID into the origin table.
  • item_no: progressive number individually identifying each elementary Geometry within its ROWID family.
  • geometry: an elementary Geometry item.
• every SELECT statement referencing ElementaryGeometries must include a WHERE clause specifying at least the intended f_table_name and origin_rowid values.
  The db_prefix element if not specified always implies the main DB.
  Specifying f_geometry_column is optional, except when the target table supports more than a single Geometry column.

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.