D 2014-09-22T22:55:15.194
L VirtualElementary
U sandro
W 4127
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.
Z d492df97151ca328165e49951c0d6efd